影响优化器的几个参数

Controlling the Behavior. of the Query Optimizer
This section lists some initialization parameters that can be used to control the behavior. of the query optimizer. These parameters can be used to enable various optimizer features in order to improve the performance of SQL execution.

CURSOR_SHARING

This parameter converts literal values in SQL statements to bind variables. Converting the values improves cursor sharing and can affect the execution plans of SQL statements. The optimizer generates the execution plan based on the presence of the bind variables and not the actual literal values.

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.

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.
该初始化参数表示一个百分比，0％～99％，缺省值0 ，对cbo来说，意味着0％的数据块（使用索引访问）可以在oracle‘s SGA的buffer cache中发现。即所有的对索引的访问都将需要物理读（每一个对buffer cache的逻辑读都产生一个对I/o子系统的物理读），也可以看作是：对buffer cache 0％的hit ratio。该参数只影响CBO计算访问索引块时候的成本，和涉及的表无关。
This parameter applies only to the CBO’s calculations of accesses for blocks in an index, not for the blocks in the table related to the index.

OPTIMIZER_INDEX_COST_ADJ

This parameter can be used to adjust the cost of index probes. The range of values is 1 to 10000. The default value is 100, which means that indexes are evaluated as an access path based on the normal costing model. A value of 10 means that the cost of an index access path is one-tenth the normal cost of an index access path.
该初始化参数也表示一个百分比，0～10000，表示索引访问的i/o开销相对于全表扫描的i/o开销。缺省值100 ，对cbo来说，表示索引访问的开销和全表扫描是等效的。（索引访问花费的时间和全表扫描花费的时间几乎是相等的。）
The default value of 100 indicates to the cost-based optimizer

OPTIMIZER_MODE

This initialization parameter sets the mode of the optimizer at instance startup. The possible values are ALL_ROWS, FIRST_ROWS_n, and FIRST_ROWS. For descriptions of these parameter values, see "OPTIMIZER_MODE Initialization Parameter".

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. See "PGA Memory Management".

STAR_TRANSFORMATION_ENABLED

This parameter, if set to true, enables the query optimizer to cost a star transformation for star queries. The star transformation combines the bitmap indexes on the various fact table columns.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-531615/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-531615/