Oracle 12c 新特性：SQL Plan Directives与过量的动态采样解析

在 12c 中，优化器进行了较大的改变，推出了 Adaptive query optimization，从整体上说，Adaptive query optimization 可以看作如下两部分：

一部分是自适应执行计划，一部分是自适应统计信息。

这里注意一下，
• Adaptive Plans – 是在第一次执行的时候，从default plan变成adaptive plan。
• Automatic Re-optimization -是在第二次执行的时候
• Statistics Feedback 以前叫Cardinality Feedback
• Dynamic Statistics 以前叫Dynamic Sampling
• SQL Plan Directives（SPD） 到目前12.1为止，你可以认为是动态采样的持久化

好，我们今天讨论的主题是最后一项，SQL Plan Directives（SPD）。

我们来一起看看 SPD。在2013年6月oracle官方的白皮书『Oracle Database 12c 中的优化器』中，提到：

SPD是根据从自动重新优化获得的信息自动创建的。SQL 计划指令是优化器用于生成更优执行计划的附加信息。例如，当联接在其联接列中具有数据偏差的两个表时，SQL 计划指令可指导优化器使用动态统计获得更准确的联接基数估算。

所以，当 SQL 第一次运行时，oracle发现统计信息估计的值和实际执行过程中发现值差距较大（misestimate），需要重新优化，就会生成SPD。也就是说，如果我们看到v$sql的is_reoptimizable字段为Y，说明这个语句需要重新优化，在第二次执行的时候，或者类似sql执行的时候，SPD介入。在12.1中，SPD的唯一一个type，就是动态采样（Dynamic sampling）。

Oracle会在misestimate的情况下，让SPD介入。从目前收集到的信息看，如下基数不准，会让oracle认为misestimate。
• single table cardinality misestimate
• join cardinality misestimate
• query block cardinality misestimate
• group by cardinality misestimate
• having cardinality misestimate

我们来看这样一个例子。

1

我创建了一个表，并生成一些数据，收集统计信息

--初始化

conn test/test

drop table big_table;

create table big_table as

select 'iPhone' as product,

       mod(rownum, 5) as channel_id,

       mod(rownum, 1000) as cust_id

  from dual

connect by level <= 2000000

UNION ALL

select 'Motorola' as product,

       mod(rownum, 5) as channel_id,

       mod(rownum, 1000) as cust_id

  from dual

connect by level <= 10

UNION ALL

select 'Nokia' as product,

       mod(rownum, 5) as channel_id,

       mod(rownum, 1000) as cust_id

  from dual

connect by level <= 20401

UNION ALL

select 'Samsung' as product,

       mod(rownum, 5) as channel_id,

       mod(rownum, 1000) as cust_id

  from dual

connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user,'BIG_TABLE',cascade=>true);

exit

2

删除所有已经存在的SPD，并且清空 shared pool；

sqlplus  -S "/ as sysdba"

set pages 0

set line 10000

set echo off

set feedback off

set heading off 

set trimspool on

spool drop_spd.sql

select 'exec dbms_spd.DROP_SQL_PLAN_DIRECTIVE('||''''||DIRECTIVE_ID||''''||');' from dba_sql_plan_directives;

spool off

exit

sqlplus "/as sysdba"

@drop_spd

alter system flush shared_pool;