Result cache latch contention

During certain workloads we run into latch contention on the result cache

[image: image.png]
This was from a 20 minute AWR report.

Now We have had issues with this before and have a startup trigger that blacklists some cache id's so that most of the latch contention wont happen.

But like I said I think we need to add some more cache id's but I am not sure how to capture the problematic id's from the gv$result_cache_objects.

Some queries were handed over by the project team when they handed this over, but it was a bit light on the explanation how to use them. Below are what was handed over.

To analyse performance impact caused by IBMS upgrades/hotfixes, run the following queries to identify any new queries that might require cache blacklisting.

select inst_id, namespace, status, name, cache_id,

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 gv$result_cache_objects

where type = 'Result'

group by inst_id, namespace, name, status, cache_id

having round(sum(block_count)) > 10

order by tot_blk_cnt desc;

select inst_id, substr(name, 1,100) name,

cache_id,

count(*) result_count,

round(avg(scan_count)) avg_scan_count,type,status

from gv$result_cache_objects

where type = 'Result'

group by inst_id, name, cache_id,type,status

having count(*) > 10

order by 4 desc;

select inst_id, namespace, status, name, cache_id,

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 gv$result_cache_objects

where type = 'Result'

group by inst_id, namespace, name, status, cache_id

having round(sum(block_count)) > 10

order by tot_blk_cnt desc;

Can anyone explain the relationship between SQL, cache_id and how to identify the bad cache_id...

I can simply pick the top x id's and blacklist them, but like to do this a bit more scientifically

Jack van Zanen

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png