某张表有统计信息,但突然执行很慢,经检查执行优化器走了动态采样:
explain plan for select 1 from vt_dnb t where zcbh = '123456'
select * from table(dbms_xplan.display());
15 Note
16 -----
17 - dynamic statistics used: dynamic sampling (level=2)
18 - 1 Sql Plan Directive used for this statement
经查资料,发现有前人碰到过:
https://blog.csdn.net/orion61/article/details/7363928
我们知道动态采样一般在没有统计信息的时候生效,但我们表都有最新的统计信息。为什么会这样呢?BUG 就算是level8的采样,也不过千百个block,肯定不准确。
经过查询metalink,并且和ORACLE support沟通以后,确认bug
Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8
解决方案 关闭动态采样
在 12.1 版本中修复 , GOD!
https://blog.csdn.net/eygle/article/details/80841483
那么,什么样的情况,才会让SPD认为信息已经收集齐了,
STATE列从USABLE变成SUPERSEDED,INTERNAL_STATE列从MISSING_STATS变成HAS_STATS。
从我的测试看,条件非常严格。不仅仅要收集所有列的直方图,
还要收集(CHANNEL_ID, PRODUCT)的extended stats.收集完之后,再次硬解析的时候,才会不走动态采样。
。。。。。。。
>>>解决方案<<<
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
我的做法:
1、SQL加Hint:/*+ dynamic_sampling(t 0) */
2、直接关闭
show parameter dyna;
alter system set optimizer_dynamic_sampling = 2 ;