SQL Plan Directives与过量的动态采样

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

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

这里注意一下,
• 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. 我创建一个表,并生成一些数据,收集统计信息。

2. 删除所有已经存在的SPD,并且清空shared pool:

3. 第一次执行这个sql:

p1
我们看到,即使收集了统计信息,在执行过程中的E-rows和A-rows还是相去甚远。

我们检查v$sql的is_reoptimizable字段,可以看到是Y。

p2

我们来看一下此时SPD的信息,我们用到了2个视图:dba_sql_plan_dir_objects和dba_sql_plan_directives。注意由于SPD是每隔15分钟才刷入数据字典中持久化,我们手工进行flush一次,就不用等15分钟,可以直接看了。

p3
可以看到(点图放大),SPD针对的是对象级,是我的table BIG_TABLE和其字段PRODUCT和CHANNEL_ID。而当时用到的条件是: where product = ‘Motorola’ and channel_id = 1,通过实际执行,oracle认为这个表,和这2个字段的信息不准。

上图中的几个字段稍微解释一下:
a.SPD_TEXT是{EC(TEST.BIG_TABLE)[CHANNEL_ID, PRODUCT]}。Oracle认为你用来这2个字段进行查询,而这2个字段缺少联合统计信息。这里的E和C,以及可能出现其他的字符,解释如下:
E – equality_predicates_only
C – simple_column_predicates_only
J – index_access_by_join_predicates
F – filter_on_joining_object
举例来说,通常这样的条件会认为如下的信息misestimate:

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

from DEMO_TABLE where a+b=c+d;
• {E(DEMO.DEMO_TABLE)[A, B, C, D]}
• missing_stats, permanent as no statistics can help

from DEMO1 join DEMO2 using(KEY) where DEMO2.a=1;
• {(DEMO.DEMO1) – F(DEMO.DEMO2)}

b.INTERNAL_STATE 是NEW,表示是第一次,我们一会可以看看第二次执行的时候,会是如何。
NEW – 1st pass
MISSING_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)

c. TYPE是DYNAMIC_SAMPLING,表示下次执行时,如果此SPD介入,会执行动态采样。

d.REASON表示为什么oracle会认为这个语句需要SPD介入。因为oracle认为SINGLE TABLE CARDINALITY MISESTIMATE。
这个字段的值有:
• single table cardinality misestimate
• join cardinality misestimate
• query block cardinality misestimate
• group by cardinality misestimate
• having cardinality misestimate
这个字段要结合extract之后的notes,也就是上面的SPD_TEXT一起看。

好,我们再来看看,此时有没有动态采样介入。我们看看v$sql中,是否有DS_SVC hint的sql:

p4
我们看到。此时没有动态采样的介入。

4. 我们再来继续测试,同样的SQL语句,第二次执行的情况:
p5
我们看到此时的E-rows已经和A-rows一样,也就是说,此时再次执行,cursor还在缓存的时候,直接使用statistics feedback,生成child number为1的cursor。注意,此时动态采样也还是没介入的。
p7

5. 如果我们flush shared pool,我们来看看是什么情况

p8
我们看到,当同一个SQL,发生硬解析的时候,SPD介入,执行动态采样。

我们再来看看是否在v$sql中有了动态采样的信息:
p9
可以看到已经有很多关于DS_SVC的动态采样的语句了。

注意,此处的动态采样,不是ADS(Automatic Dynamic Statistics)引起的,是SPD引起的。所以说,12c的动态采样比11g要多的多,很大程度上,是SPD引起的。

11g的表如果收集的统计信息,就不再会动态采样。而12c中,即使表收集了统计信息,还是会被SPD触发,进行动态采样。

我们再来看看SPD中的信息:
p10
注意这里的Internal状态从NEW已经变成了MISSING_STATS。

到这里,你可能已经意识到了SPD会造成比11g多的动态采样,但是,是否只是影响同一个sql?

之前说过,SPD是针对对象级的,不是SQL级的,所以,当我有一个类似的sql触发时,第一次硬解析的时候,SPD也会介入

6. 我再运行一个“类似”SQL:

p11
可以看到,也触发了SPD,SPD指导优化器再次进行动态采样。

而由于在第一次跑的时候,进行了动态采样,且E-rows和A-rows之间差距小,Oracle认为它不需要is_reoptimizable:
p12

7.再运行另一个“类似”sql

也是同样道理,SPD介入,执行动态采样,但是is_reoptimizable是N.
p13

p14

但是,此时的DS_SVC,由于这些“类似”SQL的动态采样,在v$sql中渐渐变多了。
p15

注:“类似”,是指语句中也有where product = ‘Motorola’ and channel_id = 1的语句。只要是这样的语句,SPD都会介入。

我只是跑了4个,就已经出现了15个动态采样的递归sql,在生产环境中,会更严重一些,如v$sql中总共9万多个sql,其中7万多个是这样的带DS_SVC的sql。

而由于12c中每次动态采样都需要被result cache,此时就会出现Result cache的latch争用。见Document 2002089.1 High Latch Free Waits on ‘Result Cache: RC Latch’ In 12C when RESULT_CACHE_MODE = MANUAL

从上面的测试可以看出,SPD的介入应该是比较靠前的,当同一个语句再次执行的时候,如果已经缓存,就采用Statistics Feedback,如果没缓存,在hard parse之初就介入了SPD,如果SPD的信息还是missing,要求动态采样,则在后面的执行的时候,都走了动态采样;如果SPD中missing的信息已经被收集,则SPD就从USABLE更新成SUPERSEDED,此时就不走动态采样;

另外,当“类似”语句进入的时候,也是同样道理。

用流程图表示,基本就是下面这个图的绿框部分:(整个图是Adaptive query optimization)
p16

那么,什么样的情况,才会让SPD认为信息已经收集齐了,STATE列从USABLE变成SUPERSEDED,INTERNAL_STATE列从MISSING_STATS变成HAS_STATS。从我的测试看,条件非常严格。不仅仅要收集所有列的直方图,还要收集(CHANNEL_ID, PRODUCT)的extended stats.收集完之后,再次硬解析的时候,才会不走动态采样。

p17

检查SPD的状态,可以看到变成如下了:
p18

此时,动态采样不再进行。(由于收集了非常精确的统计信息,E-rows完全等于A-rows)
p19

那么既然SPD这么容易造成动态采样,且动态采样容易有Result cache的latch(可以通过修改_optimizer_ads_use_result_cache=false来让动态采样不进result cache),且即使关闭了result cache的动态采样,还是容易在v$sql中积累大量DS_SVC的hint的递归sql,消耗shared pool,我们如何来解决由SPD引起的动态采样呢?

解决方案:

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







参考:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据