Finding out the row cache lock holder through V$ view

Hanganalyze is always the best tool for finding row cache lock holder as following.

connect sys/oracle as sydba

oradebug setmypid
oradebug hanganalyze 3


-- trace file
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/158/44363/0x3424fb7c/5504/No Wait>
 -- <0/134/928/0x3424f58c/4600/row cache lock>

Am I able to find out the row cache lock holder only through V$ views? The answer is YES and it’s what V$ROWCACHE_PARENT view is meant for.

Let me explain with simple test case.

Create a sequence object with NOCACHE attribute and let two sessions invoke the sequence.nextvalue function recursively. Oracle needs to hold the row cache lock in 5(SRX) mode when calling NEXTVALUE on the nocached sequence, which means it’s very easy to replay the row cache lock contention.

create sequence s1 nocache;

-- temp.sql
declare
 v_value   number;
begin
  for idx in 1 .. 100000 loop
   select s1.nextval into v_value from dual;
  end loop;
end;
/

ho start sqlplus ukja/ukja@ukja1021 @temp
ho start sqlplus ukja/ukja@ukja1021 @temp

Now query on the V$ROWCACHE_PARENT view like following and you get the ID of the row cache lock holder.

col sid new_value sid

select h.address, h.saddr, s.sid, h.lock_mode
from v$rowcache_parent h, v$rowcache_parent w, v$session s
where h.address = w.address and
			w.saddr = (select saddr from v$session where event = 'row cache lock' 
									and rownum = 1) and
			h.saddr = s.saddr and
			h.lock_mode > 0
;

ADDRESS  SADDR           SID  LOCK_MODE
-------- -------- ---------- ----------
283AFB50 3432EB34        145          5

With the session id found out, it’s not a big deal to get the detailed information on the holder session.

UKJA@ukja1021> @session &sid
UKJA@ukja1021> set echo off
01. basic session info
SID                           : 145
SERIAL#                       : 447
SPID                          : 2328
MACHINE                       : POWER_GROUP\UKJAX
PROGRAM                       : sqlplus.exe
PGA                           : 515668
UGA                           : 156280
LAST_CALL_ET                  : 13
LOGON_TIME                    : 2009/10/15 13:50:31
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
02. session wait
SID                           : 145
EVENT                         : row cache lock
P1                            : 13
P1RAW                         : 0000000D
P2                            : 0
P2RAW                         : 00
P3                            : 5
P3RAW                         : 00000005
SECONDS_IN_WAIT               : 0
STATE                         : WAITING
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
03. process info
PID                           : 23
PROGRAM                       : ORACLE.EXE (SHAD)
PGA_USED_MEM                  : 324361
PGA_ALLOC_MEM                 : 623853
PGA_MAX_MEM                   : 623853
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
04. sql info
SID                           : 141
SHARABLE_MEM                  : 8640
PERSISTENT_MEM                : 1156
RUNTIME_MEM                   : 592
EXECUTIONS                    : 100000
FETCHES                       : 100000
BUFFER_GETS                   : 403341
SQL_TEXT                      : SELECT S1.NEXTVAL FROM DUAL
-----------------


05. sql plan info

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

SQL_ID  8c3n1ysfzzd1z, child number 0
-------------------------------------
SELECT S1.NEXTVAL FROM DUAL

Plan hash value: 2479889702

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  SEQUENCE        | S1   |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------
...

Well, it’s just good to find out the usage of the view whose usage is never well known. :)