Latch, mutex and beyond

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

2 Comments »

  1. [...] I will use oradebug call to acquire the latch concurrently from two sessions. My tool will be latch_spin_trace.sql. This script differs from previously used latch_wait_trace.sql by timeout values. In addition, it [...]

    Pingback by Spin tales: Part 1. Exclusive latches in Oracle 9.2-11g « Latch, mutex and beyond — January 6, 2011 @ 9:59 pm | Reply

  2. [...] corresponding row of x$ksllclass. Again, I will trace the latch acquisition using DTrace and the latch_spin_trace.sql script. In my previous posts, I introduced several DTrace scripts to trace various aspects of latch [...]

    Pingback by Spin tales: Part 3. Non-standard latch classes in Oracle 9.2-11g « Latch, mutex and beyond — January 18, 2011 @ 8:20 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: