mutex_awr_waits.sql

/*
     Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru)
     https://andreynikolaev.wordpress.com 

     Sessions waited for mutexes from DBA_HIST_ACTIVE_SESS_HISTORY
     Some columns were not printed to fit linesize 80. You can adjust this.
     BLKS - Blocking SID<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>
     LOC  - mutex sleep Location_ID
     RFC  - mutex RefCount

     usage: @mutex_awr_waits
*/
col sample_id noprint format 9999999
col sample_time format a17
col session_id heading "SID" format 9999
col session_serial# noprint format 9999
col event format a23
col blocking_sid heading "BLKS" format 9999
col shared_refcount noprint heading "RFC" format 99
col location_id heading "LOC" format 99
col sleeps  noprint format 99999
col mutex_object format a15
set pagesize 50000
set wrap off

SELECT sample_id,to_char(sample_time,'dd-mm-yy hh24:mi:ss') sample_time,session_id,session_serial#,sql_id,event,p1 IDN,
            FLOOR (p2/POWER(2,4*ws)) blocking_sid,MOD (p2,POWER(2,4*ws)) shared_refcount,
            FLOOR (p3/POWER (2,4*ws)) location_id,MOD (p3,POWER(2,4*ws)) sleeps,
            (select nvl(CASE WHEN (event LIKE 'library cache:%' AND p1 <= power(2,17)) THEN  'library cache bucket: '||p1
                             ELSE  (SELECT kglnaobj FROM x$kglob WHERE kglnahsh=p1 AND (kglhdadr = kglhdpar) and rownum=1) END,
                        (select substr(sql_text,1,1000) from dba_hist_sqltext where DBMS_UTILITY.SQLID_TO_SQLHASH(sql_id)=p1
                             and dbid=dba_hist_active_sess_history.dbid and rownum=1))  from dual) mutex_object
       FROM (SELECT DECODE (INSTR (banner, '64'), 0, '4', '8') ws FROM v$version WHERE ROWNUM = 1) wordsize,
                  dba_hist_active_sess_history
       WHERE p1text='idn' AND session_state='WAITING'
and rownum < 1000        ORDER BY sample_id desc; /*  SQL> @mutex_awr_waits

SAMPLE_TIME         SID SQL_ID        EVENT                          IDN  BLKS LOC MUTEX_OBJECT
----------------- ----- ------------- ----------------------- ---------- ----- --- ------------
19-09-17 02:25:29  1982 ahkdwaxwnhvwf cursor: pin S wait on X 2034790286   535  5 create table
19-09-17 03:04:26  1085 cu32d197864an cursor: pin S wait on X 1317212500  1835  5 WITH MONITOR_
19-09-17 02:52:33  2868 37vwvqw8nq24a library cache: mutex X   919375363  1519  0 WRI$_REPT_SQL
...
*/