当前位置:Oracle研究中心 > 故障案例 >
时间:2016-08-29 22:15 来源:Oracle研究中心 作者:惜分飞 点击: 次
Oracle12.1的库在业务高峰期非常慢,分析AWR发现latch free导致,具体定位为:Result Cache: RC Latch事件引起数据库缓慢
awr整体负载情况,证明当前这个库已经比较忙,业务反馈很慢
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 大量减少,业务反馈已经运行正常
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【案例】Oracle优化 latch free问题Result Cache:RC Latch引起数据库缓慢
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/939.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。