Latch, mutex and beyond

cursor_pin_s_waits.sql

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

http://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

3 Comments »

  1. [...] "release" the mutex, I will change its value back to zero. I wrote script mutex_e_mode.sql that "holds" mutex in E mode for 50 [...]

    Pingback by Mutex waits. Part 1. “Cursor: Pin S” in Oracle 10.2-11.1. Invisible and aggressive. « Latch, mutex and beyond — July 9, 2011 @ 1:19 pm | Reply

  2. [...] previous post, I introduced the testcase in which session waited for mutex during 49 sec. In Oracle 11.2.0.1 for Solaris SPARC on T2000 [...]

    Pingback by Mutex waits. Part II. “Cursor: Pin S” in Oracle 11.2 _mutex_wait_scheme=0. Steps out of shadow. « Latch, mutex and beyond — October 25, 2011 @ 4:23 pm | Reply

  3. [...] April 2011 demonstrated almost negligible CPU consumption during mutex waits. Remember that in my testcase one session statically waits for “Cursor Pin” mutex during [...]

    Pingback by Mutex waits. Part III. Contemporary Oracle wait schemes diversity. « Latch, mutex and beyond — July 30, 2012 @ 12:22 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. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 63 other followers

%d bloggers like this: