# RDS Oracle Database 12c

The optimization pack for Oracle Database 12c on Amazon RDS.

## Metrics

### Memory

<table><thead><tr><th width="255">Metric</th><th width="98">Unit</th><th>Description</th></tr></thead><tbody><tr><td>oracle_sga_total_size</td><td>bytes</td><td>The current memory size of the SGA.</td></tr><tr><td>oracle_sga_free_size</td><td>bytes</td><td>The amount of SGA currently available.</td></tr><tr><td>oracle_sga_max_size</td><td>bytes</td><td>The configured maximum memory size for the SGA.</td></tr><tr><td>oracle_pga_target_size</td><td>bytes</td><td>The configured target memory size for the PGA.</td></tr><tr><td>oracle_redo_buffers_size</td><td>bytes</td><td>The memory size of the redo buffers.</td></tr><tr><td>oracle_default_buffer_cache_size</td><td>bytes</td><td>The memory size for the DEFAULT buffer cache component.</td></tr><tr><td>oracle_default_2k_buffer_cache_size</td><td>bytes</td><td>The memory size for the DEFAULT 2k buffer cache component.</td></tr><tr><td>oracle_default_4k_buffer_cache_size</td><td>bytes</td><td>The memory size for the DEFAULT 4k buffer cache component.</td></tr><tr><td>oracle_default_8k_buffer_cache_size</td><td>bytes</td><td>The memory size for the DEFAULT 8k buffer cache component.</td></tr><tr><td>oracle_default_16k_buffer_cache_size</td><td>bytes</td><td>The memory size for the DEFAULT 16k buffer cache component.</td></tr><tr><td>oracle_default_32k_buffer_cache_size</td><td>bytes</td><td>The memory size for the DEFAULT 32k buffer cache component.</td></tr><tr><td>oracle_keep_buffer_cache_size</td><td>bytes</td><td>The memory size for the KEEP buffer cache component.</td></tr><tr><td>oracle_recycle_buffer_cache_size</td><td>bytes</td><td>The memory size for the RECYCLE buffer cache component.</td></tr><tr><td>oracle_asm_buffer_cache_size</td><td>bytes</td><td>The memory size for the ASM buffer cache component.</td></tr><tr><td>oracle_shared_io_pool_size</td><td>bytes</td><td>The memory size for the IO pool component.</td></tr><tr><td>oracle_java_pool_size</td><td>bytes</td><td>The memory size for the Java pool component.</td></tr><tr><td>oracle_large_pool_size</td><td>bytes</td><td>The memory size for the large pool component.</td></tr><tr><td>oracle_shared_pool_size</td><td>bytes</td><td>The memory size for the shared pool component.</td></tr><tr><td>oracle_streams_pool_size</td><td>bytes</td><td>The memory size for the streams pool component.</td></tr><tr><td>oracle_buffer_cache_hit_ratio</td><td>percent</td><td>How often a requested block has been found in the buffer cache without requiring disk access.</td></tr></tbody></table>

### Event waits

