latch_spin_trace.sql

/*
     This file is part of demos for "Contemporary Latch Internals" seminar v.18.09.2010
     Copyright:   (c) Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) RDTEX 

http://AndreyNikolaev.wordpress.com

     shared_latches table contents can be downloaded from:

http://andreynikolaev.wordpress.com/summary-tables/shared_latches-ctl/

     usage:
       sqlplus/nolog @trace_spin_wait <latch#>
     Substitute DTrace script spin.d to trace latch spin 
*/
connect / as sysdba
set echo on
set define %
set verify off
alter session set max_dump_file_size=0;
/* determine a function to to acquire the latch */
var db_v varchar2(100);
var db_comp varchar2(100);
begin dbms_utility.db_version(:db_v,:db_comp); end;
/
col latch_function new_value latch_function
col addr new_value laddr
col lname new_value lname
select decode(shared,'Y'
        ,decode(sign(replace(:db_v,'.','')-110000),1,'kslgetsl_w', 'kslgetsl')
        ,'kslgetl') latch_function,
      a1.addr,
      translate(a1.name,' /$''','____') lname
from v$latch_parent a1,shared_latches a2
  where a1.name=a2.name (+)
  and a1.latch# = a2.latch# (+)
  and a1.latch#=%1
  and nvl(version,:db_v) = :db_v;
/* spid of current shadow process */
col spid new_value spid
SELECT spid
 FROM v$process
 WHERE addr IN (SELECT paddr
  FROM v$session WHERE SID =  (SELECT SID FROM v$mystat WHERE ROWNUM = 1));
/* prepare script to acquire latch for 10 sec 
   if latch is shared, get it in S mode 
*/
set echo off
set head off
spool get_latch_to_trace.sql
select 'connect / as sysdba'||chr(10)||'oradebug setmypid'||chr(10)||
      'oradebug call '||'%latch_function'||
         ' 0x'||'%laddr'||' 1 2 3 8'||chr(10)||
      'host sleep 30'||chr(10)||
      'oradebug call kslfre 0x'||'%laddr'||chr(10)||
      'exit'||chr(10) 
 from dual;
spool off
host sqlplus /nolog @get_latch_to_trace.sql &
oradebug setmypid
host /usr/sbin/dtrace -ZCs spin.d -p %spid &
/* wait until DTrace startup */
host sleep 10
/* Acquire latch. If latch is shared get it in X mode */
oradebug call %latch_function 0x%laddr 1 2 3 16
oradebug call kslfre 0x%laddr
exit