执行计划

执行计划：SQL语句访问和处理数据的方式。

数据的访问：

  • 直接表的访问

    • 并行
    • 多数据块

  • 通过索引访问

    • index unique scan
    • index range scan
    • index full scan
    • index fast full scan
    • index skip scan

数据的处理：排序、分组、求和、汇总等操作

数据的关联处理：nested loop join、merge join、hash join等

产生执行计划：

  1. set autotrace trace exp;
  2. select * from t;

执行计划查看：缩进越靠右的先执行，同样缩进的从上往下执行。

数据访问

通过索引访问：

  • index unique scan
    1. select * from t where id=10;  -- 通常是等号条件
  • index range scan
    1. select * from t where id < 10;  -- 查询条件是索引的一个范围
  • index fast full scan
    1. select count(*) from t;  -- 通过索引可以直接得到结果。count(*)不关注索引的键值，只关注个数，所以可以通过index fast full scan一次读多个索引的数据块
  • index full scan
    1. select id from t order by id;  -- 只查了索引字段，排序字段也是索引字段
  • index skip scan
    1. select * from t where object_id=100;

数据处理

数据集的关联：

  • hash join：把小表通过hash算法加载到内存中，另一张表和它做关联
    1. select t1.* 
    2. from t1, t2
    3. where t1.object_id=t2.object_id;
  • nested loops：从其中一张表中遍历拿数据到另一张表遍历
    1. select t1.* 
    2. from t1, t2
    3. where t1.object_id=t2.object_id;
  • merge join（sorted merge join）：排好序的数据使用merge join比较好
    1. select t1.*
    2. from (select * from t1 order by object_id) t1,
    3. (select * from t2 order by object_id) t2
    4. where t1.object_id=t2.object_id;

表的处理：

  • 并行：表没有索引，对表所有数据做处理时使用并行比较好。
    1. select count(*) from t1;

优化器

RBO（8i之前）：Rule based optimizer，一套死板的规则来控制数据的访问。

CBO（8i之后）：Cost based optimizer，依据一套数据模型，计算数据访问和处理的成本，择最优成本为执行方案。

CBO还在不断发展，即使参数全部相同，oracle不同版本中产生的执行计划也可能不同。

工作模式

CBO的工作模式：

  • all_rows：以结果集全部处理完毕为目的
    1. select id,count(*) from t
    2. group by id
    3. order by id;
  • first_rows（n）：以最快返回前n行为目的
    1. select object_name
    2. from
    3. (
    4.   select rownum rn, object_name
    5.   from
    6.   (
    7.       select object_name from t order by object_name
    8.   )
    9.   where rownum <= 20
    10. )
    11. where rn >= 11;

模式的设置方式：

  1. 通过参数设置
    1. show parameter optimizer_mode;
  1. 设置会话
    1. alter session set optimizer_mode=all_rows;
  1. sql通过hint设置
    1. select /*+ all_rows */ count(*) from t;

COST代价

在执行计划中，会有一列COST表示这一步骤的代价，oracle会按自己的数学模型计算出一个数值。

选择性（selectivity）：oracle决定选用哪种访问路径的一个关键

当一个表使用dbms_stats包分析后，可以在user_tab_col_statistics查看相关的统计信息：

  1. select column_name,num_distinct 
  2. from user_tab_col_statistics 
  3. where table_name='T'
  4. order by 2 desc;

column_name为表的每个字段，num_distinct为这个字段在表中不重复的值个数。

字段的选择性 = 1 / num_distinct，计算出的结果越小，表示选择性越高。有些重复性高的字段（例如性别的值可能为1/2）计算结果会很大，表示选择性很低。

选择性高的字段适合做B-tree索引，选择性低的不适合。

索引的选择性：通过user_indexes进行查询

  1. select index_name,distinct_keys
  2. from user_indexes
  3. where table_name='T';

集的势（cardinality，plsqldev中叫基数）：在执行计划中表示每一步操作返回的记录数（Oracle通过表的统计信息推测出来的值，并不一定等于实际返回的记录数）。CBO通过对这个值的权重计算，决定使用哪一种方式访问数据。

在Oracle 10gR2以前，执行计划中通过Card表示。在Oracle 10gR2以后，执行计划中通过Rows表示。

如果没有对字段做直方图分析，oracle会通过selectivity推测出cardinality，例如：

实际中，会影响cardinality的因素有很多

  1. -- 查看x的不重复值，即selectivity。假如为6个值
  2. select distinct x from t;
  4. -- 查看表中数据总条数，假设43101条
  5. select count(*) from t;
  7. -- oracle只知道不重复值数量，不做直方图分析
  8. exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
  10. -- 查询表中x=1的值。此时oracle生成的执行计划中，cardinality为7184
  11. select * from t where x = 1;
  13. -- 因为selectivity中x不重复值有6个
  14. -- 表中数据总条数为43101条
  15. -- 所以oracle估算 x=1 的查询结果cardinality条数可能为 43101/6 = 7184 条
  16. select round(43101/6) from dual;

clustering factor：会影响CBO对索引的代价评估

  1. select index_name, clustering_factor from user_indexes where table_name='T';

CBO的核心：成本的计算

  • 数据访问的成本

    • I/O成本（全表扫描、索引）
    • CPU成本

  • 数据处理的成本

    • CPU成本

  • 其他因素的影响

    • 直方图
    • 多数据块读
    • 并行
    • 相关参数设置

总结

优化器是Oracle一不断完善的数学模型。

每个版本的机制有较大的差异。

I/O成本对执行计划最为关键。

无法对SQL进行智能优化。