<table><thead><tr><th width="236">Metric</th><th width="108">Unit</th><th>Description</th></tr></thead><tbody><tr><td>oracle_wait_class_commit</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Commit'.</td></tr><tr><td>oracle_wait_class_concurrency</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Concurrency'.</td></tr><tr><td>oracle_wait_class_system_io</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'System I/O'.</td></tr><tr><td>oracle_wait_class_user_io</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'User I/O'.</td></tr><tr><td>oracle_wait_class_other</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Other'.</td></tr><tr><td>oracle_wait_class_scheduler</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Scheduler'.</td></tr><tr><td>oracle_wait_class_idle</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Idle'.</td></tr><tr><td>oracle_wait_class_application</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Application'.</td></tr><tr><td>oracle_wait_class_network</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Network'.</td></tr><tr><td>oracle_wait_class_configuration</td><td>percent</td><td>The percentage of time spent waiting on the events of class 'Configuration'.</td></tr><tr><td>oracle_wait_event_log_file_sync</td><td>percent</td><td>The percentage of time spent waiting on the 'log file sync' event.</td></tr><tr><td>oracle_wait_event_log_file_parallel_write</td><td>percent</td><td>The percentage of time spent waiting on the 'log file parallel write' event.</td></tr><tr><td>oracle_wait_event_log_file_sequential_read</td><td>percent</td><td>The percentage of time spent waiting on the 'log file sequential read' event.</td></tr><tr><td>oracle_wait_event_enq_tx_contention</td><td>percent</td><td>The percentage of time spent waiting on the 'enq: TX - contention' event.</td></tr><tr><td>oracle_wait_event_enq_tx_row_lock_contention</td><td>percent</td><td>The percentage of time spent waiting on the 'enq: TX - row lock contention' event.</td></tr><tr><td>oracle_wait_event_latch_row_cache_objects</td><td>percent</td><td>The percentage of time spent waiting on the 'latch: row cache objects' event.</td></tr><tr><td>oracle_wait_event_latch_shared_pool</td><td>percent</td><td>The percentage of time spent waiting on the 'latch: shared pool' event.</td></tr><tr><td>oracle_wait_event_resmgr_cpu_quantum</td><td>percent</td><td>The percentage of time spent waiting on the 'resmgr:cpu quantum' event.</td></tr><tr><td>oracle_wait_event_sql_net_message_from_client</td><td>percent</td><td>The percentage of time spent waiting on the 'SQL*Net message from client' event.</td></tr><tr><td>oracle_wait_event_rdbms_ipc_message</td><td>percent</td><td>The percentage of time spent waiting on the 'rdbms ipc message' event.</td></tr><tr><td>oracle_wait_event_db_file_sequential_read</td><td>percent</td><td>The percentage of time spent waiting on the 'db file sequential read' event.</td></tr><tr><td>oracle_wait_event_log_file_switch_checkpoint_incomplete</td><td>percent</td><td>The percentage of time spent waiting on the 'log file switch (checkpoint incomplete)' event.</td></tr><tr><td>oracle_wait_event_row_cache_lock</td><td>percent</td><td>The percentage of time spent waiting on the 'row cache lock' event.</td></tr><tr><td>oracle_wait_event_buffer_busy_waits</td><td>percent</td><td>The percentage of time spent waiting on the 'buffer busy waits' event.</td></tr><tr><td>oracle_wait_event_db_file_async_io_submit</td><td>percent</td><td>The percentage of time spent waiting on the 'db file async I/O submit' event.</td></tr></tbody></table>

### Sessions

<table><thead><tr><th width="274">Metric</th><th width="111">Unit</th><th>Description</th></tr></thead><tbody><tr><td>oracle_sessions_active_user</td><td>sessions</td><td>The number of active user sessions.</td></tr><tr><td>oracle_sessions_inactive_user</td><td>sessions</td><td>The number of inactive user sessions.</td></tr><tr><td>oracle_sessions_active_background</td><td>sessions</td><td>The number of active background sessions.</td></tr><tr><td>oracle_sessions_inactive_background</td><td>sessions</td><td>The number of inactive background sessions.</td></tr></tbody></table>

### Other metrics

| Metric                             | Unit         | Description                                                                                                                |
| ---------------------------------- | ------------ | -------------------------------------------------------------------------------------------------------------------------- |
| oracle\_calls\_execute\_count      | calls        | Total number of calls (user and recursive) that executed SQL statements.                                                   |
| oracle\_tuned\_undoretention       | seconds      | The amount of time for which undo will not be recycled from the time it was committed.                                     |
| oracle\_max\_query\_length         | seconds      | The length of the longest query executed.                                                                                  |
| oracle\_transaction\_count         | transactions | The total number of transactions executed within the period.                                                               |
| oracle\_sso\_errors                | errors/s     | The number of ORA-01555 (snapshot too old) errors raised per second.                                                       |
| oracle\_redo\_log\_space\_requests | requests     | The number of times a user process waits for space in the redo log file, usually caused by checkpointing or log switching. |

## Parameters

### Memory

