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