Oracle Database 19c
The optimization pack for Oracle Database 19c.
Metrics
Memory
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
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
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
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
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
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
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
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
19.1.0
19.1.0, 18.1.0, 12.2.0.1, 12.1.0.2, 12.1.0.1, 11.2.0.4
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
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
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
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
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
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
db.sessions < db.transactions
db.parallel_min_servers < db.parallel_max_servers
Last updated