Re: Result cache latch contention

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 16 Jun 2020 10:27:55 +1000
Message-ID: <CAFeFPA9ue1OvshEr3v1G3iw_Tyjom2_B7_m1=02imTeXHf8MoA_at_mail.gmail.com>

Hi All,

We monitored the RC usage for a while and identified several cache-id's that were having very high numbers of results (invalids) but very low scan counts.
We blacklisted 4 of the worst offenders and contention has all but disappeared. Lets see how this goes for the next week.

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

On Fri, May 29, 2020 at 11:35 AM Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> 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: 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




image/png attachment: image.png

Received on Tue Jun 16 2020 - 02:27:55 CEST