Oracle Database Performance Related Initialization Parameter
Parameters which can be affect db performance except memory or SGA related parameter
1) OPEN_CURSORS :
The Oracle initialization parameter OPEN_CURSORS specifies the number of cursors a session can have
open at any one time. The default value is 300. If any session attempts to open a new cursor but already has the maximum number of cursors open, the Oracle error -1000 will be returned.
2) SESSION_CACHED_CURSORS:
Oracle monitors the SQL statements that are submitted for each session. If it detects the same statement has been submitted multiple times, it moves that statement to cursor cache and keep it open during session run time for subsequent reuse. The SESSION_CACHED_CURSORS parameter controls the number of cursors allowed in the cursor cache.
The default value for SESSION_CACHED_CURSORS varies by Oracle release. If your instance is not configured to cache at least 50 cursors, increase the value of this parameter to 50.
4) DISTRIBUTED_LOCK_TIMEOUT:
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.
5) DB_FILE_MULTIBLOCK_READ_COUNT: (Oracle Doc ID 1398860.1)
This parameter determines how many database blocks are read in a single I/O during:
1) full table scan and
2) Index fast full scan
Optimizer uses this value to calculate the cost of full table scan and index fast full scan.
Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters we can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
the maximum read I/O request size that Oracle can issue to the OS is 1 Megabyte (1MB).
Formula to calculate the db_file_multiblock_read_count:
db_file_multiblock_read_count = min(max I/O size/db_block_size , db_cache_size/(sessions * db_block_size))
OR if you are using a db_block_size of 8192 (8k) and db_file_multiblock_read_count is set to 64 the maximum read size request would be 8192 * 64 = 524,288 bytes or 0.5MB. We could set db_file_multiblock_read_count = 128 to achieve a 1MB read size, but that is the absolute maximum possible.
6) OPTIMIZER_MODE:
Mechanism for result-set retrieval.
A subset of values are available: ALL_ROWS, FIRST_ROWS, FIRST_ROWS_N
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n : The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows: The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows :The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
7) CURSOR_SHARING: CURSOR_SHARING=EXACT (default) Oracle
CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Converts literal SQL to SQL with bind variables, reducing parse overhead.
its values would be :
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SELECT * FROM hr.employees WHERE employee_id = 101
If we use FORCE , then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan.
SIMILAR (Removed from 12c onwards) behaving similar to FORCE in 11.2.0.3
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement. or the degree to which the plan is optimized.
EXACT
if a statement in the pool is not identical, then the database does not share the SQL area. Each statement has its own parent cursor and its own execution plan based on the literal in the statement.
8) FILESYSTEMIO_OPTIONS + DISK_ASYNCH_IO :
There are 4 options available for filesystemio_options: ASYNC, DIRECTIO, SETALL, NONE
and 2 options for DISK_ASYNC_IO : TRUE and FALSE
I/O operations in UNIX and Linux systems typically go through the file system cache, this extra processing does require resources.
Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database file operations.
Operations against raw devices automatically bypass the file system cache.
In Synchronus I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. where as in sychronus I/O processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks associated with I/O operations.
FILESYSTEMIO_OPTIONS=SETALL
DISK_ASYNCH_IO=TRUE
When both parameters are activated to use asynchronous I/O, dbw0 process performs the I/O asynchronously
FILESYSTEMIO_OPTIONS=NONE
DISK_ASYNCH_IO=TRUE
FILESYSTEMIO_OPTIONS is set to NONE. This should normally disable the DISK_ASYNCH_IO when datafiles are located on a regular filesystem (non-ASM). However, since datafiles are located on ASM, FILESYSTEMIO_OPTIONS has no affect in this case. I/O is asynchronous.
FILESYSTEMIO_OPTIONS=NONE
DISK_ASYNCH_IO=FALSE
and
FILESYSTEMIO_OPTIONS=SETALL
DISK_ASYNCH_IO=FALSE
In the last two cases, whatever the filesystemio_options is set, if DISK_ASYNCH_IO is false, IO is done synchronously.
9) OPTIMIZER_INDEX_COST_ADJ:
Adjustment between the cost of an index scan and the cost of a full table scan. Set between 1 and 10 to force index use more frequently. Setting this parameter to a value between 1 and 10 pretty much guarantees index use, however, even when not appropriate
Parameters which can be affect db performance except memory or SGA related parameter
1) OPEN_CURSORS :
The Oracle initialization parameter OPEN_CURSORS specifies the number of cursors a session can have
open at any one time. The default value is 300. If any session attempts to open a new cursor but already has the maximum number of cursors open, the Oracle error -1000 will be returned.
2) SESSION_CACHED_CURSORS:
Oracle monitors the SQL statements that are submitted for each session. If it detects the same statement has been submitted multiple times, it moves that statement to cursor cache and keep it open during session run time for subsequent reuse. The SESSION_CACHED_CURSORS parameter controls the number of cursors allowed in the cursor cache.
The default value for SESSION_CACHED_CURSORS varies by Oracle release. If your instance is not configured to cache at least 50 cursors, increase the value of this parameter to 50.
3) FAST_START_MTTR_TARGET:
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL
4) DISTRIBUTED_LOCK_TIMEOUT:
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.
5) DB_FILE_MULTIBLOCK_READ_COUNT: (Oracle Doc ID 1398860.1)
This parameter determines how many database blocks are read in a single I/O during:
1) full table scan and
2) Index fast full scan
Optimizer uses this value to calculate the cost of full table scan and index fast full scan.
Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters we can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
the maximum read I/O request size that Oracle can issue to the OS is 1 Megabyte (1MB).
Formula to calculate the db_file_multiblock_read_count:
db_file_multiblock_read_count = min(max I/O size/db_block_size , db_cache_size/(sessions * db_block_size))
OR if you are using a db_block_size of 8192 (8k) and db_file_multiblock_read_count is set to 64 the maximum read size request would be 8192 * 64 = 524,288 bytes or 0.5MB. We could set db_file_multiblock_read_count = 128 to achieve a 1MB read size, but that is the absolute maximum possible.
Mechanism for result-set retrieval.
A subset of values are available: ALL_ROWS, FIRST_ROWS, FIRST_ROWS_N
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.
Values:
first_rows_n : The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
first_rows: The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
all_rows :The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
7) CURSOR_SHARING: CURSOR_SHARING=EXACT (default) Oracle
CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Converts literal SQL to SQL with bind variables, reducing parse overhead.
its values would be :
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SELECT * FROM hr.employees WHERE employee_id = 101
If we use FORCE , then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan.
SIMILAR (Removed from 12c onwards) behaving similar to FORCE in 11.2.0.3
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement. or the degree to which the plan is optimized.
EXACT
if a statement in the pool is not identical, then the database does not share the SQL area. Each statement has its own parent cursor and its own execution plan based on the literal in the statement.
8) FILESYSTEMIO_OPTIONS + DISK_ASYNCH_IO :
There are 4 options available for filesystemio_options: ASYNC, DIRECTIO, SETALL, NONE
and 2 options for DISK_ASYNC_IO : TRUE and FALSE
I/O operations in UNIX and Linux systems typically go through the file system cache, this extra processing does require resources.
Bypassing the file system cache reduces CPU requirements, and frees up the file system cache for other non-database file operations.
Operations against raw devices automatically bypass the file system cache.
In Synchronus I/O request is submitted to the operating system, the writing process blocks until the write is complete before continuing processing. where as in sychronus I/O processing continues while the I/O request is submitted and processed. This allows asynchronous I/O to bypass some of the performance bottlenecks associated with I/O operations.
FILESYSTEMIO_OPTIONS=SETALL
DISK_ASYNCH_IO=TRUE
When both parameters are activated to use asynchronous I/O, dbw0 process performs the I/O asynchronously
FILESYSTEMIO_OPTIONS=NONE
DISK_ASYNCH_IO=TRUE
FILESYSTEMIO_OPTIONS is set to NONE. This should normally disable the DISK_ASYNCH_IO when datafiles are located on a regular filesystem (non-ASM). However, since datafiles are located on ASM, FILESYSTEMIO_OPTIONS has no affect in this case. I/O is asynchronous.
FILESYSTEMIO_OPTIONS=NONE
DISK_ASYNCH_IO=FALSE
and
FILESYSTEMIO_OPTIONS=SETALL
DISK_ASYNCH_IO=FALSE
In the last two cases, whatever the filesystemio_options is set, if DISK_ASYNCH_IO is false, IO is done synchronously.
9) OPTIMIZER_INDEX_COST_ADJ:
Adjustment between the cost of an index scan and the cost of a full table scan. Set between 1 and 10 to force index use more frequently. Setting this parameter to a value between 1 and 10 pretty much guarantees index use, however, even when not appropriate
The following select will extract the majority of
parameters relevent to query optimization
col name format a35
col value format a40
SELECT name, value
FROM v$parameter
WHERE name like 'optimizer%'
OR name like 'parallel%'
OR name in ('cursor_sharing',
'db_file_multiblock_read_count',
'hash_area_size',
'hash_join_enabled',
'query_rewrite_enabled',
'query_rewrite_integrity',
'sort_area_size',
'star_transformation_enabled',
'bitmap_merge_area_size',
'partition_view_enabled')
ORDER BY name;
