Re: Result cache latch contention
Date: Fri, 29 May 2020 09:20:33 +0200
Message-ID: <CAJu8R6gj8n=8szagas7ww0pRx+0_TQrF+qQZ_6bAOjtkJeAhJA_at_mail.gmail.com>
Hello,
You can use Tanel Poder *latchprof.sql *script as I did to investigate a wrong use of result_cache hint in the following real life situation
https://hourim.wordpress.com/2018/08/17/wrong-utilisation-of-result-cache/
As you will notice, by reading the above blog post, I did the following:
SQL> *_at_latchprof* sid,name,sqlid % "Result" 100000
- LatchProf 2.02 by Tanel Poder ( http://www.tanelpoder.com )
SID NAME SQLID Held Gets Held % ----- ------------------------ ------------- ----- ---------- ------- 1753 Result Cache: RC Latch 3djqkyz0taafr 248 62 .25 298 Result Cache: RC Latch 3djqkyz0taafr 151 45 .15 35 Result Cache: RC Latch 3djqkyz0taafr 148 32 .15 3671 Result Cache: RC Latch 3djqkyz0taafr 136 35 .14 2681 Result Cache: RC Latch 3djqkyz0taafr 130 34 .13 273 Result Cache: RC Latch 3djqkyz0taafr 94 18 .09 1238 Result Cache: RC Latch 6hbsjju24n8d1 56 56 .06
This script has, clearly, pointed out the main SQL_ID (3djqkyz0taafr) at the origin of the Result Cache Latch
You can also try such a kind of following query to find the root cause of the result cache latch:
SQL> select
to_char(dbms_sqltune.sqltext_to_signature(name, 1))
, count(1)
from
gv$result_cache_objects
group by
to_char(dbms_sqltune.sqltext_to_signature(name, 1))
having count(1) > 3
order by 2 desc;
TO_CHAR(DBMS_SQLTUNE.SQLTEXT_TO_SIGNATUR COUNT(1)
- ----------
11688800737312996943 106782 778588378770889215 879 9760132213098432565 62 13511637134398334555 7 10994613278769629249 7 13666841246362081009 6 2234831540847838164 5 16412641633620715561 4
And the following query will help you finding the proportion of invalidated result cache id (use your force matching signature)
SQL> select
status
, count(1)
from
gv$result_cache_objects
where
to_char(dbms_sqltune.sqltext_to_signature(name, 1)) = '11688800737312996943'
group by status;
STATUS COUNT(1)
- ----------
Invalid 77583
Published 30185
New 1
More details and reproducible example can be found in the above mentioned article.
Best regards
Mohamed
Le jeu. 28 mai 2020 à 07:53, Jack van Zanen <jack_at_vanzanen.com> a écrit :
> 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
>
-- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri>Received on Fri May 29 2020 - 09:20:33 CEST
-- http://www.freelists.org/webpage/oracle-l
- image/png attachment: image.png