| Parameter                         | Unit      | Default value | Domain                  | Restart | Description                                                                                                                                     |
| --------------------------------- | --------- | ------------- | ----------------------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| bitmap\_merge\_area\_size         | kilobytes | `1024`        | `0` → `2097152`         | yes     | The amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index.                                                     |
| create\_bitmap\_area\_size        | megabytes | `8192`        | `0` → `2097152`         | yes     | Size of create bitmap buffer for bitmap index. Relevant only for systems containing bitmap indexes.                                             |
| db\_cache\_size                   | megabytes | `48`          | `0` → `2097152`         | no      | The size of the DEFAULT buffer pool for standard block size buffers. The value must be at least 4M \* cpu number.                               |
| hash\_area\_size                  | kilobytes | `128`         | `0` → `2097151`         | yes     | Maximum size of in-memory hash work area maximum amount of memory.                                                                              |
| java\_pool\_size                  | megabytes | `24`          | `0` → `16384`           | no      | The size of the Java pool. If SGA\_TARGET is set, this value represents the minimum value for the memory pool.                                  |
| large\_pool\_size                 | megabytes | `0`           | `0` → `65536`           | no      | The size of large pool allocation heap.                                                                                                         |
| memory\_max\_target               | megabytes | `8192`        | `152` → `2097152`       | yes     | The maximum value to which a DBA can set the MEMORY\_TARGET initialization parameter.                                                           |
| memory\_target                    | megabytes | `6864`        | `0` → `2097152`         | no      | Oracle systemwide usable memory. The database tunes memory to the MEMORY\_TARGET value, reducing or enlarging the SGA and PGA as needed.        |
| olap\_page\_pool\_size            | bytes     | `0`           | `0` → `2147483647`      | no      | Size of the olap page pool.                                                                                                                     |
| pga\_aggregate\_limit             | megabytes | `2048`        | `0` → `2097152`         | no      | The limit on the aggregate PGA memory consumed by the instance.                                                                                 |
| pga\_aggregate\_target            | megabytes | `1024`        | `0` → `2097152`         | no      | The target aggregate PGA memory available to all server processes attached to the instance.                                                     |
| pre\_page\_sga                    |           | `FALSE`       | `TRUE`, `FALSE`         | yes     | Read the entire SGA into memory at instance startup.                                                                                            |
| result\_cache\_max\_result        | percent   | `5`           | `0` → `100`             | no      | Maximum result size as a percent of the cache size.                                                                                             |
| result\_cache\_max\_size          | megabytes | `0`           | `0` → `65536`           | no      | The maximum amount of SGA memory that can be used by the Result Cache.                                                                          |
| result\_cache\_mode               |           | `MANUAL`      | `MANUAL`, `FORCE`       | no      | Specifies when a ResultCache operator is spliced into a query's execution plan.                                                                 |
| result\_cache\_remote\_expiration | minutes   | `0`           | `0` → `10000`           | no      | The expiration in minutes of remote objects. High values may cause stale answers.                                                               |
| sga\_max\_size                    | megabytes | `8192`        | `0` → `2097152`         | yes     | The maximum size of the SGA for the lifetime of the instance.                                                                                   |
| sga\_min\_size                    | megabytes | `2920`        | `0` → `1048576`         | no      | The guaranteed SGA size for a pluggable database (PDB). When SGA\_MIN\_SIZE is set for a PDB, it guarantees the specified SGA size for the PDB. |
| sga\_target                       | megabytes | `5840`        | `0` → `2097152`         | no      | The total size of all SGA components, acts as the minimum value for the size of the SGA.                                                        |
| shared\_pool\_reserved\_size      | megabytes | `128`         | `1` → `2048`            | yes     | The shared pool space reserved for large contiguous requests for shared pool memory.                                                            |
| shared\_pool\_size                | megabytes | `0`           | `0` → `65536`           | no      | The size of the shared pool.                                                                                                                    |
| sort\_area\_retained\_size        | kilobytes | `0`           | `0` → `2097151`         | no      | The maximum amount of the User Global Area memory retained after a sort run completes.                                                          |
| sort\_area\_size                  | kilobytes | `64`          | `0` → `2097151`         | no      | The maximum amount of memory Oracle will use for a sort. If more space is required then temporary segments on disks are used.                   |
| streams\_pool\_size               | megabytes | `0`           | `0` → `2097152`         | no      | Size of the streams pool.                                                                                                                       |
| use\_large\_pages                 |           | `TRUE`        | `ONLY`, `FALSE`, `TRUE` | yes     | Enable the use of large pages for SGA memory.                                                                                                   |
| workarea\_size\_policy            |           | `AUTO`        | `MANUAL`, `AUTO`        | no      | Policy used to size SQL working areas (MANUAL/AUTO).                                                                                            |

### Redo

| Parameter                    | Unit      | Default value | Domain                         | Restart | Description                                                                                                       |
| ---------------------------- | --------- | ------------- | ------------------------------ | ------- | ----------------------------------------------------------------------------------------------------------------- |
| commit\_logging              |           | `BATCH`       | `IMMEDIATE`, `BATCH`           | no      | Control how redo is batched by Log Writer.                                                                        |
| commit\_wait                 |           | `WAIT`        | `NOWAIT`, `WAIT`, `FORCE_WAIT` | no      | Control when the redo for a commit is flushed to the redo logs.                                                   |
| log\_archive\_max\_processes | processes | `4`           | `1` → `30`                     | no      | Maximum number of active ARCH processes.                                                                          |
| log\_buffer                  | megabytes | `16`          | `2` → `256`                    | yes     | The amount of memory that Oracle uses when buffering redo entries to a redo log file.                             |
| log\_checkpoint\_interval    | blocks    | `0`           | `0` → `2147483647`             | no      | The maximum number of log file blocks between incremental checkpoints.                                            |
| log\_checkpoint\_timeout     | seconds   | `1800`        | `0` → `2147483647`             | no      | Maximum time interval between checkpoints. Guarantees a no buffer remains dirty for more than the specified time. |

### Undo

| Parameter                        | Unit    | Default value | Domain              | Restart | Description                                    |
| -------------------------------- | ------- | ------------- | ------------------- | ------- | ---------------------------------------------- |
| db\_flashback\_retention\_target | minutes | `1440`        | `30` → `2147483647` | no      | Maximum Flashback Database log retention time. |
| undo\_retention                  | seconds | `900`         | `0` → `2147483647`  | no      | Low threshold value of undo retention.         |

### Optimizer

| Parameter                                | Unit | Default value | Domain                                                                                                                                                                                                                                                                                                                                           | Restart | Description                                                                                                                              |
| ---------------------------------------- | ---- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| optimizer\_capture\_sql\_plan\_baselines |      | `FALSE`       | `TRUE`, `FALSE`                                                                                                                                                                                                                                                                                                                                  | no      | Automatic capture of SQL plan baselines for repeatable statements                                                                        |
| optimizer\_dynamic\_sampling             |      | `2`           | `0` → `11`                                                                                                                                                                                                                                                                                                                                       | no      | Controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics. |
| optimizer\_features\_enable              |      | `11.2.0.4`    | `11.2.0.4.1`, `11.2.0.4`, `11.2.0.3`, `11.2.0.2`, `11.2.0.1`, `11.1.0.7`, `11.1.0.6`, `10.2.0.5`, `10.2.0.4`, `10.2.0.3`, `10.2.0.2`, `10.2.0.1`, `10.1.0.5`, `10.1.0.4`, `10.1.0.3`, `10.1.0`, `9.2.0.8`, `9.2.0`, `9.0.1`, `9.0.0`, `8.1.7`, `8.1.6`, `8.1.5`, `8.1.4`, `8.1.3`, `8.1.0`, `8.0.7`, `8.0.6`, `8.0.5`, `8.0.4`, `8.0.3`, `8.0.0` | no      | Enable a series of optimizer features based on an Oracle release number.                                                                 |
| optimizer\_index\_caching                |      | `0`           | `0` → `100`                                                                                                                                                                                                                                                                                                                                      | no      | Adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.                                        |
| optimizer\_index\_cost\_adj              |      | `100`         | `1` → `10000`                                                                                                                                                                                                                                                                                                                                    | no      | Tune optimizer behavior for access path selection to be more or less index friendly.                                                     |
| optimizer\_mode                          |      | `ALL_ROWS`    | `ALL_ROWS`, `FIRST_ROWS`, `FIRST_ROWS_1`, `FIRST_ROWS_10`, `FIRST_ROWS_100`, `FIRST_ROWS_1000`                                                                                                                                                                                                                                                   | no      | The default behavior for choosing an optimization approach for the instance.                                                             |
| optimizer\_secure\_view\_merging         |      | `TRUE`        | `TRUE`, `FALSE`                                                                                                                                                                                                                                                                                                                                  | no      | Enables security checks when the optimizer uses view merging.                                                                            |
| optimizer\_use\_invisible\_indexes       |      | `FALSE`       | `TRUE`, `FALSE`                                                                                                                                                                                                                                                                                                                                  | no      | Enable or disables the use of invisible indexes.                                                                                         |
| optimizer\_use\_pending\_statistics      |      | `FALSE`       | `TRUE`, `FALSE`                                                                                                                                                                                                                                                                                                                                  | no      | Control whether the optimizer uses pending statistics when compiling SQL statements.                                                     |
| optimizer\_use\_sql\_plan\_baselines     |      | `TRUE`        | `TRUE`, `FALSE`                                                                                                                                                                                                                                                                                                                                  | no      | Enables the use of SQL plan baselines stored in SQL Management Base.                                                                     |

### Approximate exection

| Parameter                    | Unit | Default value | Domain                                                                                                                                     | Restart | Description                                                                                |
| ---------------------------- | ---- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------ | ------- | ------------------------------------------------------------------------------------------ |
| approx\_for\_aggregation     |      | `FALSE`       | `TRUE`, `FALSE`                                                                                                                            | no      | Replace exact query processing for aggregation queries with approximate query processing.  |
| approx\_for\_count\_distinct |      | `FALSE`       | `TRUE`, `FALSE`                                                                                                                            | no      | Automatically replace COUNT (DISTINCT expr) queries with APPROX\_COUNT\_DISTINCT queries.  |
| approx\_for\_percentile      |      | `NONE`        | `NONE`, `PERCENTILE_CONT`, `PERCENTILE_CONT DETERMINISTIC`, `PERCENTILE_DISC`, `PERCENTILE_DISC DETERMINISTIC`, `ALL`, `ALL DETERMINISTIC` | no      | Converts exact percentile functions to their approximate percentile function counterparts. |

### Parallel processing

| Parameter                          | Unit      | Default value | Domain                      | Restart | Description                                                                                                                      |
| ---------------------------------- | --------- | ------------- | --------------------------- | ------- | -------------------------------------------------------------------------------------------------------------------------------- |
| parallel\_degree\_policy           |           | `MANUAL`      | `MANUAL`, `LIMITED`, `AUTO` | no      | Policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO).                                                          |
| parallel\_execution\_message\_size |           | `16384`       | `2148` → `32768`            | yes     | Message buffer size for parallel execution.                                                                                      |
| parallel\_force\_local             |           | `FALSE`       | `TRUE`, `FALSE`             | no      | Force single instance execution.                                                                                                 |
| parallel\_max\_servers             | processes | `0`           | `0` → `3600`                | no      | The maximum number of parallel execution processes and parallel recovery processes for an instance.                              |
| parallel\_min\_servers             | processes | `0`           | `0` → `2000`                | no      | The minimum number of execution processes kept alive to service parallel statements.                                             |
| parallel\_min\_percent             | percent   | `0`           | `0` → `100`                 | yes     | The minimum percentage of parallel execution processes (of the value of PARALLEL\_MAX\_SERVERS) required for parallel execution. |

### Resources

| Parameter                          | Unit         | Default value | Domain                                              | Restart | Description                                                                                                                         |
| ---------------------------------- | ------------ | ------------- | --------------------------------------------------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| circuits                           | circuits     | `10`          | `0` → `3000`                                        | no      | The total number of virtual circuits that are available for inbound and outbound network sessions.                                  |
| cpu\_count                         | cpus         | `0`           | `0` → `512`                                         | no      | Number of CPUs available for the Oracle instance to use.                                                                            |
| cursor\_bind\_capture\_destination |              | `MEMORY+DISK` | `OFF`, `MEMORY`, `MEMORY+DISK`                      | no      | Allowed destination for captured bind variables.                                                                                    |
| cursor\_sharing                    |              | `EXACT`       | `FORCE`, `EXACT`, `SIMILAR`                         | no      | Cursor sharing mode.                                                                                                                |
| cursor\_space\_for\_time           |              | `FALSE`       | `TRUE`, `FALSE`                                     | yes     | Use more memory in order to get faster execution.                                                                                   |
| db\_files                          | files        | `200`         | `200` → `20000`                                     | yes     | The maximum number of database files that can be opened for this database. This may be subject to OS constraints.                   |
| open\_cursors                      | cursors      | `300`         | `0` → `65535`                                       | no      | The maximum number of open cursors (handles to private SQL areas) a session can have at once.                                       |
| open\_links                        | connections  | `4`           | `0` → `255`                                         | yes     | The maximum number of concurrent open connections to remote databases in one session.                                               |
| open\_links\_per\_instance         | connections  | `4`           | `0` → `2147483647`                                  | yes     | Maximum number of migratable open connections globally for each database instance.                                                  |
| processes                          | processes    | `100`         | `80` → `20000`                                      | yes     | The maximum number of OS user processes that can simultaneously connect to Oracle.                                                  |
| serial\_reuse                      |              | `DISABLE`     | `DISABLE`, `ALL`, `SELECT`, `DML`, `PLSQL`, `FORCE` | yes     | Types of cursors that make use of the serial-reusable memory feature.                                                               |
| session\_cached\_cursors           |              | `50`          | `0` → `65535`                                       | no      | Number of session cursors to cache.                                                                                                 |
| session\_max\_open\_files          |              | `10`          | `1` → `50`                                          | yes     | Maximum number of open files allowed per session.                                                                                   |
| sessions                           | sessions     | `1262`        | `100` → `65532`                                     | no      | The maximum number of sessions that can be created in the system, effectively the maximum number of concurrent users in the system. |
| transactions                       | transactions | `1388`        | `4` → `2147483647`                                  | yes     | The maximum number of concurrent transactions.                                                                                      |

### Other parameters

| Parameter                            | Unit         | Default value      | Domain                                                                | Restart | Description                                                                                                               |
| ------------------------------------ | ------------ | ------------------ | --------------------------------------------------------------------- | ------- | ------------------------------------------------------------------------------------------------------------------------- |
| aq\_tm\_processes                    |              | `1`                | `0` → `40`                                                            | no      | Number of AQ Time Managers to start.                                                                                      |
| audit\_sys\_operations               |              | `FALSE`            | `TRUE`, `FALSE`                                                       | yes     | Enable sys auditing                                                                                                       |
| audit\_trail                         |              | `NONE`             | `NONE`, `OS`, `DB`, `TRUE`, `FALSE`, `DB_EXTENDED`, `XML`, `EXTENDED` | yes     | Configure system auditing.                                                                                                |
| client\_result\_cache\_lag           | milliseconds | `3000`             | `0` → `60000`                                                         | yes     | Maximum time before checking the database for changes related to the queries cached on the client.                        |
| client\_result\_cache\_size          | kilobytes    | `0`                | `0` → `2147483647`                                                    | yes     | The maximum size of the client per-process result set cache.                                                              |
| db\_block\_checking                  |              | `MEDIUM`           | `FALSE`, `OFF`, `LOW`, `MEDIUM`, `TRUE`, `FULL`                       | no      | Header checking and data and index block checking.                                                                        |
| db\_block\_checksum                  |              | `TYPICAL`          | `OFF`, `FALSE`, `TYPICAL`, `TRUE`, `FULL`                             | no      | Store checksum in db blocks and check during reads.                                                                       |
| db\_file\_multiblock\_read\_count    |              | `128`              | `0` → `1024`                                                          | no      | Db block to be read each IO.                                                                                              |
| db\_keep\_cache\_size                | megabytes    | `0`                | `0` → `2097152`                                                       | no      | Size of KEEP buffer pool for standard block size buffers.                                                                 |
| db\_lost\_write\_protect             |              | `NONE`             | `NONE`, `TYPICAL`, `FULL`                                             | no      | Enable lost write detection.                                                                                              |
| db\_recovery\_file\_dest\_size       | megabytes    | `1024`             | `1` → `16777216`                                                      | no      | Database recovery files size limit.                                                                                       |
| db\_recycle\_cache\_size             | megabytes    | `0`                | `0` → `2097152`                                                       | no      | Size of RECYCLE buffer pool for standard block size buffers.                                                              |
| db\_writer\_processes                |              | `1`                | `1` → `36`                                                            | yes     | Number of background database writer processes to start.                                                                  |
| ddl\_lock\_timeout                   |              | `0`                | `0` → `1000000`                                                       | no      | Timeout to restrict the time that ddls wait for dml lock.                                                                 |
| deferred\_segment\_creation          |              | `TRUE`             | `TRUE`, `FALSE`                                                       | no      | Defer segment creation to first insert.                                                                                   |
| distributed\_lock\_timeout           | seconds      | `60`               | `1` → `2147483647`                                                    | yes     | Number of seconds a distributed transaction waits for a lock.                                                             |
| dml\_locks                           |              | `5552`             | `0` → `2000000`                                                       | yes     | The maximum number of DML locks - one for each table modified in a transaction.                                           |
| enable\_goldengate\_replication      |              | `FALSE`            | `TRUE`, `FALSE`                                                       | no      | Enable GoldenGate replication.                                                                                            |
| fast\_start\_parallel\_rollback      |              | `LOW`              | `FALSE`, `LOW`, `HIGH`                                                | no      | Max number of parallel recovery slaves that may be used.                                                                  |
| hs\_autoregister                     |              | `TRUE`             | `TRUE`, `FALSE`                                                       | no      | Enable automatic server DD updates in HS agent self-registration.                                                         |
| java\_jit\_enabled                   |              | `TRUE`             | `TRUE`, `FALSE`                                                       | no      | Enables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine.                                              |
| java\_max\_sessionspace\_size        | bytes        | `0`                | `0` → `2147483647`                                                    | yes     | Max allowed size in bytes of a Java sessionspace.                                                                         |
| java\_soft\_sessionspace\_limit      | bytes        | `0`                | `0` → `2147483647`                                                    | yes     | Warning limit on size in bytes of a Java sessionspace.                                                                    |
| job\_queue\_processes                |              | `1000`             | `0` → `1000`                                                          | no      | Maximum number of job queue slave processes.                                                                              |
| object\_cache\_max\_size\_percent    | percent      | `10`               | `0` → `100`                                                           | no      | Percentage of maximum size over optimal of the user sessions object cache.                                                |
| object\_cache\_optimal\_size         | kilobytes    | `100`              | `0` → `67108864`                                                      | no      | Optimal size of the user sessions object cache.                                                                           |
| plscope\_settings                    |              | `IDENTIFIERS:NONE` | `IDENTIFIERS:NONE`, `IDENTIFIERS:ALL`                                 | no      | Plscope\_settings controls the compile-time collection, cross reference, and storage of PL/SQLsourcecode identifier data. |
| plsql\_code\_type                    |              | `INTERPRETED`      | `INTERPRETED`, `NATIVE`                                               | no      | PL/SQL code-type.                                                                                                         |
| plsql\_optimize\_level               |              | `2`                | `0` → `3`                                                             | no      | PL/SQL optimize level.                                                                                                    |
| query\_rewrite\_enabled              |              | `TRUE`             | `FALSE`, `TRUE`, `FORCE`                                              | no      | Allow rewrite of queries using materialized views if enabled.                                                             |
| query\_rewrite\_integrity            |              | `ENFORCED`         | `ENFORCED`, `TRUSTED`, `STALE_TOLERATED`                              | no      | Perform rewrite using materialized views with desired integrity.                                                          |
| remote\_dependencies\_mode           |              | `TIMESTAMP`        | `TIMESTAMP`, `SIGNATURE`                                              | no      | Remote-procedure-call dependencies mode parameter.                                                                        |
| replication\_dependency\_tracking    |              | `TRUE`             | `TRUE`, `FALSE`                                                       | yes     | Tracking dependency for Replication parallel propagation.                                                                 |
| resource\_limit                      |              | `FALSE`            | `TRUE`, `FALSE`                                                       | no      | Enforce resource limits in database profiles.                                                                             |
| resourcemanager\_cpu\_allocation     |              | `2`                | `0` → `20`                                                            | no      | ResourceManager CPU allocation.                                                                                           |
| resumable\_timeout                   | seconds      | `0`                | `0` → `2147483647`                                                    | no      | Enables resumable statements and specifies resumable timeout at the system level.                                         |
| sql\_trace                           |              | `FALSE`            | `TRUE`, `FALSE`                                                       | no      | Enable SQL trace.                                                                                                         |
| star\_transformation\_enabled        |              | `FALSE`            | `FALSE`, `TRUE`, `TEMP_DISABLE`                                       | no      | Enable the use of star transformation.                                                                                    |
| timed\_os\_statistics                |              | `0`                | `0` → `1000000`                                                       | no      | The interval at which Oracle collects operating system statistics.                                                        |
| timed\_statistics                    |              | `TRUE`             | `TRUE`, `FALSE`                                                       | no      | Maintain internal timing statistics.                                                                                      |
| trace\_enabled                       |              | `TRUE`             | `TRUE`, `FALSE`                                                       | no      | Enable in-memory tracing.                                                                                                 |
| transactions\_per\_rollback\_segment |              | `5`                | `1` → `10000`                                                         | yes     | Expected number of active transactions per rollback segment.                                                              |

