Re: Result cache latch contention

From: sam roberts <sam112233_at_gmail.com>
Date: Fri, 29 May 2020 09:50:43 +0400
Message-ID: <CALts1HLvcTW6qSR2Ff1+2Q14LfcbR=pmG-AKUB1=b9wJpf92Kg_at_mail.gmail.com>





unscusbribe -l

please unsubscribe me

On Thu, May 28, 2020 at 9:54 AM Jack van Zanen <jack_at_vanzanen.com> 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
>



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


Received on Fri May 29 2020 - 07:50:43 CEST

Original text of this message