Latch, mutex and beyond

library_cache_mutex_x_contention.sql

/*
     This file is part of demos for "Mutex Internals"  seminar v.04.04.2011
     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)

http://andreynikolaev.wordpress.com

     "library cache: mutex X" contention testcase.

     The script spawn several sessions to induce "library cache: mutex X", 
     creates AWR snapshots and generates report

     Beware: The script will restart the test database. 
             Wait timeout is 30 minutes. Adjust it for your environment.

     usage: sqlplus /nolog @library_cache_mutex_contention.sql <parallelism>

*/ 
connect / as sysdba
/* restart the instance */
startup force
set echo off
set define %
set verify off
set head off
set linesize 300

/* spawn parallel processes to create load on library cache mutex */
spool one_thread.tmp
select 'set verify off' from dual;
select 'alter session set session_cached_cursors = 50;' from dual; 
select 'set timing on' from dual; 
select 'begin for i in 1..1000000 loop' from dual;
select '      execute immediate ''begin dbms_application_info.set_client_info(''''mutex'''');end;'';' from dual;
select 'end loop; end;'||chr(10)||'/' from dual;
spool off

/* sleep while AWR initialized */
host sleep 5 
exec dbms_workload_repository.create_snapshot();

spool many_threads.tmp
select 'host sqlplus "/ as sysdba" @one_thread.tmp &'
from dba_objects where rownum <= %1;
spool off
@many_threads.tmp
host sleep 5
prompt "The script will wait now for 30 min." 
host sleep 1800
set echo on
exec dbms_workload_repository.create_snapshot();
set markup html on
set head on
spool mutex_sleep_history.htm
select to_char(SLEEP_TIMESTAMP,'hh:mi:ss.ff') SLEEP_TIMESTAMP,MUTEX_ADDR,MUTEX_IDENTIFIER,MUTEX_TYPE,
       GETS,SLEEPS,REQUESTING_SESSION,BLOCKING_SESSION,
       LOCATION,MUTEX_VALUE from x$mutex_sleep_history order by sleep_timestamp;
spool off

set define &
set markup html off
set echo off heading on underline on;
column inst_num  heading "Inst Num"  new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
  from v$database d,
       v$instance i;
define num_days=1;
define report_type='html';
col end_snap new_value end_snap;
col begin_snap new_value begin_snap;
select max(snap_id) end_snap from dba_hist_snapshot where dbid=&dbid;
select max(snap_id) begin_snap from dba_hist_snapshot where dbid=&dbid and snap_id < &end_snap;
define report_name='awr_report.htm';
@?/rdbms/admin/awrrpti
exit

2 Comments »

  1. [...] at “library_cache_mutex_x_contention.sql” script. It spawns several sqlplus sessions to execute the above PL/SQL block concurrently. In [...]

    Pingback by Divide and conquer the “true” mutex contention « Latch, mutex and beyond — May 1, 2011 @ 7:17 pm | Reply

  2. [...] at the following graph. It compares time to complete my “library cache mutex X” testcase for Solaris T2000 with 100Hz and 1000Hz timers. Predictably, when the number of threads is small, [...]

    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


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 67 other followers

%d bloggers like this: