# Oracle Database 12c
The optimization pack for Oracle Database 12c.
## Metrics
### Memory
| Metric | Unit | Description |
| ----------------------------------------- | ------- | --------------------------------------------------------------------------------------------- |
| oracle\_sga\_total\_size | bytes | The current memory size of the SGA. |
| oracle\_sga\_free\_size | bytes | The amount of SGA currently available. |
| oracle\_sga\_max\_size | bytes | The configured maximum memory size for the SGA. |
| oracle\_pga\_target\_size | bytes | The configured target memory size for the PGA. |
| oracle\_redo\_buffers\_size | bytes | The memory size of the redo buffers. |
| oracle\_default\_buffer\_cache\_size | bytes | The memory size for the DEFAULT buffer cache component. |
| oracle\_default\_2k\_buffer\_cache\_size | bytes | The memory size for the DEFAULT 2k buffer cache component. |
| oracle\_default\_4k\_buffer\_cache\_size | bytes | The memory size for the DEFAULT 4k buffer cache component. |
| oracle\_default\_8k\_buffer\_cache\_size | bytes | The memory size for the DEFAULT 8k buffer cache component. |
| oracle\_default\_16k\_buffer\_cache\_size | bytes | The memory size for the DEFAULT 16k buffer cache component. |
| oracle\_default\_32k\_buffer\_cache\_size | bytes | The memory size for the DEFAULT 32k buffer cache component. |
| oracle\_keep\_buffer\_cache\_size | bytes | The memory size for the KEEP buffer cache component. |
| oracle\_recycle\_buffer\_cache\_size | bytes | The memory size for the RECYCLE buffer cache component. |
| oracle\_asm\_buffer\_cache\_size | bytes | The memory size for the ASM buffer cache component. |
| oracle\_shared\_io\_pool\_size | bytes | The memory size for the IO pool component. |
| oracle\_java\_pool\_size | bytes | The memory size for the Java pool component. |
| oracle\_large\_pool\_size | bytes | The memory size for the large pool component. |
| oracle\_shared\_pool\_size | bytes | The memory size for the shared pool component. |
| oracle\_streams\_pool\_size | bytes | The memory size for the streams pool component. |
| oracle\_buffer\_cache\_hit\_ratio | percent | How often a requested block has been found in the buffer cache without requiring disk access. |
### Event waits
| Metric | Unit | Description |
| -------------------------------------------------------------- | ------- | -------------------------------------------------------------------------------------------- |
| oracle\_wait\_class\_commit | percent | The percentage of time spent waiting on the events of class 'Commit'. |
| oracle\_wait\_class\_concurrency | percent | The percentage of time spent waiting on the events of class 'Concurrency'. |
| oracle\_wait\_class\_system\_io | percent | The percentage of time spent waiting on the events of class 'System I/O'. |
| oracle\_wait\_class\_user\_io | percent | The percentage of time spent waiting on the events of class 'User I/O'. |
| oracle\_wait\_class\_other | percent | The percentage of time spent waiting on the events of class 'Other'. |
| oracle\_wait\_class\_scheduler | percent | The percentage of time spent waiting on the events of class 'Scheduler'. |
| oracle\_wait\_class\_idle | percent | The percentage of time spent waiting on the events of class 'Idle'. |
| oracle\_wait\_class\_application | percent | The percentage of time spent waiting on the events of class 'Application'. |
| oracle\_wait\_class\_network | percent | The percentage of time spent waiting on the events of class 'Network'. |
| oracle\_wait\_class\_configuration | percent | The percentage of time spent waiting on the events of class 'Configuration'. |
| oracle\_wait\_event\_log\_file\_sync | percent | The percentage of time spent waiting on the 'log file sync' event. |
| oracle\_wait\_event\_log\_file\_parallel\_write | percent | The percentage of time spent waiting on the 'log file parallel write' event. |
| oracle\_wait\_event\_log\_file\_sequential\_read | percent | The percentage of time spent waiting on the 'log file sequential read' event. |
| oracle\_wait\_event\_enq\_tx\_contention | percent | The percentage of time spent waiting on the 'enq: TX - contention' event. |
| oracle\_wait\_event\_enq\_tx\_row\_lock\_contention | percent | The percentage of time spent waiting on the 'enq: TX - row lock contention' event. |
| oracle\_wait\_event\_latch\_row\_cache\_objects | percent | The percentage of time spent waiting on the 'latch: row cache objects' event. |
| oracle\_wait\_event\_latch\_shared\_pool | percent | The percentage of time spent waiting on the 'latch: shared pool' event. |
| oracle\_wait\_event\_resmgr\_cpu\_quantum | percent | The percentage of time spent waiting on the 'resmgr:cpu quantum' event. |
| oracle\_wait\_event\_sql\_net\_message\_from\_client | percent | The percentage of time spent waiting on the 'SQL\*Net message from client' event. |
| oracle\_wait\_event\_rdbms\_ipc\_message | percent | The percentage of time spent waiting on the 'rdbms ipc message' event. |
| oracle\_wait\_event\_db\_file\_sequential\_read | percent | The percentage of time spent waiting on the 'db file sequential read' event. |
| oracle\_wait\_event\_log\_file\_switch\_checkpoint\_incomplete | percent | The percentage of time spent waiting on the 'log file switch (checkpoint incomplete)' event. |
| oracle\_wait\_event\_row\_cache\_lock | percent | The percentage of time spent waiting on the 'row cache lock' event. |
| oracle\_wait\_event\_buffer\_busy\_waits | percent | The percentage of time spent waiting on the 'buffer busy waits' event. |
| oracle\_wait\_event\_db\_file\_async\_io\_submit | percent | The percentage of time spent waiting on the 'db file async I/O submit' event. |
### Sessions
| Metric | Unit | Description |
| -------------------------------------- | -------- | ------------------------------------------- |
| oracle\_sessions\_active\_user | sessions | The number of active user sessions. |
| oracle\_sessions\_inactive\_user | sessions | The number of inactive user sessions. |
| oracle\_sessions\_active\_background | sessions | The number of active background sessions. |
| oracle\_sessions\_inactive\_background | sessions | The number of inactive background sessions. |
### 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 | `1048576` | `0` → `2147483647` | yes | The amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index. |
| create\_bitmap\_area\_size | megabytes | `8388608` | `0` → `1073741824` | yes | Size of create bitmap buffer for bitmap index. Relevant only for systems containing bitmap indexes. |
| db\_block\_size | bytes | `8192` | `2048` → `32768` | yes | The size of Oracle database blocks. The value of this parameter can be changed only when the database is first created. |
| 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. |
| db\_2k\_cache\_size | megabytes | `0` | `0` → `2097152` | no | Size of cache for 2K buffers. |
| db\_4k\_cache\_size | megabytes | `0` | `0` → `2097152` | no | Size of cache for 4K buffers. |
| db\_8k\_cache\_size | megabytes | `0` | `0` → `2097152` | no | Size of cache for 8K buffers. |
| db\_16k\_cache\_size | megabytes | `0` | `0` → `2097152` | no | Size of cache for 16K buffers. |
| db\_32k\_cache\_size | megabytes | `0` | `0` → `2097152` | no | Size of cache for 32K buffers. |
| hash\_area\_size | kilobytes | `131072` | `0` → `2147483647` | yes | Maximum size of in-memory hash work area maximum amount of memory. |
| java\_pool\_size | megabytes | `24` | `0` → `65536` | 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. |
| lock\_sga | | `FALSE` | `TRUE`, `FALSE` | yes | Lock the entire SGA in physical memory. |
| 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 | `6144` | `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. |
| pga\_aggregate\_limit | megabytes | `2048` | `0` → `4194304` | no | The limit on the aggregate PGA memory consumed by the instance. |
| pga\_aggregate\_target | megabytes | `1024` | `0` → `4194304` | no | The target aggregate PGA memory available to all server processes attached to the instance. |
| result\_cache\_max\_result | percent | `5` | `0` → `100` | no | Maximum result size as a percent of cache the 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\_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` → `2147483647` | no | The maximum amount of the User Global Area memory retained after a sort run completes. |
| sort\_area\_size | kilobytes | `64` | `0` → `8388608` | 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` → `65536` | no | Size of the streams pool. |
| use\_large\_pages | | `TRUE` | `ONLY`, `FALSE`, `TRUE` | yes | Enable the use of large pages for SGA memory. |
### Redo
| Parameter | Unit | Default value | Domain | Restart | Description |
| ---------------------------- | --------- | ------------- | -------------------- | ------- | ----------------------------------------------------------------------------------------------------------------- |
| commit\_logging | | `BATCH` | `IMMEDIATE`, `BATCH` | no | Control how redo is batched by Log Writer. |
| log\_archive\_max\_processes | processes | `4` | `1` → `30` | no | Maximum number of active ARCH processes. |
| log\_buffer | megabytes | `16` | `2` → `8192` | 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 |
| ------------------- | ------- | ------------- | ------------------ | ------- | ---------------------------------------------------------------------------------------------- |
| undo\_retention | seconds | `900` | `0` → `2147483647` | no | Low threshold value of undo retention. |
| undo\_management | | `AUTO` | `MANUAL`, `AUTO` | yes | Instance runs in SMU mode if TRUE, else in RBU mode |
| temp\_undo\_enabled | | `FALSE` | `TRUE`, `FALSE` | no | Split undo log into temporary (temporary objects) and permanent (persistent objects) undo log. |
### Optimizer
| Parameter | Unit | Default value | Domain | Restart | Description |
| ---------------------------------------- | ---- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| optimizer\_adaptive\_plans | | `FALSE` | `TRUE`, `FALSE` | no | Controls adaptive plans, execution plans built with alternative choices based on collected statistics. |
| optimizer\_adaptive\_statistics | | `FALSE` | `TRUE`, `FALSE` | no | Enable the optimizer to use adaptive statistics for complex queries. |
| 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\_inmemory\_aware | | `TRUE` | `TRUE`, `FALSE` | no | Enables all of the optimizer cost model enhancements for in-memory. |
| 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\_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 execution
| 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\_max\_servers | processes | `0` | `0` → `32767` | 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\_threads\_per\_cpu | | `2` | `1` → `128` | no | Number of parallel execution threads per CPU. |
### Resources
| Parameter | Unit | Default value | Domain | Restart | Description |
| -------------------------- | ------------ | ------------- | ------------------ | ------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| cpu\_count | cpus | `0` | `0` → `512` | no | Number of CPUs available for the Oracle instance to use. |
| db\_files | files | `200` | `100` → `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 | `50` | `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` → `32768` | 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 | `800` | `6` → `20000` | yes | The maximum number of OS user processes that can simultaneously connect to Oracle. |
| read\_only\_open\_delayed | | `FALSE` | `TRUE`, `FALSE` | yes | Delay opening of read only files until first access. |
| sessions | sessions | `1262` | `1` → `65536` | 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 |
| ------------------------------------ | --------- | ------------- | ---------------------------------------------------------- | ------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |
| audit\_sys\_operations | | `FALSE` | `TRUE`, `FALSE` | yes | Enable sys auditing |
| audit\_trail | | `NONE` | `NONE`, `OS`, `DB`, `DB, EXTENDED`, `XML`, `XML, EXTENDED` | yes | Configure system auditing. |
| gcs\_server\_processes | processes | `0` | `0` → `100` | yes | The number of background GCS server processes to serve the inter-instance traffic among Oracle RAC instances. |
| java\_jit\_enabled | | `TRUE` | `TRUE`, `FALSE` | no | Enables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine. |
| fast\_start\_mttr\_target | seconds | `0` | `0` → `3600` | no | number of seconds the database should take to perform crash recovery of a single instance. This parameter impacts the time between checkpoints. |
| recyclebin | | `ON` | `ON`, `OFF` | no | Allow recovering of dropped tables. |
| statistics\_level | | `TYPICAL` | `BASIC`, `TYPICAL`, `ALL` | no | Level of collection for database and operating system statistics. |
| transactions\_per\_rollback\_segment | | `5` | `1` → `10000` | yes | Expected number of active transactions per rollback segment. |
| filesystemio\_options | | `asynch` | `none`, `setall`, `directIO`, `asynch` | yes | Specifies I/O operations for file system files. |
## Values and domain suggestions
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` | 24MB if SGA\_TARGET is not set
0 if SGA\_TARGET is set, meaning the lower bound for the pool is automatically determined
| |
| `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` | MEMORY\_MAX\_TARGET if MEMORY\_TARGET explicit or
2 \* PGA\_AGGREGATE\_TARGET if PGA\_AGGREGATE\_TARGET explicit or
0.9 \* ({MEMORY\_AVAILABLE} - SGA)
| at least `MAX(2GB, 3MB * db.processes)` |
| `hash_area_size` | `2 * sort_area_size` | |
### Resources
| **Parameter** | **Default value** | **Domain** |
| ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------- |
| `cpu_count` | should match the available CPUs
0 to let the Oracle engine automatically determine the value
| must not exceed the available CPUs |
| `gcs_server_processes` | 0 if cluster\_database=false
1 for 1-3 CPUs, or if ASM
2 for 4-15 CPUs2+lower(CPUs/32) for 16+ CPUs
| |
| `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.1 * processes + 5` | must be at least equal to the default value |
| `transactions` | `1.1 * sessions` | |
## Constraints
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` | |