Parameters that control the behavior of Query Optimizer

1)CURSOR_SHARING: •The optimizer generates the execution plan based on the presence of the bind variables but not the actual literal values.

•Based on the settings of this parameter -CURSOR_SHARING it converts literal values in SQL statements to bind variables and affect the execution plan of SQL statements.

•This parameter determines what kind of SQL statements can share the same cursors and can have any of either three values EXACT or SIMILAR or FORCE.

•If it is set to EXACT then it only allows statements with identical text to share the same cursor.

•SIMILAR settings 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. SIMILAR is default.

•Forces specified that statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

2)DB_FILE_MULTIBLOCK_READ_COUNT:•This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan.

•The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans.

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

•If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.

3)OPTIMIZER_INDEX_CACHING:•This parameter controls the costing of an index probe in conjunction with a nested loop.

•The range of values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list iterators.

•A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop accordingly.

•Use caution when using this parameter because execution plans can change in favor of index caching.

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

5)OPTIMIZER_MODE:This parameter is discussed on About Parameter OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET: This parameter automatically controls the amount of memory allocated for sorts and hash joins. Larger amounts of memory allocated for sorts or hash joins reduce the optimizer cost of these operations. This parameter is discussed on About PGA_AGGREGATE_TARGET parameter
7)STAR_TRANSFORMATION_ENABLED: •STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.

•If it is set to TRUE the optimizer will consider performing a cost-based query transformation on the star query.

•If it is set to FALSE the transformation will not be applied.

•If it is set to TEMP_DISABLE the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.