Optimizing Oracle Database

When optimizing a MongoDB instance, typically the goal is to maximize the throughput of an Oracle-backed application or to minimize its resource consumption, thus reducing costs.

Please refer to the Oracle Database optimization pack for the list of component types, parameters, metrics, and constraints.

Workflows

Applying parameters

One common way to configure Oracle parameters is through the execution ALTER SYSTEM statements on the database instance: to automate the execution of this task Akamas provides the OracleConfigurator operator. For finer control, Akamas provides the FileConfigurator operator, which allows building custom statements in a script file that can be executed by the Executor operator.

Oracle Configurator

The OracleConfigurator operator allows the workflow to configure an on-premise instance with minimal configuration. The following snippet is an example of a configuration task, where all the connection arguments are already defined in the referenced component:

name: Update Oracle parameters
operator: OracleConfigurator
arguments:
  component: oracledb

File Configurator and Executor

Most cloud providers offer web APIs as the only way to configure database services. In this case, the Executor operator can submit an API request through a custom executable using a configuration file generated by a FileConfigurator operator. The following is an example workflow where a FileConfigurator task generates a configuration file (oraconf), followed by an Executor task that parses and submits the configuration to the API endpoint through a custom script (api_update_db_conf.sh):

tasks:
  - name: Generate Oracle configuration
    operator: FileConfigurator
    arguments:
      sourcePath: /home/akamas/oraconf.template
      targetPath: /home/akamas/oraconf
      component: oracledb

  - name: Update conf
    operator: Executor
    arguments:
      command: bash /home/akamas/oraconf/api_update_db_conf.sh /home/akamas/oraconf
      component: oracle

A typical workflow

The optimization of an Oracle database usually includes the following tasks in the workflow, as implemented in the example below:

1. Apply the Oracle configuration suggested by Akamas and restart the instance if needed (Update parameters task).

2. Perform any additional warm-up task that may be required to bring the database up at the operating regime (Execute warmup task).

3. Execute the workload targeting the database or the front-end in front of it (Execute performance test task).

4. Restore the original state of the database in order to guarantee the consistency of further tests, removing any dirty data added by the workload and possibly flushing the database caches (Cleanup task).

The following is the complete YAML configuration file of the workflow described above:

name: workflow
description: Test Oracle instance configuration.
tasks:

  - name: Update parameters
    operator: OracleConfigurator
    arguments:
      component: oracledb

  - name: Execute warmup
    operator: Executor
    arguments:
      host:
        hostname: perf.mycompany.com
        key: ...
        username: perf
      command: /home/perf/warmup.sh

  - name: Execute performance test
    operator: Executor
    arguments:
      host:
        hostname: perf.mycompany.com
        key: ...
        username: perf
      command: /home/perf/start.sh

  - name: Cleanup
    operator: OracleExecutor
    arguments:
      sql:
        - TRUNCATE TABLE user_actions
      component: oracledb

Telemetry Providers

Akamas offers many telemetry providers to extract Oracle Database metrics; one of them is the Prometheus provider, which we can use to query Oracle Database metrics collected by a Prometheus instance via the Prometheus Oracle Exporter.

The snippet below shows a toml configuration example for the Oracle Exporter extracting metrics regarding the Oracle sessions:

[[metric]]
context = "sessions"
labels = [ "status", "type" ]
metricsdesc = { value= "Gauge metric with count of sessions by status and type." }
request = "SELECT status, type, COUNT(*) as value FROM v$session GROUP BY status, type"

The following example shows how to configure a telemetry instance for a Prometheus provider in order to query the data points extracted from the exporter described above:

provider: Prometheus
config:
  address: akamas.mycompany.com
  port: 9090

metrics:
  - metric: sessions_active_user
    datasourceMetric: oracledb_sessions_value{instance='$INSTANCE$', type='USER', status='ACTIVE', %FILTERS%}

  - metric: sessions_inactive_user
    datasourceMetric: oracledb_sessions_value{instance='$INSTANCE$', type='USER', status='INACTIVE', %FILTERS%}

Examples

See Optimizing an Oracle Database server instance and Optimizing an Oracle Database for an e-commerce service for examples of studies leveraging the Oracle Database pack.