RDS Oracle Database 12c

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

Metrics

Memory

MetricUnitDescription

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

MetricUnitDescription

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

MetricUnitDescription

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

MetricUnitDescription

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

ParameterUnitDefault valueDomainRestartDescription

bitmap_merge_area_size

kilobytes

1024

02097152

yes

The amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index.

create_bitmap_area_size

megabytes

8192

02097152

yes

Size of create bitmap buffer for bitmap index. Relevant only for systems containing bitmap indexes.

db_cache_size

megabytes

48

02097152

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

02097151

yes

Maximum size of in-memory hash work area maximum amount of memory.

java_pool_size

megabytes

24

016384

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

065536

no

The size of large pool allocation heap.

memory_max_target

megabytes

8192

1522097152

yes

The maximum value to which a DBA can set the MEMORY_TARGET initialization parameter.

memory_target

megabytes

6864

02097152

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

02147483647

no

Size of the olap page pool.

pga_aggregate_limit

megabytes

2048

02097152

no

The limit on the aggregate PGA memory consumed by the instance.

pga_aggregate_target

megabytes

1024

02097152

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

0100

no

Maximum result size as a percent of the cache size.

result_cache_max_size

megabytes

0

065536

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

010000

no

The expiration in minutes of remote objects. High values may cause stale answers.

sga_max_size

megabytes

8192

02097152

yes

The maximum size of the SGA for the lifetime of the instance.

sga_min_size

megabytes

2920

01048576

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

02097152

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

12048

yes

The shared pool space reserved for large contiguous requests for shared pool memory.

shared_pool_size

megabytes

0

065536

no

The size of the shared pool.

sort_area_retained_size

kilobytes

0

02097151

no

The maximum amount of the User Global Area memory retained after a sort run completes.

sort_area_size

kilobytes

64

02097151

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

02097152

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

ParameterUnitDefault valueDomainRestartDescription

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

130

no

Maximum number of active ARCH processes.

log_buffer

megabytes

16

2256

yes

The amount of memory that Oracle uses when buffering redo entries to a redo log file.

log_checkpoint_interval

blocks

0

02147483647

no

The maximum number of log file blocks between incremental checkpoints.

log_checkpoint_timeout

seconds

1800

02147483647

no

Maximum time interval between checkpoints. Guarantees a no buffer remains dirty for more than the specified time.

Undo

ParameterUnitDefault valueDomainRestartDescription

db_flashback_retention_target

minutes

1440

302147483647

no

Maximum Flashback Database log retention time.

undo_retention

seconds

900

02147483647

no

Low threshold value of undo retention.

Optimizer

ParameterUnitDefault valueDomainRestartDescription

optimizer_capture_sql_plan_baselines

FALSE

TRUE, FALSE

no

Automatic capture of SQL plan baselines for repeatable statements

optimizer_dynamic_sampling

2

011

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

0100

no

Adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

optimizer_index_cost_adj

100

110000

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

ParameterUnitDefault valueDomainRestartDescription

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

ParameterUnitDefault valueDomainRestartDescription

parallel_degree_policy

MANUAL

MANUAL, LIMITED, AUTO

no

Policy used to compute the degree of parallelism (MANUAL/LIMITED/AUTO).

parallel_execution_message_size

16384

214832768

yes

Message buffer size for parallel execution.

parallel_force_local

FALSE

TRUE, FALSE

no

Force single instance execution.

parallel_max_servers

processes

0

03600

no

The maximum number of parallel execution processes and parallel recovery processes for an instance.

parallel_min_servers

processes

0

02000

no

The minimum number of execution processes kept alive to service parallel statements.

parallel_min_percent

percent

0

0100

yes

The minimum percentage of parallel execution processes (of the value of PARALLEL_MAX_SERVERS) required for parallel execution.

Resources

ParameterUnitDefault valueDomainRestartDescription

circuits

circuits

10

03000

no

The total number of virtual circuits that are available for inbound and outbound network sessions.

cpu_count

cpus

0

0512

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

20020000

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

065535

no

The maximum number of open cursors (handles to private SQL areas) a session can have at once.

open_links

connections

4

0255

yes

The maximum number of concurrent open connections to remote databases in one session.

open_links_per_instance

connections

4

02147483647

yes

Maximum number of migratable open connections globally for each database instance.

processes

processes

100

8020000

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

065535

no

Number of session cursors to cache.

session_max_open_files

10

150

yes

Maximum number of open files allowed per session.

sessions

sessions

1262

10065532

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

42147483647

yes

The maximum number of concurrent transactions.

Other parameters

ParameterUnitDefault valueDomainRestartDescription

aq_tm_processes

1

040

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

060000

yes

Maximum time before checking the database for changes related to the queries cached on the client.

client_result_cache_size

kilobytes

0

02147483647

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

01024

no

Db block to be read each IO.

db_keep_cache_size

megabytes

0

02097152

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

116777216

no

Database recovery files size limit.

db_recycle_cache_size

megabytes

0

02097152

no

Size of RECYCLE buffer pool for standard block size buffers.

db_writer_processes

1

136

yes

Number of background database writer processes to start.

ddl_lock_timeout

0

01000000

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

12147483647

yes

Number of seconds a distributed transaction waits for a lock.

dml_locks

5552

02000000

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

02147483647

yes

Max allowed size in bytes of a Java sessionspace.

java_soft_sessionspace_limit

bytes

0

02147483647

yes

Warning limit on size in bytes of a Java sessionspace.

job_queue_processes

1000

01000

no

Maximum number of job queue slave processes.

object_cache_max_size_percent

percent

10

0100

no

Percentage of maximum size over optimal of the user sessions object cache.

object_cache_optimal_size

kilobytes

100

067108864

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

03

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

020

no

ResourceManager CPU allocation.

resumable_timeout

seconds

0

02147483647

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

01000000

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

110000

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

ParameterDefault valueDomain

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

ParameterDefault valueDomain

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

FormulaNotes

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

FormulaNotes

db.sessions < db.transactions

db.parallel_min_servers < db.parallel_max_servers

Last updated