OPTIMIZER_MODE、optimizer_index_cost_adj

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list  iterators.The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is  used to access the inner table depends on the caching of that index in the buffer cache.You can modify the optimizer's  assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value  between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache
可以理解为在buffer cache里面找到索引块的百分比，默认0

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
optimizer_index_cost_adj可以理解为把索引扫描的成本转换为全表扫描的成本百分比，默认100%表示索引扫描成本等价转换为全表扫 描成本。
Oracle在选择不同的访问路径时，会对全表扫描和索引扫描进行比较评估，在比较的时候，Oracle会把索引扫描的成本转换为全表扫描 的成本，和全表扫描的COST进行比较。这个转换需要一个转换因子，就是Optimizer_index_cost_adj;
Optimizer_index_cost_adj*(index scan cost)=等价的Full Scan cost


比如oracle算出全表扫描成本为20，索引扫描为30
如果设置optimizer_index_cost_adj等于50，则索引扫描*50%=15=等价的全表扫描为15，这个时候比算出的全表扫描要低，所以走索引 扫描。
如果设置optimizer_index_cost_adj等于100，则索引扫描*100%=30=等价的全表扫描为30，这个时候比算出的全表扫描要高，所以走全 表扫描
20/30=66.7%,说明临界值是67%，当大于67%比如100%则走全表扫描，当小于67%比如50%则走索引扫描



10g开始，optimizer_mode替代了以上两个参数的作用
optimizer_mode=ALL_ROWS更容易走全表扫描
OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

first_rows_n:The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the  first n rows (where n = 1, 10, 100, 1000).
all_rows:The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of  best throughput (minimum resource use to complete the entire statement).


OPTIMIZER_MODE：告诉CBO在生成执行计划的时候的一个方向。
- 以响应时间优先：first_rows_N
要求不考虑代价，最快的返回前N条记录，N为1,10,100,1000
通常会产生索引扫描，或者nested loop join的执行计划。
适用于OLTP系统。

- 以吞吐量优先：all_rows
要求以最小资源开销为目的，返回所有记录。
通常会产生全表扫描，或者hash join的执行计划。
适用于DW或者混合系统。

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

转载于:http://blog.itpub.net/30126024/viewspace-2141984/