cursor_pin_s_waits.sql

/*
     This file is part of demos for "Latch, mutex and beyond"  blog 
     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) 
     https://andreynikolaev.wordpress.com

     Trace "Cursor: pin S" waits from OS.

     Beware: The script will restart the test database.

     usage: sqlplus /nolog @cursor_pin_s_waits.sql 
*/
connect / as sysdba
set echo on
set define %
rem set verify off
set linesize 300

col spid new_value spid
col ws new_value ws


/*  1. Induce "Cursor: pin S" contention to determine "Cursor pin" mutex address */
@cursor_pin_s_contention.sql 2 
set head on
set echo on

col mutex_addr new_value cursor_pin_mutex

SELECT MUTEX_ADDR,mutex_type,MAX (GETS),MAX (SLEEPS)
        FROM x$mutex_sleep_history
        WHERE MUTEX_IDENTIFIER = 3222383532 AND MUTEX_TYPE like('Cursor Pin%')
GROUP BY mutex_addr, mutex_type;

prompt "Wait until disconnect of submitted sessions"
pause

SELECT spid FROM v$process join v$session on (addr=paddr)  natural join  (SELECT SID FROM v$mystat WHERE ROWNUM = 1);
select decode(instr(banner,'64'),0,'32','64') ws from v$version where rownum=1;


exec dbms_workload_repository.create_snapshot();

/*  2. Simulate the mutex get */
host sqlplus /nolog @mutex_e_mode %cursor_pin_mutex &
host truss -o oswaits.lst -v semtimedop -p %spid  &
rem host /usr/sbin/dtrace -ZCs syscalls_in_mutex_wait.d -%ws -p %spid &
rem host strace -o oswaits.lst  -p %spid  &
set timing on

host sleep 1
select 1 from dual where 1=2;

/*   Last 10 waits of this session */

col sid format 9999
col wtime format 9999999
col event format a19
set wrap off

SELECT SID,wait_time wtime,event,p1,p2,p3
  FROM v$session_wait_history 
  WHERE sid=(select sid from v$mystat where rownum=1)
  order by seq#  desc   ;
exec dbms_workload_repository.create_snapshot();
exit