【案例】Oracle优化 latch free问题Result Cache:RC Latch引起数据库缓慢

天萃荷净

Oracle12.1的库在业务高峰期非常慢，分析AWR发现latch free导致，具体定位为:Result Cache: RC Latch事件引起数据库缓慢

1.优化之前awr部分信息

awr整体负载情况，证明当前这个库已经比较忙，业务反馈很慢

---

addr信息和top wait信息，确定是latch free问题比较突出

---

latch信息统计和ash信息，找出来突出的latch,定位为Result Cache: RC Latch引起该问题

---

补充大量异常sql

---

类似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?

2.设置该优化参数之后效果

这里看，通过上述处理后，系统db time 大量减少，业务反馈已经运行正常

---

latch free和Result Cache: RC Latch已经基本上消失

---

当然这个异常是由于动态采样导致,可以通过收集数据库统计信息，设置动态采样级别，也可以从一定程度上缓解该情况.

3.参考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-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle优化 latch free问题Result Cache:RC Latch引起数据库缓慢

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/939.html

Oracle研究中心

关键词：

Oracle优化案例

latch free优化问题的解决办法

Result Cache:RC Latch事件引起数据库缓慢