## Values and domain suggestions <a href="#values-and-domain-suggestions" id="values-and-domain-suggestions"></a>

The following parameters require their ranges or default values to be updated according to the described rules.

### Memory

| Parameter                   | Default value                                                                                                                                                                                                                        | Domain                                                       |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------ |
| `db_cache_size`             | `MAX(48MB, 4MB * cpu_num)`                                                                                                                                                                                                           |                                                              |
| `java_pool_size`            | <p><code>24MB</code> if <code>SGA\_TARGET</code> is not set<br><code>0</code> if <code>SGA\_TARGET</code> is set, meaning the lower bound for the pool is automatically determined</p>                                               |                                                              |
| `shared_pool_reserved_size` | `5%` of `shared_pool_size`                                                                                                                                                                                                           |                                                              |
| `shared_pool_size`          | `0` if `sga_target` is set, `128MB` otherwise                                                                                                                                                                                        |                                                              |
| `shared_pool_reserved_size` |                                                                                                                                                                                                                                      | upper bound can’t exceed half the size of `shared_pool_size` |
| `pga_aggregate_target`      | `MAX(10MB, 0.2*sga_target)`                                                                                                                                                                                                          |                                                              |
| `pga_aggregate_limit`       | <p><code>MEMORY\_MAX\_TARGET</code> if <code>MEMORY\_TARGET</code> explicit or<br><code>2 \* PGA\_AGGREGATE\_TARGET</code> if <code>PGA\_AGGREGATE\_TARGET</code> explicit or<br><code>0.9 \* ({MEMORY\_AVAILABLE} - SGA)</code></p> | at least `MAX(2GB, 3MB * db.processes)`                      |
| `hash_area_size`            | `2 * sort_area_size`                                                                                                                                                                                                                 |                                                              |

