Re: Result cache latch contention

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 2 Jun 2020 12:29:39 +1000
Message-ID: <CAFeFPA9BKUVVoAx4j+g=FJNFhsBHr8VMaU7NZhS0GNwFvnZaPg_at_mail.gmail.com>

Thanks , this is interesting and I will dig a bit deeper

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 5:20 PM Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

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

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





image/png attachment: image.png

Received on Tue Jun 02 2020 - 04:29:39 CEST