Re: Result cache latch contention

From: Jack van Zanen <jack@xxxxxxxxxxxx>
To: Mohamed Houri <mohamed.houri@xxxxxxxxx>
Date: Tue, 2 Jun 2020 12:29:39 +1000

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@xxxxxxxxx>
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> *@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@xxxxxxxxxxxx> 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>

Re: Result cache latch contention

Other related posts: