latch free等待问题之Result Cache:RC Latch优化案例
问题背景：
客户反馈数据库运行缓慢，DB （12c）
问题解决
1> 查看相关时间段的AWR报告
查看DB的整体负载情况看出数据库已经比较繁忙
AWR报告中存在大量异常sql（不属于业务模块）
例如
1 select chainviewi0_.id as id1_112_, 2 chainviewi0_.actId as actId2_112_, 3 chainviewi0_.activityName as activityName3_112_, 4 chainviewi0_.approved as approved4_112_, 5 chainviewi0_.approvedDate as approvedDate5_112_, 6 chainviewi0_.barcode as barcode6_112_, 7 chainviewi0_.chainSku_id as chainSku_id7_112_, 8 chainviewi0_.channelCrowdCode as channelCrowdCode8_112_, 9 chainviewi0_.conditionType as conditionType9_112_, 10 chainviewi0_.coverBefore as coverBefore10_112_, 11 chainviewi0_.dailyLimit as dailyLimit11_112_, 12 chainviewi0_.description as description12_112_, 13 chainviewi0_.discountType as discountType13_112_, 14 chainviewi0_.effective as effective14_112_, 15 chainviewi0_.endDate as endDate15_112_, 16 chainviewi0_.endTime as endTime16_112_, 17 chainviewi0_.itemDiscount_id as itemDiscount_id17_112_, 18 chainviewi0_.management_id as management_id18_112_, 19 chainviewi0_.matnr as matnr19_112_, 20 chainviewi0_.memberLimit as memberLimit20_112_, 21 chainviewi0_.merchantCrowdCode as merchantCrowdCode21_112_, 22 chainvi ewi0_.oldPrice as oldPrice22_112_, 23 chainviewi0_.onditionNum as onditionNum23_112_, 24 chainviewi0_.outActId as outActId24_112_, 25 chainviewi0_.poi as poi25_112_, 26 chainviewi0_.price as price26_112_, 27 chainviewi0_.priorityValue as priorityValue27_112_, 28 chainviewi0_.skuCode as skuCode28_112_, 29 chainviewi0_.skuId as skuId29_112_, 30 chainviewi0_.source as source30_112_, 31 chainviewi0_.startDate as startDate31_112_, 32 chainviewi0_.startTime as startTime32_112_, 33 chainviewi0_.storeCode as storeCode33_112_, 34 chainviewi0_.totalLimit as totalLimit34_112_, 35 chainviewi0_.upc as upc35_112_, 36 chainviewi0_.userDailyLimit as userDailyLimit36_112_, 37 chainviewi0_.userLimit as userLimit37_112_, 38 chainviewi0_.zkfd as zkfd38_112_, 39 chainviewi0_.zkl as zkl39_112_ 40 from dbusrchain.view_chain_ItemDiscountItem chainviewi0_ 41 where chainviewi0_.poi = :1 42 and chainviewi0_.chainSku_id = :2 43 and chainviewi0_.approved = :3 44 and chainviewi0_.effective = :4 45 order by chainviewi0_.source desc nulls last, 46 chainviewi0_.approvedDate desc nulls last, 47 chainviewi0_.id desc 48 49 50 根据ADDM，latch ID 559 51 52 SQL> select * from v$latchname where latch#=559; 53 LATCH# NAME DISPLAY_NAME D 54 55 ---------- ---------------------------------------------------------------- ----------------------------------------------------------------- 56 57 559 Result Cache: RC Latch Result Cache: RC Latch
latch free的真正原因是RC Latch
查询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
可以通过调整隐含参数规避
1 alter system set "_optimizer_ads_use_result_cache" = FALSE；
设置该优化参数之后效果
业务反馈已经运行正常