### Resources

| Parameter              | Default value                                                                                                                                                                     | Domain                                      |
| ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------- |
| `cpu_count`            | <p>should match the available CPUs<br>0 to let the Oracle engine automatically determine the value</p>                                                                            | must not exceed the available CPUs          |
| `gcs_server_processes` | <p><code>0</code> if <code>cluster\_database=false</code><br><code>1</code> for 1-3 CPUs, or if ASM<br><code>2</code> for 4-15 CPUs<code>2+lower(CPUs/32)</code> for 16+ CPUs</p> |                                             |
| `parallel_min_servers` | `CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2`                                                                                                                                        |                                             |
| `parallel_max_servers` | `PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5`                                                                                                            |                                             |
| `sessions`             | `1.5 * processes + 22`                                                                                                                                                            | must be at least equal to the default value |
| `transactions`         | `1.1 * sessions`                                                                                                                                                                  |                                             |

## Constraints <a href="#constraints" id="constraints"></a>

The following tables show a list of constraints that may be required in the definition of the study, depending on the tuned parameters:

### Memory

| Formula                                                                                                                                    | Notes                                       |
| ------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------- |
| `db.memory_target <= db.memory_max_target && db.memory_max_target < {MEMORY_AVAILABLE}`                                                    | Add when tuning automatic memory management |
| `db.sga_max_size + db.pga_aggregate_limit <= db.memory_max_target`                                                                         | Add when tuning SGA and PGA                 |
| `db.sga_target + db.pga_aggregate_target <= db.memory_target`                                                                              | Add when tuning SGA and PGA                 |
| `db.sga_target <= db.sga_max_size`                                                                                                         | Add when tuning SGA                         |
| `db.db_cache_size + db.java_pool_size + db.large_pool_size + db.log_buffer + db.shared_pool_size + db.streams_pool_size < db.sga_max_size` | Add when tuning SGA areas                   |
| `db.pga_aggregate_target <= db.pga_aggregate_limit`                                                                                        | Add when tuning PGA                         |
| `db.shared_pool_reserved_size <= 0.5 * db.shared_pool_size`                                                                                |                                             |
| `db.sort_area_retained_size <= db.sort_area_size`                                                                                          |                                             |

### Other constraints

| Formula                                             | Notes |
| --------------------------------------------------- | ----- |
| `db.sessions < db.transactions`                     |       |
| `db.parallel_min_servers < db.parallel_max_servers` |       |


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.akamas.io/akamas-docs/3.1.3/akamas-reference/optimization-packs/oracle-database-pack/rds-oracle-database-12c.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.