latch_wait_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

     usage:
       sqlplus/nolog @trace_latch_wait <latch#>

     Trace syscalls during wait for parent latch identified by latch#.

     This script intentionally use old-fashioned SQL to work with Oracle 8i
     shared_latches table can be downloaded from:

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

*/
connect / as sysdba
set echo on
set define %
set verify off
alter session set max_dump_file_size=0;

/* determine a function 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;


/* determine 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 the latch for 3 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 3'||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
/* trace system calls for latch wait. Uncomment line corresponding to your OS */
/* Solaris: */
host truss -o%lname..lst  -p %spid &

/* Linux: */
/* host strace -o%lname..lst  -p %spid & */
/* HP-UX: */
/* host tusc -o%lname..lst  -p %spid & */

host sleep 1

/* Acquire the 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