Latch Free Wait contention issue

Latch Free Wait contention issue

To troubleshoot this issue, following the following steps:

1. run AWR reports on each node RAC
2. run AWR diff report
3.  Patch 26436717 HIGH RESULT CACHE LATCHES AFTER MIGRATION TO 12.1.0.2

Apply the patch or use workaround:  
alter system set "_optimizer_dsdir_usage_control" = 0;

This parameter can be set dynamically


spool query_result.html
set serveroutput on
exec dbms_result_cache.memory_report;
set lines 500
set long 9999
set pages 999
set serveroutput on size 1000000
set feedback off
SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title><STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
set echo off
select namespace, status, name,
count(*) number_of_results,
round(avg(scan_count)) avg_scan_cnt,
round(max(scan_count)) max_scan_cnt,
round(sum(block_count)) tot_blk_cnt
from v$result_cache_objects
where type = 'Result'
group by namespace, name, status
order by namespace, tot_blk_cnt;
select name, value from v$result_cache_statistics;
spool off
SET MARKUP HTML OFF
set echo on

No comments:

Post a Comment