oracle12 保存动态采样,Oracle动态采样引起的性能问题

某张表有统计信息，但突然执行很慢，经检查执行优化器走了动态采样：

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 ;