RDS Oracle Database 12c

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

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

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.5 * processes + 22	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