# Optimizing an Oracle Database for an e-commerce service
In this example study, we will tune the initialization parameters of an Oracle Database server instance to minimize the memory required for running [KonaKart](https://www.konakart.com/), a popular Java e-commerce service, without significantly impacting the responsiveness of the whole system.
We’ll use [Apache JMeter](https://jmeter.apache.org/) to stress the system for the test, while we will leverage the [Oracle Prometheus exporter](https://github.com/iamseth/oracledb_exporter) to extract the metrics.
## Environment Setup
### Environment
For this study, we will use three dedicated machines:
* *oradb.mycompany.com*, hosting an Oracle Database 19c instance
* *konakart.mycompany.com*, running the KonaKart Community Edition service
* *akamas.mycompany.com*, which generates the workload using JMeter and will host the [OracleDB Prometheus exporter](https://github.com/iamseth/oracledb_exporter) instance
Refer to the following links to install and configure KonaKart Community Edition:
* [Install KonaKart](https://www.konakart.com/docs/InstallKonaKart.html): install and configure the service
* [Manual Installation](https://www.konakart.com/docs/manualInstallation.html): install the demo dataset
For this use case, we provisioned the database on a VM on [Oracle Cloud](https://www.oracle.com/cloud/), which allows us to easily provision licensed instances on demand.
### Prometheus and exporters
#### Install the OracleDB Prometheus exporter
Through the OracleDB Prometheus exporter, we can publish as metrics the results of the arbitrary queries defined in the configuration file. In our case, we’ll use it to extract valuable performance metrics from Oracle’s Dynamic Performance (V$) Views.
We can spin up the exporter using the [official Docker image](https://github.com/iamseth/oracledb_exporter) using the following command, where `cust-metrics.toml` is our custom metrics file:
```yaml
docker run -d --name orabench_exporter --restart always \
-p 9161:9161 \
-v ~/oracledb_exporter/cust-metrics.toml:/cust-metrics.toml \
-e CUSTOM_METRICS=/cust-metrics.toml \
-e DATA_SOURCE_NAME='user/passwd@//oradb.mycompany.com:1521/konakart' \
iamseth/oracledb_exporter
```
The exporter will publish the metrics on the specified port `9161`.
Here’s the metrics file used to run the exporter:
```toml
[[metric]]
context= "memory"
labels= [ "component" ]
metricsdesc= { size="Component memory extracted from v$memory_dynamic_components in Oracle." }
request = '''
SELECT component, current_size as "size"
FROM V$MEMORY_DYNAMIC_COMPONENTS
UNION
SELECT name, bytes as "size"
FROM V$SGAINFO
WHERE name in ('Free SGA Memory Available', 'Redo Buffers', 'Maximum SGA Size')
'''
[[metric]]
context = "activity"
metricsdesc = { value="Generic counter metric from v$sysstat view in Oracle." }
fieldtoappend = "name"
request = '''
SELECT name, value
FROM V$SYSSTAT WHERE name IN (
'execute count',
'user commits', 'user rollbacks',
'db block gets from cache', 'consistent gets from cache', 'physical reads cache', /* CACHE */
'redo log space requests'
)
'''
[[metric]]
context = "system_event"
labels = [ "event", "wait_class" ]
request = '''
SELECT
event, wait_class,
total_waits, time_waited
FROM V$SYSTEM_EVENT
'''
[metric.metricsdesc]
total_waits= "Total number of waits for the event as per V$SYSTEM_EVENT in Oracle."
time_waited= "Total time waited for the event (in hundredths of seconds) as per V$SYSTEM_EVENT in Oracle."
```
#### Install and configure Prometheus
Using the following snippet we configure Prometheus to fetch metrics from:
* the JMeter exporter exposing the load-generator stats
* the OracleDB exporter monitoring the database
```yaml
scrape_configs:
- job_name: jmeter
static_configs:
- targets: ['akamas.mycompany.com:9270']
relabel_configs:
- source_labels: [__address__]
action: replace
regex: "(.*):.*"
target_label: instance
- job_name: oracle-exp
static_configs:
- targets: ['akamas.mycompany.com:9161']
labels:
instance: oradb
```
For a complete guide on how to configure and manage Prometheus refer to the [official documentation](https://prometheus.io/docs/introduction/first_steps/).
#### Setup the load-generator
The load generator runs containerized on the *akamas.mycomopany.com* instance using the attached `Konakart_optimizePerf.jmx` configuration file.
```xml
falsetruefalseHOST${__P(HOST,konakartoraclecloud.lab.akamas.io)}=VUSER${__P(VUSER,60)}=${__P(VUSER,10)}02006000false-1startnextloop301true666catId${HOST}8780httpkonakart/Welcome.actionGETfalsetruetruefalsetruetruetrue${HOST}8780httpkonakart/SelectCat.action?catId=${catId}GETfalsetruetruefalsetruetruetrueSampler Response TimeResponseTimeSUMMARYlabelcode0.5,0.01|0.85,0.01|0.9,0.01|0.99,0.01;60samplesResponseTimeSuccess and failure ratioRatioSUCCESS_RATIOlabelcodesamplesSuccessRatiofalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetruefalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetrue20601140230060840260060540290060240false-1continue301true666catId${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/Welcome.actionGETfalsetruetruefalsetruetruetrue10100${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/SelectCat.action?catId=${catId}GETfalsetruetruefalsetruetruetrue10100Sampler Response TimeResponseTimeSUMMARYlabelcode0.5,0.01|0.85,0.01|0.9,0.01|0.99,0.01;60samplesResponseTimeSuccess and failure ratioRatioSUCCESS_RATIOlabelcodesamplesSuccessRatiofalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetruefalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetrue55030010354540207755800255106106530240false-1continue301true666catId${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/Welcome.actionGETfalsetruetruefalsetruetruetrue${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/SelectCat.action?catId=${catId}GETfalsetruetruefalsetruetruetrueSampler Response TimeResponseTimeSUMMARYlabelcode0.5,0.01|0.85,0.01|0.9,0.01|0.99,0.01;60samplesResponseTimeSuccess and failure ratioRatioSUCCESS_RATIOlabelcodesamplesSuccessRatiofalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetruefalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetrue6003600false-1continue301true666catId${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/Welcome.actionGETfalsetruetruefalsetruetruetrue${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/SelectCat.action?catId=${catId}GETfalsetruetruefalsetruetruetrueSampler Response TimeResponseTimeSUMMARYlabelcode0.5,0.01|0.85,0.01|0.9,0.01|0.99,0.01;60samplesResponseTimeSuccess and failure ratioRatioSUCCESS_RATIOlabelcodesamplesSuccessRatiofalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetruefalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetrue15090012002120012078031620180300false-1continue301true666catId${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/Welcome.actionGETfalsetruetruefalsetruetruetrue10100${__P(HOST,konakartoraclecloud.lab.akamas.io)}8780httpkonakart/SelectCat.action?catId=${catId}GETfalsetruetruefalsetruetruetrue10100Sampler Response TimeResponseTimeSUMMARYlabelcode0.5,0.01|0.85,0.01|0.9,0.01|0.99,0.01;60samplesResponseTimeSuccess and failure ratioRatioSUCCESS_RATIOlabelcodesamplesSuccessRatiofalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetruefalsesaveConfigtruetruetruetruetruetruetruefalsetruetruefalsefalsefalsetruefalsefalsefalsetrue0truetruetruetruetruetrue
```
The provided `run_test.sh` wraps the command to execute the test, and requires as an argument the URL of the target KonaKart instance.
```bash
#!/bin/bash
set -e
TARGET="${1:-konakartoracle.lab.akamas.io}"
IMAGE=chiabre/jmeter_plugins
THIS_DIR=`dirname "${BASH_SOURCE[0]}"`
NOW=`date +'%Y%m%d%H%M%S'`
docker run --rm --name jmeter -i \
--network akamas \
-v /home/ubuntu/konakart-oracle/jmeter:/tmp \
-w /tmp \
-p 9270:9270 \
$IMAGE \
-t Konakart_optimizePerf.jmx -JTARGET_HOST=$TARGET
cp "${THIS_DIR}/jmeter.log" "${THIS_DIR}/jmeter.log.$NOW"
```
## Optimization Setup
### System
Our modeled system includes the following components:
* The `oracle` component that models the Oracle Database instance on oradb.mycompany.com, whose parameters are the targets of our optimization
* The `webapp` component that models the KonaKart service running on konakart.mycompany.com, providing the performance metrics used to validate the system’s SLOs
The first step is defining the system (`system.yaml`):
```yaml
name: oracle system
description: Multi-tier application model featuring Java technology and Oracle Database on cloud
```
Here’s the definition of our `oracle` component (`oracle.yaml`), including the parameters needed to connect to the database instances and the filters to fetch metrics from Prometheus.
```yaml
name: oracle
description: Oracle DB for konakart
componentType: Oracle Database 19c
properties:
connection:
user: user
password: password
host: oradb.mycompany.com
service: konakart
port: 1521
prometheus:
instance: oradb
```
Notice: to update the init parameter the user requires the `ALTER SYSTEM` privilege.
Here’s the definition of the `konakart` component (`konakart.yaml`), containing the filters to fetch the metrics from Prometheus:
```yaml
name: konakart
description: Web application component for e2e metrics
componentType: Web Application
properties:
prometheus:
instance: jmeter
job: jmeter
```
We can create the system by running the following command:
```bash
akamas create system system.yaml
```
We can then create the components by running the following commands:
```bash
akamas create component oracle.yaml 'oracle system'
akamas create component konakart.yaml 'oracle system'
```
### Telemetry
Since we are using Prometheus to extract the database metrics we can leverage the [Prometheus provider,](https://docs.akamas.io/akamas-docs/3.6/integrating/integrating-telemetry-providers/prometheus-provider) which already includes the queries needed for the Oracle and JMetric queries for the metrics we need. To use the Prometheus provider we need to define a telemetry instance (`prom.yaml`):
```yaml
provider: Prometheus
config:
address: akamas.mycompany.com
port: 9090
logLevel: DETAILED
```
We can now create the telemetry instance and attach it to our system by running:
```bash
akamas create telemetry-instance prom.yaml 'oracle system'
```
### Workflow
This section outlines the steps performed during the execution of the experiments.
#### Stop KonaKart
Using an [Executor operator](https://docs.akamas.io/akamas-docs/3.6/reference/workflow-operators/executor-operator) we run a command to stop the KonaKart instance using the script provided with the installation, then check the service is not running anymore with a custom script:
```yaml
- name: stop konakart
operator: Executor
arguments:
retries: 0
command: bash /opt/konakart/bin/stopkonakart.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
- name: check konakart stop
operator: Executor
arguments:
retries: 0
command: bash /opt/scripts/check_konakart_stop.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
```
Attached you can find the referenced script check\_konakart\_stop.sh:
```bash
#! /bin/bash
numOfKonakartRunning=$(ps aux | grep "/opt/konakart/bin" | grep -v "grep" | wc -l)
if [ $numOfKonakartRunning -eq 0 ] ;
then
echo "konakart not running"
exit 0
else
echo "konakart is still running"
exit 1
fi
```
#### Configure the Oracle instance
Using the [OracleConfigurator operator](https://docs.akamas.io/akamas-docs/3.6/reference/workflow-operators/oracleconfigurator-operator) to update the Oracle initialization parameters with the new configuration. Then with the [Executor operator](https://docs.akamas.io/akamas-docs/3.6/reference/workflow-operators/executor-operator), we run some custom scripts to restart the database instance to apply the new parameters and check for a successful startup. Additionally, in case of a failed startup, the script of the last task restores a backup of the default configuration file (`spfile`), restarts the database, and returns an error code to notify Akamas that the tested configuration is invalid:
```yaml
- name: update oracle parameters
operator: OracleConfigurator
arguments:
retries: 0
component: oracle
- name: restart oracle
operator: Executor
arguments:
retries: 0
command: sudo su -c 'bash /opt/scripts/restart_db.sh' - oracle
host:
hostname: oradb.mycompany.com
username: opc
key: oraclekey
- name: check oracle status
operator: Executor
arguments:
retries: 0
command: sudo su -c 'bash /opt/scripts/check_db.sh' - oracle
host:
hostname: oradb.mycompany.com
username: opc
key: oraclekey
```
Attached you can find the referenced script check\_db.sh:
```bash
#!/bin/bash
ORA_OWNER=oracle
DB_NAME=kona_kart
BAK_FILE=~/akamas/spfilekona.ora
# [o] to avoid self-grep
PMON=[o]ra_pmon_kona
if ps -ef | grep -q $PMON ; then
echo "[INFO] - Database running"
else
if sudo test -f "$BAK_FILE"; then
echo "[INFO] - Trying to restore Oracle database using file $BAK_FILE"
echo "[INFO] - ORACLE_HOME value is: $ORACLE_HOME"
cp "$BAK_FILE" "$ORACLE_HOME/dbs/"
echo "[INFO] - Trying to restart oracle"
echo "[INFO] - Stopping the database"
$ORACLE_HOME/bin/dbshut $ORACLE_HOME
# $ORACLE_HOME/bin/srvctl stop database -d $DB_NAME
cat $ORACLE_HOME/rdbms/log/shutdown.log
echo "[INFO] - Starting the database"
$ORACLE_HOME/bin/dbstart $ORACLE_HOME
# $ORACLE_HOME/bin/srvctl start database -d $DB_NAME
cat $ORACLE_HOME/rdbms/log/startup.log
ps -ef | grep $PMON
else
echo "[ERROR] - The spfile does not exist in current folder!"
fi
exit 255
fi
exit 0
```
and restart\_db.sh:
```bash
#!/bin/bash
ORA_OWNER=oracle
DB_NAME=kona_kart
# [o] to avoid self-grep
PMON=[o]ra_pmon_kona
echo "[INFO] - Stopping the database"
$ORACLE_HOME/bin/dbshut $ORACLE_HOME
# $ORACLE_HOME/bin/srvctl stop database -d $DB_NAME
cat $ORACLE_HOME/rdbms/log/shutdown.log
echo "[INFO] - Starting the database"
$ORACLE_HOME/bin/dbstart $ORACLE_HOME
# $ORACLE_HOME/bin/srvctl start database -d $DB_NAME
cat $ORACLE_HOME/rdbms/log/startup.log
ps -ef | grep $PMON
exit 0
```
#### Restart the KonaKart instance
We then define the [Executor operator](https://docs.akamas.io/akamas-docs/3.6/reference/workflow-operators/executor-operator) tasks that restart the KonaKart service and check it is running correctly:
```yaml
- name: start konakart
operator: Executor
arguments:
retries: 0
command: /opt/konakart/bin/startkonakart.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
- name: wait for konakart
operator: Sleep
arguments:
retries: 0
seconds: 30
- name: check konakart service status
operator: Executor
arguments:
retries: 0
command: bash /opt/scripts/check_konakart_start.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
```
Attached you can find the referenced script:
```bash
#! /bin/bash
numOfKonakartRunning=$(ps aux | grep "/opt/konakart/bin" | grep -v "grep" | wc -l)
if [ $numOfKonakartRunning -eq 1 ] ;
then
echo "konakart running"
exit 0
else
echo "konakart not running"
exit 1
fi
```
#### Run the workload
Finally, we define a task that uses the [Executor operator](https://docs.akamas.io/akamas-docs/3.6/reference/workflow-operators/executor-operator) to run the JMeter load test against the KonaKart instance:
```yaml
- name: test
operator: Executor
arguments:
retries: 0
command: bash /opt/scripts/run_test.sh konakart.mycompany.com
host:
hostname: akamas.mycompany.com
username: ubuntu
key: keyfile
```
#### Complete workflow
By putting together all the tasks defined above we come up with the following workflow definition (`workflow.yaml`):
```yaml
name: konakart_workfl
tasks:
- name: stop konakart
operator: Executor
arguments:
retries: 0
command: bash /opt/konakart/bin/stopkonakart.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
- name: check konakart stop
operator: Executor
arguments:
retries: 0
command: bash /opt/scripts/check_konakart_stop.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
- name: update oracle parameters
operator: OracleConfigurator
arguments:
retries: 0
component: oracle
- name: restart oracle
operator: Executor
arguments:
retries: 0
command: sudo su -c 'bash /opt/scripts/restart_db.sh' - oracle
host:
hostname: oradb.mycompany.com
username: opc
key: oraclekey
- name: wait for oracle
operator: Sleep
arguments:
retries: 0
seconds: 60
- name: check oracle status
operator: Executor
arguments:
retries: 0
command: sudo su -c 'bash /opt/scripts/check_db.sh' - oracle
host:
hostname: oradb.mycompany.com
username: opc
key: oraclekey
- name: start konakart
operator: Executor
arguments:
retries: 0
command: /opt/konakart/bin/startkonakart.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
- name: wait for konakart
operator: Sleep
arguments:
retries: 0
seconds: 30
- name: check konakart service status
operator: Executor
arguments:
retries: 0
command: bash /opt/scripts/check_konakart_start.sh
host:
hostname: konakart.mycompany.com
username: ubuntu
key: keyfile
- name: test
operator: Executor
arguments:
retries: 0
command: bash /opt/scripts/run_test.sh konakart.mycompany.com
host:
hostname: akamas.mycompany.com
username: ubuntu
key: keyfile
```
We can create the workflow by running:
```bash
akamas create workflow workflow.yaml
```
### Study
This study aims to minimize the memory allocated for the Oracle database while under a simulated load of the typical traffic, without impacting the SLOs.
This section provides a step-by-step description of the study definition.
#### Goal
Here’s the definition of the goal for our study, which is to minimize the memory allocated by Oracle to the SGA and PGA memory areas.\
The constraints ensure that any tested configuration that does not operate within the defined SLOs is flagged as not valid. In particular, the following are required:
* the peak error rate must not exceed 5 errors per second
* the transaction throughput must not decrease more than 10% with respect to the baseline
* the response time must not increase more than 20% with respect to the baseline
```yaml
goal:
objective: minimize
function:
formula: oracle.oracle_sga_total_size + oracle.oracle_pga_target_size
constraints:
absolute:
- konakart.transactions_error_rate <= 5.0
relativeToBaseline:
- konakart.transactions_throughput >= -10%
- konakart.transactions_response_time <= +20%
```
#### Windowing
We define a window to consider only the data points after the ramp-up time of the load test:
```yaml
windowing:
type: trim
trim: [200s, 30s]
task: testde
```
#### Parameters to optimize
For this study, we are trying to optimize the size of the two main memory areas, meaning the *Program Global Area* and the *Shared Global Area*.
Given our goal, we set the domains of the parameters to explore only sizes smaller than the baseline.
```yaml
parametersSelection:
- name: oracle.sga_target
domain: [256, 1536]
- name: oracle.sga_max_size
domain: [256, 1536]
- name: oracle.pga_aggregate_target
domain: [256, 512]
```
The following constraint prevents Akamas from exploring configurations that we already know Oracle won’t validate:
```yaml
parameterConstraints:
- name: SGA
formula: oracle.sga_target <= oracle.sga_max_size
```
#### Steps
We are going to add to our study two steps:
* A baseline step, in which we configure the default values for the memory parameters as discovered from previous manual executions.
* An optimization step, where we perform 200 experiments to search the set of parameters that best satisfies our goal.
Here’s what these steps look like:
```yaml
steps:
- name: Baseline step
type: baseline
values:
oracle.sga_target: 1536
oracle.pga_aggregate_target: 512
oracle.sga_max_size: 1536
oracle.pga_aggregate_limit: 2048
- name: Optimization step
type: optimize
numberOfExperiments: 200
maxFailedExperiments: 200
```
#### Complete study
Here’s the study definition (`study.yaml`) for optimizing the Oracle instance:
```yaml
name: Minimize Oracle memory for KonaKart
system: oracle system
workflow: konakart_workfl
goal:
objective: minimize
function:
formula: oracle.oracle_sga_total_size + oracle.oracle_pga_target_size
constraints:
absolute:
- konakart.transactions_error_rate <= 5.0
relativeToBaseline:
- konakart.transactions_throughput >= -10%
- konakart.transactions_response_time <= +20%
windowing:
type: trim
trim: [200s, 30s]
task: test
parametersSelection:
- name: oracle.sga_target
domain: [256, 1536]
- name: oracle.sga_max_size
domain: [256, 1536]
- name: oracle.pga_aggregate_target
domain: [256, 512]
parameterConstraints:
- name: SGA
formula: oracle.sga_target <= oracle.sga_max_size
trialAggregation: AVG
numberOfTrials: 1
steps:
- name: Baseline step
type: baseline
values:
oracle.sga_target: 1536
oracle.pga_aggregate_target: 512
oracle.sga_max_size: 1536
oracle.pga_aggregate_limit: 2048
- name: Optimization step
type: optimize
numberOfExperiments: 200
maxFailedExperiments: 200
```
You can create the study by running:
```bash
akamas create study study.yaml
```
You can then start it by running:
```bash
akamas start study 'Minimize Oracle memory for KonaKart'
```