Tuesday, November 26, 2013

Result Cache Latch and PDBs

One interesting aspect of Oracle 12cR1 database when it comes to PDBs is how latching is done. For example, if all PDBs have to work under the same latch then contention in one PDB can easily affect users in other PDBs too.

Continuing my series of posts on the Result Cache latch today I'll check what happens when you try to acquire RC latch from two different PDBs.

Session 1

The first session is connected under container name TEST:
SQL> select sys_context('userenv', 'con_name') con_name from dual;

CON_NAME
--------------------
TEST

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

ADDR
----------------
0000000060041C78
Session 2

The second session is connected under container name TEST2:
SQL> select sys_context('userenv', 'con_name') con_name from dual;

CON_NAME
--------------------
TEST2

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

ADDR
----------------
0000000060041C78
As you can see the address of the latch is exactly the same under both containers so both PDBs appear to share exactly the same latch. Let's confirm it by trying to acquire the latch in exclusive mode in both sessions:

Session 1
SQL> oradebug setmypid
Statement processed.
SQL> oradebug call kslgetsl_w 0x0000000060041C78 1 1 1 16
Function returned 1
Session 2
SQL> oradebug setmypid
Statement processed.
SQL> oradebug call kslgetsl_w 0x0000000060041C78 1 1 1 16
...session hangs...
...and we can confirm that it waits on the RC latch:
SQL> select event, p1, to_char(p1, 'fmxxxxxxxxxxx') addr, state, seconds_in_wait
  2   from v$session_wait
  3   where sid=18;
 
EVENT              P1 ADDR         STATE               SECONDS_IN_WAIT
---------- ---------- ------------ ------------------- ---------------
latch free 1610882168 60041c78     WAITING                          25
The bottom line is that the single RC latch appears to be shared by all PDBs.

No comments:

Post a Comment