Oracle Scratchpad

February 10, 2023

Result Cache

Filed under: Infrastructure,latches,Oracle — Jonathan Lewis @ 12:02 pm GMT Feb 10,2023

A historic complaint about the result cache was that it did not scale. Although this complaint was often the consequence of the mechanism was being used inappropriately, there was an underlying issue that imposed a limit on how scalable (in terms of concurrency) the cache could be: it was covered by a single child latch.

A question came up recently about whether anything had changed in recent versions of Oracle, so I dusted down a script I had written in the days of 11gR1 and brought it up to date for container databases. All it does is report (from the CDB Root) all the latches, latch parents and latch children for any latch with a name like ‘Result Cache%’:

rem     Script:         result_cache_latches.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2023 (Feb 2008)
rem
rem     Last tested 
rem             21.3.0.0
rem             19.11.0.0
rem
rem     Notes:
rem     Has to be run on the CDB root, by user with select
rem     privileges on the v$ latch views.
rem

set linesize 144
set pagesize 60
set trimspool on
set tab off

spool result_cache_latches.lst

column name format a32

clear breaks
break on name skip n1 on con_id

prompt  ================
prompt  v$latch_children
prompt  ================

select  name, con_id, count(*) 
from    v$latch_children 
where   name like 'Result Cache%' 
group by name, con_id 
order by 1,2;

break on name skip 1

prompt  ==============
prompt  v$latch_parent
prompt  ==============

select  name, con_id, count(*) 
from    v$latch_parent 
where   name like 'Result Cache%' 
group by name,con_id 
order by 1,2;

prompt  =======
prompt  v$latch
prompt  =======

select  name, con_id, count(*) 
from    v$latch 
where   name like 'Result Cache%' 
group by name, con_id 
order by 1,2;

spool off

My results for 19.11.0.0 seem to suggest that there has been no change (as far as latch children go):

================
v$latch_children
================

no rows selected

==============
v$latch_parent
==============

NAME                                 CON_ID   COUNT(*)
-------------------------------- ---------- ----------
Result Cache: MB Latch                    0          1

Result Cache: RC Latch                    0          1
                                          2          1
                                          3          1

Result Cache: SO Latch                    0          1


=======
v$latch
=======

NAME                                 CON_ID   COUNT(*)
-------------------------------- ---------- ----------
Result Cache: MB Latch                    0          1

Result Cache: RC Latch                    0          1
                                          2          1
                                          3          1

Result Cache: SO Latch                    0          1

You will note, however, that I do have three latches called “Result Cache: RC Latch” – so the wrong query for latch information might fool you into thinking that there were multiple latch children unless you’ve include the con_id in your query. There are three latches, but each one is dedicated to a different PDB.

Moving on to 21.3.0.0, though, things change:

================
v$latch_children
================

NAME                                 CON_ID   COUNT(*)
-------------------------------- ---------- ----------
Result Cache: RC Latch                    0          4
Result Cache: RC Latch                    2          4
Result Cache: RC Latch                    3          4

==============
v$latch_parent
==============

NAME                                 CON_ID   COUNT(*)
-------------------------------- ---------- ----------
Result Cache: Heap Queue Latch            0          1

Result Cache: MB Latch                    0          1

Result Cache: RC Latch                    0          1
                                          2          1
                                          3          1

Result Cache: SO Latch                    0          1

Result Cache: Set                         1          1
                                          2          1
                                          3          1

Result Cache; Flush View Latch            1          1
                                          2          1
                                          3          1


12 rows selected.

=======
v$latch
=======

NAME                                 CON_ID   COUNT(*)
-------------------------------- ---------- ----------
Result Cache: Heap Queue Latch            0          1

Result Cache: MB Latch                    0          1

Result Cache: RC Latch                    0          1
                                          2          1
                                          3          1

Result Cache: SO Latch                    0          1

Result Cache: Set                         1          1
                                          2          1
                                          3          1

Result Cache; Flush View Latch            1          1
                                          2          1
                                          3          1


12 rows selected.

I have 12 latch children for the “Result Cache: RC latch” – four for each of the PDBs.

The obvious guess about the number of child latches was that it was dictated by the instance cpu_count, and this seemed to be confirmed when I did a little hacking of the cpu_count (and _disable_cpu_check). It seems a little surprising that it’s taken Oracle so long to make this change (note particularly the original date on my test script) – but splitting the cache into pieces that are each dedicated to a single CPU seems like a good idea.

Footnote

You may have noticed the cautious way in which I described my results fo 19c: “seems to suggest”. There are some latches in Oracle where the number of child latches is dictated by values like cpu_count / 16, and I didn’t feel like hacking the number of CPUs on my laptop up to 17 (or more) to see if that gave me child latches on the RC latch. There are also several patches higher than 19.11 available for 19c, so maybe the 21.3 change has already appeared in later 19c releases.

So, left as exercise to anyone with a very new version of 19c, or with a large number of CPUs, or with both options, feel free to run the script and see if you get any different results; and if you do please report back in the comments.

3 Comments »

  1. […] Result Cache upgrade (Feb 2023): an enhancement to scalability in 21c […]

    Pingback by Infrastructure Catalogue | Oracle Scratchpad — February 10, 2023 @ 12:25 pm GMT Feb 10,2023 | Reply

  2. Hi Jonathan,
    the behaviour with Oracle 19.17 on Exadata X7 with 96 CPUs RAC but without PDB looks similar to 19.10:

    No latch children.

    v$Latch_Parent:
    Result Cache: MB Latch 0 1
    Result Cache: RC Latch 0 1
    Result Cache: SO Latch 0 1

    v$Latch:
    Result Cache: MB Latch 0 1
    Result Cache: RC Latch 0 1
    Result Cache: SO Latch 0 1

    Comment by Anonymous — February 10, 2023 @ 1:01 pm GMT Feb 10,2023 | Reply

    • Many thanks for supplying the answer on that one.

      Regards
      Jonathan Lewis

      Comment by Anonymous — February 10, 2023 @ 1:19 pm GMT Feb 10,2023 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: