/*
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
cursor_pin_s_waits.sql
3 Comments »
RSS feed for comments on this post. TrackBack URI
[...] "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 |
[...] 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 |
[...] 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 |