sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

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

时间:2016-08-29 22:15   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

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

1.优化之前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?

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

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


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

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

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事件引起数据库缓慢