标签归档：_optimizer_ads_use_result_cache

12.1中出现大量Result Cache: RC Latch处理

发表于 2016 年 5 月 29 日 由 惜分飞

昨天有个朋友找到我说他们的12.1的库在业务高峰期非常慢，希望我们给予优化支持,经过awr分析，定位到问题为latch free问题,具体定位为:Result Cache: RC Latch.
优化之前awr部分信息
awr整体负载情况，证明当前这个库已经比较忙，业务反馈很慢
awr1

addr信息和top wait信息，确定是latch free问题比较突出
awr2
awr3
latch信息统计和ash信息，找出来突出的latch,定位为Result Cache: RC Latch引起该问题
awr4
awr5
补充大量异常sql
awr6
类似sql语句

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */
 SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") INDEX_FFS( "ACCOUNT" "ACC_USER_ID") */ 
1 AS C1 FROM "ACCOUNT" SAMPLE BLOCK(39.3701, 8) SEED(1) "ACCOUNT" WHERE ( "ACCOUNT".USER_ID IS NOT NULL)) innerQuery

查询mos发现
The cause of this issue is automatic dynamic statistics which is enabled by default in 12c automatically decides whether dynamic statistics are useful and which statistics level to use for all SQL statements. It collects dynamic statistics when the optimizer deems it necessary.
When Automatic Dynamic Sampling is used for the SQL statements, it can decide, based upon these statistics, that a better response time could be achieved by using the result cache for those queries. This can cause heavy usage of the result cache leading to the contention on latch free for “Result Cache: RC Latch”.
也就是说,12c在自动采样有改进，而且默认使用result cache特性，从而引起该问题，即使你设置了 RESULT_CACHE_MODE = MANUAL，依旧会有大量动态采样引起 Result Cache: RC Latch，彻底解决给问题就是通过隐含参数禁止Automatic Dynamic Statistics使用result cache

alter system set "_optimizer_ads_use_result_cache" = FALSE;

设置该参数之后效果
这里看，通过上述处理后，系统db time 大量减少，业务反馈已经运行正常
hawr1

latch free和Result Cache: RC Latch已经基本上消失
hawr2
hawr3
hawr4
当然这个异常是由于动态采样导致,可以通过收集数据库统计信息，设置动态采样级别，也可以从一定程度上缓解该情况.
参考mos
Very Long Parse Time for Queries in InMemory Database (Doc ID 2102106.1)
High “Latch Free” Waits with Contention on ‘Result Cache: RC Latch’ when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1)

发表在 Oracle性能优化 | 标签为 , | 评论关闭