The optimization pack for Oracle Database 12c.
| Metric | Unit | Description |
|---|---|---|
The following parameters require their ranges or default values to be updated according to the described rules:
The following tables show a list of constraints that may be required in the definition of the study, depending on the tuned parameters.
The Oracle Database optimization pack allows monitoring of an Oracle instance and exploring the configuration space of its initialization parameters. In this way, an Akamas study can achieve goals such as maximizing the throughput of an Oracle-backed application or minimizing its resource consumption, thus reducing costs.
The main tuning areas covered by the parameters provided in this optimization pack are:
SGA memory management
PGA memory management
SQL plan optimization
Approximate query execution
The optimization pack also includes metrics to monitor:
Memory allocation and utilization
Sessions
Query executions
Wait events
These component types model different Oracle Database releases, either as on-premise or cloud solutions. They provide the initialization parameters the workflow can apply through the , and a set of metrics to monitor the instance performances.
Notice that for the Oracle Database hosted on Amazon RDS, a subset of initialization parameters can be applied in the workflow to interact with the .
| Component Type | Description |
|---|
Here’s the command to install the Oracle Database optimization pack using the Akamas CLI:
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Formula | Notes |
|---|---|
| Formula | Notes |
|---|---|
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.
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.
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.
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.
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.
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_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_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.
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_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.
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.
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.
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
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
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
db.sessions < db.transactions
db.parallel_min_servers < db.parallel_max_servers
Oracle Database 12c |
Oracle Database 18c |
Oracle Database 19c |
Oracle Database 11g on Amazon RDS |
Oracle Database 12c on Amazon RDS |
The optimization pack for Oracle Database 18c.
| Metric | Unit | Description |
|---|---|---|
The following parameters require their ranges or default values to be updated according to the described rules:
The following tables show a list of constraints that may be required in the definition of the study, depending on the tuned parameters.
The optimization pack for Oracle Database 12c on Amazon RDS.
| Metric | Unit | Description |
|---|---|---|
The following parameters require their ranges or default values to be updated according to the described rules.
The following tables show a list of constraints that may be required in the definition of the study, depending on the tuned parameters:
The optimization pack for Oracle Database 19c.
| Metric | Unit | Description |
|---|---|---|
The following parameters require their ranges or default values to be updated according to the described rules:
The following tables show a list of constraints that may be required in the definition of the study, depending on the tuned parameters.
The optimization pack for Oracle Database 11g on Amazon RDS.
| Metric | Unit | Description |
|---|---|---|
The following parameters require their ranges or default values to be updated according to the described rules.
The following tables show a list of constraints that may be required in the definition of the study, depending on the tuned parameters:
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Formula | Notes |
|---|---|
| Formula | Notes |
|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Default value | Domain |
|---|---|---|
| Parameter | Default value | Domain |
|---|---|---|
| Formula | Notes |
|---|---|
| Formula | Notes |
|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Formula | Notes |
|---|---|
| Formula | Notes |
|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Metric | Unit | Description |
|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Unit | Default value | Domain | Restart | Description |
|---|---|---|---|---|---|
| Parameter | Default value | Domain |
|---|---|---|
| Parameter | Default value | Domain |
|---|---|---|
| Formula | Notes |
|---|---|
| Formula | Notes |
|---|---|
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.
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.
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.
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.
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).
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.
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_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.
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_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.
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.
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.
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
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
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
db.sessions < db.transactions
db.parallel_min_servers < db.parallel_max_servers
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.
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.
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.
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.
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).
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.
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_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.
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_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.
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.
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.
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
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
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
db.sessions < db.transactions
db.parallel_min_servers < db.parallel_max_servers
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.
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.
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.
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.
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).
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.
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_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.
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_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.
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.
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.
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
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
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
db.sessions < db.transactions
db.parallel_min_servers < db.parallel_max_servers
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.
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.
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.
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.
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).
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 → 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.
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_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.
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.
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.
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.
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
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
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
db.sessions < db.transactions
db.parallel_min_servers < db.parallel_max_servers