Result cache latch contention

From: Jack van Zanen <jack@xxxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 28 May 2020 15:52:35 +1000

Hi

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

-------------------------
This e-mail and any attachments may contain confidential material for the
sole use of the intended recipient. If you are not the intended recipient,
please be aware that any disclosure, copying, distribution or use of this
e-mail or any attachment is prohibited. If you have received this e-mail in
error, please contact the sender and delete all copies.
Thank you for your cooperation

Follow-Ups:
- Re: Result cache latch contention
  - From: Mladen Gogala
- Re: Result cache latch contention
  - From: sam roberts
- Re: Result cache latch contention
  - From: Mohamed Houri

Result cache latch contention

Other related posts: