The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
All Rows
10g和11g中的默认值，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）.
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）.
修改optimizer_mode： sql>alter session set optimizer_mode=first_rows/all_rows （3）OPTIMIZER_INDEX_COST_ADJ参数 参数OPTIMIZER_INDEX_COST_ADJ可以理解为Oracle执行多块（MultiBlock）I/O（比如全表扫描）的代价与执行单块（Single-block）I/O代价的相对比例。 OPTIMIZER_INDEX_COST_ADJ通过指明索引I/O代价与扫描全表I/O代价的相对比值来影响CBO的行为，取值越小，CBO越倾向于使用索引，取值越大，越倾向于全表扫描。而缺省值100，指明缺省下，二者的代价是相等。 该参数从某种意义上来说可以决定使用使用索引，如果统计信息准确，但是执行计划又没走索引，可以将该值调小一点。 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. 注意： 9i中，如果相关的segment（表段，索引段等）没有做统计分析的时候，会选择走RBO。 10g以后版本，如果segment 没有统计信息，那么Oracle 会使用动态采样来收集统计的信息。 这个信息不是很准确，但也可以提供一定的参考。 有关动态采样的信息参考： Oracle 分析及动态采样 http://www.linuxidc.com/Linux/2011-07/39437.htm
易网时代-编程资源站