OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	true | false
Basic	No

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.

Note:

1. This parameter was introduced in 11g.

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES’;

Oracle initializatoin parameters

OPTIMIZER_DYNAMIC_SAMPLING

Property	Description
Parameter type	Integer
Default value	If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	0 to 10

OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer.

Note:

1. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_DYNAMIC_SAMPLING’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
optimizer_dynamic_sampling	2	TRUE	TRUE	IMMEDIATE	TRUE	FALSE	optimizer dynamic sampling

Oracle initializatoin parameters

OPTIMIZER_FEATURES_ENABLE

Property	Description
Parameter type	String
Syntax	OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.1.0.6 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5 | 11.1.0.6 | 11.1.0.7 }
Default value	11.1.0.7
Modifiable	ALTER SESSION, ALTER SYSTEM

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
For example, if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.1 optimizer behavior, you can do so by setting this parameter to 10.1.0. At a later time, you can try the enhancements introduced in releases up to and including release 11.1 by setting the parameter to 11.1.0.6.
Note:
1. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_FEATURES_ENABLE’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
optimizer_features_enable	10.1.0	FALSE	TRUE	IMMEDIATE	TRUE	FALSE	optimizer plan compatibility parameter

Oracle initializatoin parameters

More Oracle DBA tips, please visit Oracle DBA Tips  

OPTIMIZER_INDEX_CACHING

Property	Description
Parameter type	Integer
Default value	0
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	0 to 100

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.

Note:

1. Introduced:             8.0.5

2. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_INDEX_CACHING’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
optimizer_index_caching	0	TRUE	TRUE	IMMEDIATE	TRUE	FALSE	optimizer percent index caching

Oracle initializatoin parameters

OPTIMIZER_INDEX_COST_ADJ

Property	Description
Parameter type	Integer
Default value	100
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	1 to 10000

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

Note:

1. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_INDEX_COST_ADJ’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
optimizer_index_cost_adj	100	TRUE	TRUE	IMMEDIATE	TRUE	FALSE	optimizer index cost adjustment

Oracle initializatoin parameters

OPTIMIZER_MODE

Property	Description
Parameter type	String
Syntax	OPTIMIZER_MODE = { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value	all_rows
Modifiable	ALTER SESSION, ALTER SYSTEM

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

Note:

1. Query for the current value of the parameter

select name, value, isdefault, isses_modifiable, issys_modifiable,

isinstance_modifiable, isdeprecated, description

from v$parameter

where upper(name) = ‘OPTIMIZER_MODE’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
optimizer_mode	ALL_ROWS	TRUE	TRUE	IMMEDIATE	TRUE	FALSE	optimizer mode

Oracle initializatoin parameters