oracle查询优化相关参数

QueryOptimizer一共经历了两个历史阶段：RBO和CBO。RBO时代，Oracle执行计划是通过一系列固化的规则进行执行计划生成。而CBO时代，则是利用系统统计量进行各种执行路径试算，获取相对相对成本最低的执行计划.

OPTIMIZER_MODE参数

控制执行计划的优化方法

ALL_ROWS不管是不是有统计信息，全部采用基于成本的优化方法，返回最大数的量数据

FIRST_ROWS_n不管是不是有统计信息，全部采用基于成本的优化方法并以最快的速度，返回前N行记录

FIRST_ROWS使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法

第一个是返回所有行的最小代价，第二个是返回前N行的最小代价，最后一个是返回前面的少量行的最好计划

修改全部session的optimizer_mode

alter system setoptimizer_mode=all_rows;

修改当前session的optimizer_mode

alter session setoptimizer_mode=all_rows;

OPTMIZER_DYNAMIC_SAMPLING参数

表示oracle查询优化时动态采样的级别，0-10

0不会采样，10级别最高，如果没有统计信息，采样将会很慢。

OPTIMIZER_FEATURES_ENABLE参数启用优化器采用版本行为

如果想采用旧版本的特性,需要设置OPTIMIZER_FEATURES_ENABLE为旧本本版本号.
如果升级数据库版本等,OPTIMIZER_FEATURES_ENABLE的优化器版本也将跟着改变.

Showparameter OPTIMIZER_FEATURES_ENABLE;

SQL>alter system set OPTIMIZER_FEATURES_ENABLE="11.1.0.6";

如下的参数控制查询优化的行为

CURSOR_SHARING转换绑定变量的值，执行计划基于现存绑定变量。

DB_FILE_MULTIBLOCK_READ_COUNT查询是IO读操作块的数量，如果全表扫描将该值增大，默认为8.

SQL>show parameter DB_FILE_MULTIBLOCK_READ_COUNT;

SQL>alter system set DB_FILE_MULTIBLOCK_READ_COUNT=128;

OPTIMIZER_INDEX_CACHING这个参数影响嵌套循环连接索引在buffercache中的比例，值在0-100之间,100时优化器认为100%可以在内存中找到索引。

SQL>show parameter OPTIMIZER_INDEX_CACHING;

SQL>alter system set OPTIMIZER_INDEX_CACHING=0;

OPTIMIZER_INDEX_COST_ADJ这个参数优化器用来计算cost的，这个参数可以用来调整使用索引的代价，默认值是100，范围是1-10000，如果设为10,表示通过索引路径访问是正常通过索引路径访问的1/10.

SQL>show parameter OPTIMIZER_INDEX_COST_ADJ;

altersystem set OPTIMIZER_INDEX_COST_ADJ=100;

OPTIMIZER_MODE设置实例优化器的模式，ALL_ROWS,FIRST_ROWS_n,FIRST_ROWS

SQL>show parameter optimizer_mode;

SQL>alter system set optimizer_mode=all_rows;

PGA_AGGREGATE_TARGET自动控制sort与join操作的内存大小。

STAR_TRANSFORMATION_ENABLED位图索引查询时设置为true

理解查询优化

查询优化根据访问路径与提示产生隐式的执行计划。

查询优化估计根据每个执行计划的成本，包括数据分布，表数据的存储，索引，语句访问的

分区，计算访问路径，joinorder,io,cpu及内存成本。

最后比较执行计划选择最低成本的执行计划。