# 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](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/optimization-packs/oracle-database-pack) for the list of component types, parameters, metrics, and constraints.

### Workflows

#### Applying parameters <a href="#applying-parameters" id="applying-parameters"></a>

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](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/workflow-operators/oracleconfigurator-operator). For finer control, Akamas provides the [FileConfigurator operator](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/workflow-operators/fileconfigurator-operator), which allows building custom statements in a script file that can be executed by the [Executor operator](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/workflow-operators/executor-operator).

**Oracle Configurator**

The [OracleConfigurator operator](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/workflow-operators/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:

{% code lineNumbers="true" %}

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

{% endcode %}

**File Configurator and Executor**

Most cloud providers offer web APIs as the only way to configure database services. In this case, the [Executor operator](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/workflow-operators/executor-operator) can submit an API request through a custom executable using a configuration file generated by a [FileConfigurator operator](https://docs.akamas.io/akamas-docs/3.2.1-1/akamas-reference/workflow-operators/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`):

{% code lineNumbers="true" %}

```yaml
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: oracleML
```

{% endcode %}

#### A typical workflow <a href="#a-typical-workflow" id="a-typical-workflow"></a>

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:

{% code lineNumbers="true" %}

```yaml
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
```

{% endcode %}

### Telemetry Providers <a href="#telemetry-providers" id="telemetry-providers"></a>

Akamas offers many telemetry providers to extract Oracle Database metrics; one of them is the [Prometheus provider](https://docs.akamas.io/akamas-docs/3.2.1-1/integrating-akamas/integrating-telemetry-providers/prometheus-provider), which we can use to query Oracle Database metrics collected by a Prometheus instance via the [Prometheus Oracle Exporter](https://docs.akamas.io/akamas-docs/3.2.1-1/integrating-akamas/integrating-telemetry-providers/prometheus-provider/oracledb-exporter).

The snippet below shows a [toml](https://github.com/toml-lang/toml) configuration example for the Oracle Exporter extracting metrics regarding the Oracle sessions:

{% code lineNumbers="true" %}

```toml
[[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"
```

{% endcode %}

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:

{% code lineNumbers="true" %}

```yaml
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%}
```

{% endcode %}

### Examples <a href="#examples-of-studies" id="examples-of-studies"></a>

See [Optimizing an Oracle Database server instance](https://docs.akamas.io/akamas-docs/3.2.1-1/knowledge-base/optimizing-an-oracle-database-server-instance) and [Optimizing an Oracle Database for an e-commerce service](https://docs.akamas.io/akamas-docs/3.2.1-1/knowledge-base/optimizing-an-oracle-database-for-an-e-commerce-service) for examples of studies leveraging the Oracle Database pack.