optimizer_相关参数

SQL> show parameter optimizer_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL> 


Thomas建议:
对于许多系统,应到考虑设置这两个参数为非默认值,至少测试一下两种极端情形:
1. optimizer_index_caching=0 和 optimizer_index_cost_adj=100的默认值. 他们一般适用于许多数据仓库/报表系统
2. optimizer_index_caching=90和 optimizer_index_cost_adj=25的设置,他们一般适用于许多事物处理系统/oltp系统.


alter system set optimizer_index_caching=90 scope=spfile;
alter system set optimizer_index_cost_adj=25  scope=spfile;


optimizer_index_cost_adj
这个初始化参数代表一个百分比,取值范围在1到10000之间.
该参数表示索引扫描和全表扫描成本的比较。缺省值100表示:索引扫描成本等价转换与全表扫描成本。


对于数据仓库和DSS系统要反复调整来取一个合理值。
Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估，在比较的时候，
Oracle会把索引扫描的成本转换为全表扫描的成本与全表扫描的COST进行比较。这个转换需要一个转换因子，
就是Optimizer_index_cost_adj;
Optimizer_index_cost_adj*(index scan cost)=等价的Full Scan cost


所以 optimizer_index_cost_adj = Full Scan Cost / Index Scan Cost


对于自己的系统到底optimizer_index_cost_adj 的值是多少才是最合适的还是要经过自己的反复测试才确定的，不能盲目的改。




SELECT isses_modifiable, issys_modifiable
  FROM v$parameter
 WHERE name = 'optimizer_index_cost_adj';


ISSES_MODIFIABLE ISSYS_MODIFIABLE
---------------- ----------------
TRUE             IMMEDIATE       
1 row selected.
 --说明该参数可以在session级别动态改变，但不能在system级别动态改变


alter session set optimizer_index_cost_adj=100;
alter session set optimizer_index_cost_adj=1000;


在oltp系统中，可以考虑将optimizer_index_cost_adj参数值设小，使系统倾向于使用索引；在dss系统中，则可以考虑适当将该参数调大，影响oracle的决策过程。


这两个参数，都可以影响CBO对执行计划的选择。
一 Optimizer_index_caching
   Optimizer_index_caching是一个百分比的值，取值范围是0到99（缺省值为0），表示能在内存中找到需要的索引数据的可能性。比如optimizer_index_caching的值为50，CBO就会认为，有50％的可能性能找到所需要的索引数据，并根据这一可能性，估算执行计划的成本，选择执行计划。
二 optimizer_index_cost_adj
   Optimizer_index_cost_adj(adj=adjust)表示索引扫描和全表扫描的比值，取值范围是1－10000，默认值是100。跟optimizer_index_caching一样，这个值也是CBO用来计算成本的，并根据计算出来的成本，选择执行计划。假设optimizer_index_cost_adj的值是60，CBO会认为，索引扫描的成本是全表扫描成本的60％，然后根据这个计算SQL的执行成本。