- CURSOR_SHARING
determines what kind of SQL statements can shared the same cursors. The possible values you can use are:
FORCE
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.
SIMILAR
SIMILAR causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literas affect either the meaning of the statement or the degree to which the plan is optimized. Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS application, or application that used stored outlines.
EXACT
EXACT only allows statements with identical text to share the same cursor. This is the default.
- DB_FILE_MULTIBLOCK_READ_COUNT
is one of the parameters you can use to minimized I/O during table scans or index fast full scan. It specified 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 or an index fast full scan depends on factors, such as the size of the segment, the multiblock read count, and whether parallel execution is being utilized for the operation.
As of Oracle Database 10gR2 the default value of this parameter is a value that corresponds to the maximum I/O size that can be perform efficiently. This value is platform dependent and is 1 MB for most platforms.
Because the parameter is expressed in blocks, it automatically compute a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size.
Note that if the number of sessions is extremely large, the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers. Even tough the default value may be a large value, the optimizer does not favor large plans if you do not see this parameter. It would do so only if you explicitly set this parameter to a large value. Basically, if this parameter is not set explicitly (o is set at 0), the optimizer uses a default value of 8 when costing full table scans and index fast full scans.
OLTP and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to select full table scan over an index, if the value of this parameter is hight.
- PGA_AGGREGATE_TARGET
specifies the target aggregate PGA memory available to all server processes attached to the instance. Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators, such as sort, group by, has-join, bitmap merge, and bitmap create, are automatically sized.
A nonzero value for the PGA_AGGREGATE_TARGET parameter is the default since, unless you specify otherwise, the system sets it to 20% of the SGA or 10MB, whichever is greater.
Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL work areas are sized using the *_AREA_SIZE parameters. The system attempts to keep the amount of private memory below the targe specified by this parameter by adapting the size of the work areas to private memory.
When increasing the value of this parameter, you indirectly increase the memory allotted to work area. Consequently, more memory-intensive operations are able to run fully in memory and a less number of them work their way over to the disk. When setting this parameter, you should examine the total memory on your system that is available to the Oracle instance and subtract the SGA. You can assign the remaining memory to PGA_AGGREGATE_TARGET.
- STAR_TRANSFORMATION_ENABLED
determines whether a cost based query transformation is applied to star queries.
- RESULT_CACHE_MODE
The query optimizer manages the result cache mechanism depending on the settings of the RESULT_CACHE_MODE parameter in the initialization parameter file. You can use this parameter to determine whether or not the optimizer automatically sends the results of queries to the result cache. The possible parameter values are:
MANUAL
when set to MANUAL (the default), you must specify, by using the RESULT CACHE hint, that a particular result is to be stored in the cache.
FORCE
When set FORCE, all results are stored in the cache. For the FORCE setting, if the statement contains a [NO_]RESULT_CACHE hint, the hint take precedence over the parameter setting.
- RESULT_CACHE_MAX_SIZE
- RESULT_CACHE_MAX_RESULT
The memory size allocated to the result cache depends on the memory size of the SGA as well as the memory management system. You can change the memory allocated to the result cache by setting the RESULT_CACHE_MAX_SIZE parameter. The result cache is disabled if you set its value to 0. The value of this parameter is rounded to the largest multiple of 32 KB that is not greater than the specified value. If the rounded value is 0, the feature is disabled.
Use the RESULT_CACHE_MAX_RESULT parameter to specify the maximum amount of cache memory that can be used by any single result. The default value is 5%, but you can specify any percentage value between 1 and 100.
- RESULT_CACHE_REMOTE_EXPIRATION
Use the RESULT_CACHE_REMOTE_EXPIRATION parameter to specify the time (in number of minutes) for which a result that depends on remote database objects remains valid. The defaul value is 0, which implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers, for example, if the remote table used by a result is modified at the remote database .
- OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_CACHING controls the costing of an index probe in conjunction with a nested loop or an inlist iterator. The range of value 0 - 100 for OPTIMIZER_INDEX_CACHING indicates the percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loop and inlist iteratos.
A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjust the cost of an index probe or nested loop accordingly. The default for this parameter is 0, which results in default optimizer behavior. Use caution when using this parameter because execution plans can change in favor of index caching.
- OPTIMIZER_INDEX_COST_ADJ
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 range of values is 1 to 100. The default for this parameter is 100%, 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 50 makes the index access path look half as expensive as normal.
OPTIMIZER_FEATURES_ENABLED
OPTIMIZER_FEATURES_ENABLED acts as umbrella parameter for enabling a series of optimizer features based on an Oracle release number
For example, if you upgrade your database from 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 to enhancements introduced in the releases up to an including release 11.1 by setting the parameter to 11.1.0.6.
However, it is not recommended to explicitly set the OPTIMIZER_FEATURES_ENABLED parameter to an earlier release. To avoid possible SQL performance regression that may result from execution plan changes, consider using SQL plan management instead.
- OPTIMIZER_MODE
OPTIMIZER_MODE establishes the default behavior for selecting an optimizing approach for either the instance or your session. The possible values are
ALL_ROWS
The ALL_ROWS value causes the optimizer to use a cost based approach for all SQL statements in the session regardless of the presence of statistics an optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.
FIRST_ROWS_n
When you use the FIRST_ROWS_n value, the optimizer uses a cost based approach, regardless of the presence of statistics, and optimizes with a goal best response time to return the first n number of rows; n can equal 1, 10, 100 or 1000.
FIRST_ROWS
The FIRST_ROWS value causes the optimizer to use a mix of cost and heuristics for find the best plan for fast delivery of the first few rows. Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability; use FIRS_ROW_n instead.
- OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
- OPTIMIZER_USER_SQL_PLAN_BASELINES
- OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic recvognition of repeatable SQL statements, as well as the generations of SQL plan baselines for such statements.
OPTIMIZER_USER_SQL_PLAN_BASELINES enables or disabales the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL steatment being compiled. If one is found in SQL Management Base, the optimizer costs each of the baseline plans and pick one with the lowest cost.
OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sapling performed by the optimizer. If OPTIMIZER_FEATURE_ENABLE is set to 10.0.0 or later, the default value is 2. If it is set to 9.2.0, the default value is 1, and if it is set to 9.0.1 or earlier, the default value is 0.
- OPTIMIZER_USE_INVISIBLE_INDEXES
- OPTIMIZER_USE_PENDING_STATISTICS
OPTIMIZER_USE_INVISIBLE_INDEXES enables or disables the use of invisible indexes.
OPTIMIZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses pending statisctis when compiling SQL statements.