Re: Result cache latch contention

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 28 May 2020 21:33:02 -0400
Message-ID: <50eccd84-6d31-a81c-65d9-f1f8f5db304e_at_gmail.com>



I have had that problem once. My solution was to find plans with operation='RESULT CACHE'. The OBJECT_NAME in V$SQL_PLAN is the cache id. I am aware that this is not exactly an elegant solution, but it did the trick for me.

Regards

On 5/28/20 1:52 AM, Jack van Zanen wrote:
> Hi
>
> During certain workloads we run into latch contention on the result cache
>
> 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

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 29 2020 - 03:33:02 CEST

Original text of this message