Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Finding out the row cache lock holder through V$ view

with 2 comments

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. :)

About these ads

Written by Dion Cho

October 15, 2009 at 6:44 am

Posted in Misc.

Tagged with ,

2 Responses

Subscribe to comments with RSS.

  1. Hi Dion cho,

    I am very much happy to read your blog .. but i would like to have yours books in english .. which is not available i guess. Can you please help me out.

    Regards

    Bala

    Balakrishna

    October 30, 2009 at 11:30 am

  2. Hi Dion,

    Thanks so much for this small piece of SQL. It was 4 AM here and got a call from office that most of the sessions were going in “row cache lock” wait, was Googling for it and came across your post. So helpful it was for identifying the holder session.

    Thanks again.

    Regards,
    Amardeep Sidhu

    Amardeep Sidhu

    June 6, 2010 at 12:16 am


Leave a Reply

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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: