Oracle 12c 新特性：sql Plan Directives与过量的动态采样解析
在 12c 中，优化器进行了较大的改变，推出了 Adaptive query optimization，从整体上说，Adaptive query optimization 可以看作如下两部分：@H_502_16@
@H_502_16@
一部分是自适应执行计划，一部分是自适应统计信息。@H_502_16@
这里注意一下，
• Adaptive Plans – 是在第一次执行的时候，从default plan变成adaptive plan。• Automatic Re-optimization -是在第二次执行的时候• Statistics Feedback 以前叫Cardinality Feedback• Dynamic Statistics 以前叫Dynamic Sampling• sql Plan Directives（SPD） 到目前12.1为止，你可以认为是动态采样的持久化@H_502_16@
好，我们今天讨论的主题是最后一项，sql Plan Directives（SPD）。@H_502_16@
我们来一起看看 SPD。在2013年6月oracle官方的白皮书『Oracle Database 12c 中的优化器』中，提到：@H_502_16@
SPD是根据从自动重新优化获得的信息自动创建的。sql 计划指令是优化器用于生成更优执行计划的附加信息。例如，当联接在其联接列中具有数据偏差的两个表时，sql 计划指令可指导优化器使用动态统计获得更准确的联接基数估算。@H_502_16@
@H_502_16@
所以，当 sql 第一次运行时，oracle发现统计信息估计的值和实际执行过程中发现值差距较大（misestimate），需要重新优化，就会生成SPD。也就是说，如果我们看到v$sql的is_reoptimizable字段为Y，说明这个语句需要重新优化，在第二次执行的时候，或者类似sql执行的时候，SPD介入。在12.1中，SPD的唯一一个type，就是动态采样（Dynamic sampling）。@H_502_16@
Oracle会在misestimate的情况下，让SPD介入。从目前收集到的信息看，如下基数不准，会让oracle认为misestimate。
• single table cardinality misestimate• join cardinality misestimate• query block cardinality misestimate• group by cardinality misestimate• having cardinality misestimate@H_502_16@
我们来看这样一个例子。@H_502_16@
@H_502_16@
1@H_502_16@
我创建了一个表，并生成一些数据，收集统计信息@H_502_16@
--初始化@H_502_16@
conn test/test@H_502_16@
drop table big_table;@H_502_16@
create table big_table as@H_502_16@
select 'iPhone' as product,@H_502_16@
mod(rownum,5) as channel_id,1000) as cust_id@H_502_16@
from dual@H_502_16@
connect by level <= 2000000@H_502_16@
UNION ALL@H_502_16@
select 'Motorola' as product,0);">connect by level <= 10@H_502_16@
select 'Nokia' as product,0);">connect by level <= 20401@H_502_16@
select 'Samsung' as product,0);">connect by level <= 1000000;@H_502_16@
exec dbms_stats.gather_table_stats(user,'BIG_TABLE',cascade=>true);@H_502_16@
exit@H_502_16@
@H_502_16@
2@H_502_16@
删除所有已经存在的SPD，并且清空 shared pool；@H_502_16@
@H_502_16@
sqlplus -S "/ as sysdba"@H_502_16@
set pages 0@H_502_16@
set line 10000@H_502_16@
set echo off@H_502_16@
set heading off@H_502_16@
set trimspool on@H_502_16@
select 'exec dbms_spd.DROP_sql_PLAN_DIRECTIVE('||''''||DIRECTIVE_ID||''''||');' from dba_sql_plan_directives;@H_502_16@
spool off@H_502_16@
exit@H_502_16@
sqlplus "/as sysdba"@H_502_16@
@drop_spd@H_502_16@
alter system flush shared_pool;@H_502_16@
@H_502_16@
3@H_502_16@
--TestTime 1:@H_502_16@
set line 1000@H_502_16@
set pages 1000@H_502_16@
set termout off@H_502_16@
select /*+gather_plan_statistics*/@H_502_16@
cust_id,channel_id,product@H_502_16@
from big_table@H_502_16@
where product = 'Motorola'@H_502_16@
and channel_id = 1@H_502_16@
order by product@H_502_16@
/@H_502_16@
select* from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));@H_502_16@
@H_502_16@
我们看到，即使收集了统计信息，在执行过程中的 E-rows 和 A-rows 还是相去甚远。@H_502_16@
我们检查 v$sql 的 is_reoptimizable 字段，可以看到是Y。@H_502_16@
col DIRECTIVE_ID for 999999999999999999999@H_502_16@
col OWNER for a10@H_502_16@
col object_name for a10@H_502_16@
col SUBOBJECT_NAME for a10@H_502_16@
col sql_text for a90@H_502_16@
col spd_text for a45@H_502_16@
col internal_state for a30@H_502_16@
select sql_id,child_number,sql_text,is_reoptimizable from v$sql where sql_text like '%+gather_plan_statistics%big_table%'@H_502_16@
/@H_502_16@
我们来看一下此时SPD的信息，我们用到了2个视图：dba_sql_plan_dir_objects和dba_sql_plan_directives。注意由于SPD是每隔15分钟才刷入数据字典中持久化，我们手工进行flush一次，就不用等15分钟，可以直接看了。@H_502_16@
conn / as sysdba@H_502_16@
exec dbms_spd.FLUSH_sql_PLAN_DIRECTIVE;@H_502_16@
select aa.directive_id,aa.owner,aa.object_name,aa.subobject_name,aa.object_type,0);">state,0);">extract(bb.notes,'/spd_note/internal_state/text()' ) internal_state,'/spd_note/spd_text/text()' ) as spd_text,0);">bb.type,bb.reason from dba_sql_plan_dir_objects aa,dba_sql_plan_directives bb@H_502_16@
where aa.directive_id=bb.directive_id and aa.object_name in ('BIG_TABLE')@H_502_16@
order by 10,1@H_502_16@
/@H_502_16@
@H_502_16@
可以看到（点图放大），SPD针对的是对象级，是我的 table BIG_TABLE 和其字段PRODUCT和CHANNEL_ID。而当时用到的条件是： where product = ‘Motorola’ and channel_id = 1，通过实际执行，oracle认为这个表，和这2个字段的信息不准。@H_502_16@
上图中的几个字段稍微解释一下：@H_502_16@
a.SPD_TEXT是{EC(TEST.BIG_TABLE)[CHANNEL_ID,PRODUCT]}。Oracle认为你用来这2个字段进行查询，而这2个字段缺少联合统计信息。这里的E和C，以及可能出现其他的字符，解释如下：E – equality_predicates_only@H_502_16@
C – simple_column_predicates_onlyJ – index_access_by_join_predicatesF – filter_on_joining_object@H_502_16@
举例来说，通常这样的条件会认为如下的信息misestimate：@H_502_16@
from DEMO_TABLE where a=1 and b=1 and c=1 and d=1;• {EC(DEMO.DEMO_TABLE)[A,B,C,D]}• missing_stats,has_stats with extended statistics@H_502_16@
from DEMO_TABLE where a+b=c+d;• {E(DEMO.DEMO_TABLE)[A,permanent as no statistics can help@H_502_16@
from DEMO1 join DEMO2 using(KEY) where DEMO2.a=1;• {(DEMO.DEMO1) – F(DEMO.DEMO2)}@H_502_16@
@H_502_16@
b.INTERNAL_STATE是NEW，表示是第一次，我们一会可以看看第二次执行的时候，会是如何。NEW – 1st passMISSING_STATS – needs extended stats(gathered automagically)HAS_STATS – extended stats have now been gathered(Intermediate State – new statements may still need SPD’s)PERMANENT – extended stats have now been gathered(but SPD still needed because of != predicates)@H_502_16@
c.TYPE是DYNAMIC_SAMPLING，表示下次执行时，如果此SPD介入，会执行动态采样。@H_502_16@
d.REASON表示为什么oracle会认为这个语句需要SPD介入。因为oracle认为SINGLE TABLE CARDINALITY MISESTIMATE。这个字段的值有：• having cardinality misestimate@H_502_16@
这个字段要结合extract之后的notes，也就是上面的SPD_TEXT一起看。@H_502_16@
好，我们再来看看，此时有没有动态采样介入。我们看看v$sql中，是否有DS_SVC hint的sql：@H_502_16@
select sql_text from v$sql where sql_text like '%DS_SVC%'@H_502_16@
我们看到，此时没有动态采样的介入。@H_502_16@
@H_502_16@
4@H_502_16@
我们再来继续测试，同样的 sql 语句，第二次执行的情况：@H_502_16@
我们看到此时的 E-rows 已经和 A-rows 一样，也就是说，此时再次执行，cursor 还在缓存的时候，直接使用statistics Feedback，生成 child number 为1的 cursor。注意，此时动态采样也还是没介入的。@H_502_16@
@H_502_16@
5@H_502_16@
如果我们 flush shared pool，我们来看看是什么情况：@H_502_16@
alter system flush shared_pool;@H_502_16@
我们看到，当同一个sql，发生硬解析的时候，SPD介入，执行动态采样。@H_502_16@
我们再来看看是否在v$sql中有了动态采样的信息：可以看到已经有很多关于 DS_SVC 的动态采样的语句了。
注意，此处的动态采样，不是ADS（Automatic Dynamic Statistics）引起的，是SPD引起的。所以说，12c的动态采样比11g要多的多，很大程度上，是SPD引起的。@H_502_16@
11g的表如果收集的统计信息，就不再会动态采样。而 12c 中，即使表收集了统计信息，还是会被 SPD 触发，进行动态采样。@H_502_16@
我们再来看看SPD中的信息：注意这里的Internal状态从NEW已经变成了MISSING_STATS。@H_502_16@
到这里，你可能已经意识到了SPD会造成比11g多的动态采样，但是，是否只是影响同一个sql？@H_502_16@
之前说过，SPD是针对对象级的，不是sql级的，所以，当我有一个类似的sql触发时，第一次硬解析的时候，SPD也会介入。@H_502_16@
6@H_502_16@
--TestTime 2:@H_502_16@
where product = 'Nokia'@H_502_16@
order by product;@H_502_16@
可以看到，也触发了SPD，SPD指导优化器再次进行动态采样。@H_502_16@
而由于在第一次跑的时候，进行了动态采样，且E-rows和A-rows之间差距小，Oracle认为它不需要is_reoptimizable：@H_502_16@
7@H_502_16@
--TestTime 3:@H_502_16@
where product = 'iPhone'@H_502_16@
and channel_id = 4@H_502_16@
也是同样道理，SPD介入，执行动态采样，但是is_reoptimizable是N.但是，此时的DS_SVC，由于这些“类似”sql的动态采样，在v$sql中渐渐变多了。
@H_502_16@
注：“类似”，是指语句中也有where product = ‘Motorola’ and channel_id = 1的语句。只要是这样的语句，SPD都会介入。@H_502_16@
我只是跑了4个，就已经出现了15个动态采样的递归sql，在生产环境中，会更严重一些，如v$sql中总共9万多个sql，其中7万多个是这样的带DS_SVC的sql。@H_502_16@
而由于12c中每次动态采样都需要被result cache，此时就会出现Result cache的latch争用。见Document 2002089.1High Latch Free Waits on ‘Result Cache: RC Latch’ In 12C when RESULT_CACHE_MODE = MANUAL@H_502_16@
从上面的测试可以看出，SPD的介入应该是比较靠前的，当同一个语句再次执行的时候，如果已经缓存，就采用Statistics Feedback，如果没缓存，在hard parse之初就介入了SPD，如果SPD的信息还是missing，要求动态采样，则在后面的执行的时候，都走了动态采样；如果SPD中missing的信息已经被收集，则SPD就从USABLE更新成SUPERSEDED，此时就不走动态采样；@H_502_16@
另外，当“类似”语句进入的时候，也是同样道理。@H_502_16@
用流程图表示，基本就是下面这个图的绿框部分：（整个图是Adaptive query optimization）那么，什么样的情况，才会让SPD认为信息已经收集齐了，STATE列从USABLE变成SUPERSEDED，INTERNAL_STATE列从MISSING_STATS变成HAS_STATS。从我的测试看，条件非常严格。不仅仅要收集所有列的直方图，还要收集(CHANNEL_ID,PRODUCT)的extended stats.收集完之后，再次硬解析的时候，才会不走动态采样。@H_502_16@
select@H_502_16@
dbms_stats.create_extended_stats@H_502_16@
('TEST','(CHANNEL_ID,PRODUCT)')@H_502_16@
from dual;@H_502_16@
EXEC DBMS_STATS.gather_table_stats('TEST',method_opt => 'for all columns size 254');@H_502_16@
select TABLE_NAME,COLUMN_NAME,HISTOGRAM from dba_tab_col_statistics where table_name='BIG_TABLE';@H_502_16@
order by product;@H_502_16@
检查SPD的状态，可以看到变成如下了：@H_502_16@
此时，动态采样不再进行。(由于收集了非常精确的统计信息，E-rows完全等于A-rows)那么既然SPD这么容易造成动态采样，且动态采样容易有Result cache的latch（可以通过修改_optimizer_ads_use_result_cache=false来让动态采样不进result cache），且即使关闭了result cache的动态采样，还是容易在v$sql中积累大量DS_SVC的hint的递归sql，消耗shared pool，我们如何来解决由SPD引起的动态采样呢？@H_502_16@
@H_502_16@
1. 禁用Adaptive query optimization。OPTIMIZER_ADAPTIVE_FEATURES = FALSE，这是最大的总开关。2. 禁用SPD产生新的directive：_sql_plan_directive_mgmt_control = 0（注意还要将原来已经存在的directive改成disable或者drop）3. 禁用SPD的动态采样：_optimizer_dsdir_usage_control = 0@H_502_16@
@H_502_16@
>>>参考文献<<<@H_502_16@
Doc ID 2002089.1 High Latch Free Waits on 'Result Cache: RC Latch' In 12C@H_502_16@
Doc ID 2031605.1 Adaptive Query Optimization@H_502_16@
Doc ID 2002108.1 Dynamic Sampling Level Is Changed Automatically in 12C@H_502_16@
Doc ID 2033658.1 Dictionary Queries Running Slow in 12C PDBs@H_502_16@
Doc ID 2097793.1 [INTERNAL]Commonly Reported Known Issues for Database@H_502_16@
@H_502_16@
作者：何剑敏@H_502_16@
投稿：有投稿、寻求报道意向技术人请联络 wenmin.yin@enmotech.com@H_502_16@
更多精彩请关注 “数据和云” 公众号@H_502_16@
