Oracle优化器和执行计划

1. 优化器(Optimizer)是sql分析和执行的优化工具，它负责制定sql的执行计划，负责保证sql执行效率最高，比如决定oracle以什么方式访问数据，全表扫描(full table scan)还是索引范围(index range scan)扫描，还是全索引快速扫描(index fast full scan, INDEX_FFS),对于表关联查询，是用什么方式关联。有2种优化器，RBO和CBO，从oracle 10g开始，RBO已经被弃用，但是仍可以通过hint的方式使用。

2. RBO执行机制：在优化器里嵌入若干种规则，执行的sql符合什么规则，则按照规则制定执行计划，这些规则是按照优先级排列。 select /*+ rule */ * from t where id=1; 使用hint方式强制使用RBO优化器执行。

3. CBO执行机制：获取所有执行计划相关信息，通过对这些信息的分析，最后得出一个代价最小的执行计划作为最终的执行计划。

4. 即使在表，索引没有被分析的时候，oracle仍会使用CBO(从oracle 10g开始，RBO已经被弃用), 此时oracle会使用动态采样，在分析sql的时候，动态的收集表，索引上的一些数据块，使用这些数据块的信息及字典中关于这些对象的信息来计算出执行计划的代价，进而挑选出最优的执行计划。动态采样只有在sql执行的第一次，即硬分析阶段使用，后续的软分析不再使用动态采样，直接使用第一次sql硬分析出的执行计划。

5. CBO优化器有2种可选的运行模式：

• FIRST_ROWS(n): oracle在执行sql时，优先考虑将结果集中的前n条记录以最快的速度反馈回来，其他的结果不需要同时返回。这种需求在搜索或分页经常看到。

select /*+ first_rows(10) */ b.x, b.y

from (select /*+ first_rows(10) */ a.*, rownum rnum

from (select /*+ first_rows(10) */ * from t order by x) a where rownum<=20)

b where rnum>=10;

• ALL_ROWS:将sql执行完毕将结果集全部返回。

select /*+ all_rows */ *

from (select /*+ all_rows */ a.*, rownum r

from (select /*+ all_rows */ owner, object_name, created from t where owner=‘sys‘ order by object_name) a where rownum<=20)

where r>=10;

6. 执行计划中的基数(Cardinality): 执行计划每一步操作中，Card的值表示CBO预期从一个行源row source返回的记录数，这个行源可能是一个表，一个索引，也可能是一个子查询。

执行计划中card就是Cardinality的缩写，在10g以后，card被rows替换，表示CBO估算当前操作预期获取的记录数。Cardinality的值对应CBO做出正确的执行计划至关重要。

/*+ dynamic_sampling(t 0) cardinality(t 10000) */ 在未分析数据的前提下，禁止动态采样并告诉CBO从t表将获取到10000条记录。

7. 执行计划可以使用如下方式得到：

• explain plan for 如：1.explain plan for select * from t; 2. select * from table(dbms_xplan.display);
• sqlplus命令 set autotrace on; set autotrace trace exp, stat;
• 第三方提供的工具，如Toad，PL/SQL developer

8. 执行计划中的access表示这个谓词条件的值将会影响数据的访问路径(表还是索引)，filter表示谓词条件的值并不会影响数据的访问路径，只起到过滤的作用。

9. 如果表没有做分析，那么CBO可以通过动态采样的方式获得分析数据，也可以获得准确的执行计划；如果表分析过，但是分析信息过旧，这时候CBO不会使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。

Oracle优化器和执行计划,古老的榕树,5-wow.com