Oracle Database 18c

The optimization pack for Oracle Database 18c.

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_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 → 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.
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	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 → 2147483647	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_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		18.1.0	18.1.0, 12.2.0.1, 12.1.0.2, 12.1.0.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	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_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.
parallel_threads_per_cpu		2	1 → 128	no	Number of parallel execution threads per CPU.

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 → 2048	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_invalidation		IMMEDIATE	DEFERRED, IMMEDIATE	no	Whether deferred cursor invalidation or immediate cursor invalidation is used for DDL statements by default.
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.
read_only_open_delayed		FALSE	TRUE, FALSE	yes	Delay opening of read only files until first access.
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	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_trail		NONE	NONE, OS, DB, 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_recycle_cache_size	megabytes	0	0 → 2097152	no	Size of RECYCLE buffer pool for standard block size buffers.
db_writer_processes		1	1 → 256	yes	Number of background database writer processes to start.
dbwr_io_slaves		0	0 → 50	yes	The number of I/O server processes used by the DBW0 process.
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.
fast_start_parallel_rollback		LOW	FALSE, LOW, HIGH	no	Max number of parallel recovery slaves that may be used.
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.
java_max_sessionspace_size	bytes	0	0 → 2147483647	yes	Max allowed 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.
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.
recyclebin		ON	ON, OFF	no	Allow recovering of dropped tables.
replication_dependency_tracking		TRUE	TRUE, FALSE	yes	Tracking dependency for Replication parallel propagation.
resourcemanager_cpu_allocation		2	0 → 20	no	ResourceManager CPU allocation.
sql_trace		FALSE	TRUE, FALSE	no	Enable SQL trace.
star_transformation_enabled		FALSE	FALSE, TRUE, TEMP_DISABLE	no	Enable the use of star transformation.
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