Ctrlk
Akamas Insights
For the complete documentation index, see llms.txt. This page is also available as Markdown.

Optimizing an Oracle Database server instance

In this example, we are going to tune the initialization parameters of an Oracle Database server instance in order to maximize its throughput while stressed by a load generator.

For the workload, we’ll use the OLTPBench's implementation of TPC-C, a popular transaction processing benchmarking suite, while to extract the metrics we are going to leverage the Oracle Prometheus exporter.

Environment setup

Environment

For the purpose of this experiment we are going to use two dedicated machines:

We assume to be working with Linux hosts

Prometheus and exporters

Install the OracleDB Prometheus exporter

The OracleDB Prometheus exporter publishes as metrics the results of the 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 using the following command, where cust-metrics.toml is our custom metrics file:

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='system/passwd@//oraxe.mycompany.com:1521/XE' \
  iamseth/oracledb_exporter

The exporter will publish the metrics on port 9161.

Here’s the example metrics file used to run the exporter:

Install and configure Prometheus

You can check how to configure Prometheus here; by default, it will run on port 9090.

In order to configure the OracleDB exporter you can add the following snippet to the configuration file:

Optimization setup

System

In order to model the system composed of the tuned database and the workload generator we need two different components:

  • An oracle component that represents the Oracle Database instance and maps directly to oraxe.mycompany.com.

  • A tpcc component that represents the TPC-C workload from the OLTPBench suite and maps to oltpbench.mycompany.com.

For the tpcc component, we’ll need first to define some custom metrics and a new component-type. The following is the definition of the metrics (tpcc-metrics.yaml):

The following is the definition of the new component-type (tpcc-ctype.yaml):

We can then create the new component type running the commands:

As a next step, we can proceed then with the definition of our system (system.yaml):

Here’s the definition of our oracle component (oracle.yaml):

Here’s the definition of the tpcc component (tpcc.yaml):

We can create the system by running:

We can then create the components by running:

Telemetry

Prometheus

Since we are using Prometheus to extract the database metrics we can leverage the Prometheus provider, which already includes the queries needed for the Oracle metrics we need. To use the Prometheus provider we need to define a telemetry instance (prom.yaml):

We can now create the telemetry instance and attach it to our system by running:

CSV

Other than the telemetry of the Oracle instance, we need also the metrics in the output CSVs from the TPC-C workload runs. To ingest these metrics we can leverage the CSV Provider, defining the following telemetry instance (csv.yaml):

We can create the telemetry instance and attach it to our system by running:

Workflow

Remove previous executions' data

Using an Executor operator we run a command to clean the results folder that may contain files from previous executions

Configure the Oracle instance

We define a task that uses the OracleConfigurator operator to update the Oracle initialization parameters:

Restart the instance

We define a task that uses the Executor operator that reboots the Oracle container for the parameters that need a restart to take effect:

Run the workload

We define a task that uses the Executor operator to launch the TPC-C benchmark against the Oracle instance:

Prepare test results

We define a workflow task that runs a script that parses the TPC-C output files and generates a file compatible with the CSV Provider:

Where tpcc_parse_csv.sh is the following script:

Complete workflow

By putting together all the tasks defined above we come up with the following workflow definition (workflow.yaml):

We can create the workflow by running:

Study

The objective of this study is to maximize the transaction throughput while stressed by the TPC-C load generator, and to achieve this goal the study will tune the size of the most important areas of the Oracle instance.

Goal

Here’s the definition of the goal for our study, which is to maximize the tpcc.throughput metric:

Windowing

We define a window in order to consider only the data points after the ramp-up time of the load test:

Parameters to optimize

For this study, we are trying to achieve our goal by tuning the size of several areas in the memory of the database instance. In particular, we will tune the overall size of the Program Global Area (containing the work area of the active sessions) and the size of the components of the Shared Global Area.

The domains are configured to explore, for each parameter, the values around the default values.

Constraints

The following constraint allows the study to explore different size configurations without exceeding the maximum overall memory available for the instance:

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.

The baseline step contains some additional parameters (oracle.memory_target, oracle.sga_target) that are required by Oracle in order to disable the automatic management of the SGA components.

Here’s what these steps look like:

Complete study

Here’s the study definition (study.yaml) for optimizing the Oracle instance:

You can create the study by running:

You can then start it by running:

PreviousOptimizing a Spark applicationNextOptimizing an Oracle Database for an e-commerce service

Last updated

Was this helpful?