Result Cache Latch in 11GR2: Shared Mode Gets

Almost two years ago I wrote a post about 11GR2 Result Cache Scalability. In the post I noted that there has been a significant improvement compared to 11GR1. What left me a little bit puzzled is where exactly the performance advantage came from. After all, it didn't look like the latch was changed to allow for shared mode gets as I still observed some sleeps during my tests. Back then I was quite happy with the results and made a note to revisit the subject in order to figure out exactly what happened which then got on my back burner and eventually slipped away.

Result Cache: RC Latch

We can begin by testing whether shared mode gets are allowed or not using an oradebug call:

SQL> select addr from v$latch where name='Result Cache: RC Latch';

ADDR
----------------
0000000060040B70

SQL> oradebug setmypid
Statement processed.
SQL> oradebug call kslgetsl_w 0x0000000060040B70 1 1 1 8
Function returned 1

The forth argument (8) tells the function to acquire the latch in the shared mode and it does work! Now let's see what happens when another session wants to put something into the result cache:

SQL> create table t as select 1 n from dual;

Table created.

SQL> select /*+ result_cache */ * from t;
--session waits

As expected, the session waits. We do need to get a latch in exclusive mode in order to change the result cache memory contents so we can put something new in there. We can confirm that the session indeed waits on the Result Cache latch (which can be seen by the latch address):

SQL> select event, to_char(p1, 'xxxxxxxxxx') latch_addr, seconds_in_wait, state
  2    from v$session_wait
  3    where sid=63;
 
EVENT           LATCH_ADDR  SECONDS_IN_WAIT STATE
--------------- ----------- --------------- -------------------
latch free         60040b70              49 WAITING

Let's free the latch in our first session:

SQL> oradebug call kslfre 0x0000000060040B70
Function returned 0

Immediately our second session proceeds. Now it's time to do the most interesting part -- would the session block again if it only needs to read from the result cache?

Session 1:

SQL> oradebug call kslgetsl_w 0x0000000060040B70 1 1 1 8
Function returned 1

Session 2:

SQL> select /*+ result_cache */ * from t;

         N
----------
         1

For you see -- the latch was indeed changed to allow for shared mode gets! We can further verify the behavior by acquiring the Result Cache latch in exclusive mode in our first session:

SQL> oradebug call kslfre 0x0000000060040B70
Function returned 0
SQL> oradebug call kslgetsl_w 0x0000000060040B70 1 1 1 16
Function returned 1

The value of 16 in the forth argument tells the function to acquire the latch in exclusive mode. Let's test the second session again:

SQL> select /*+ result_cache */ * from t;
--sessin waits

Well, you can't get a shared mode if someone else has exclusive. Again, freeing the latch in our first session allows second session to proceeds.

So it is confirmed -- in 11GR2 the Result Cache latch can be acquired in a shared mode!