Digital in a box

set pages 999
set lines 90

column c1 heading 'Event|Name'             format a30
column c2 heading 'Total|Waits'            format 999,999,999
column c3 heading 'Seconds|Waiting'        format 999,999
column c4 heading 'Total|Timeouts'         format 999,999,999
column c5 heading 'Average|Wait|(in secs)' format 99.999

ttitle 'System-wide Wait Analysis|for current wait events'

select
  event                         c1,
  total_waits                   c2,
  time_waited / 100             c3,
  total_timeouts                c4,
  average_wait    /100          c5
from
  sys.v_$system_event
where
  event not in (
  'dispatcher timer',
  'lock element cleanup',
  'Null event',
  'parallel query dequeue wait',
  'parallel query idle wait - Slaves',
  'pipe get',
  'PL/SQL lock timer',
  'pmon timer',
  'rdbms ipc message',
  'slave wait',
  'smon timer',
  'SQL*Net break/reset to client',
  'SQL*Net message from client',
  'SQL*Net message to client',
  'SQL*Net more data to client',
  'virtual circuit status',
  'WMON goes to sleep'
  )
AND
event not like 'DFS%'
and
  event not like '%done%'
and
  event not like '%Idle%'
AND
event not like 'KXFX%'
order by
  c2 desc
;

Mon May 08                                                                       page    1
                               System-wide Wait Analysis
                                for current wait events

                                                                   Average
Event                                 Total  Seconds        Total      Wait
Name                                  Waits  Waiting     Timeouts (in secs)
------------------------------ ------------ -------- ------------ ---------
SQL*Net message to dblink           807,153        5            0      .000
SQL*Net message from dblink         807,153    1,990            0      .000
db file sequential read             390,253    1,974            0      .010
log file parallel write             164,575      917      164,205      .010
SQL*Net more data from client       150,271    3,931            0      .030
log file sync                       120,478    1,633          142      .010
control file parallel write         105,916    2,562            0      .020
control file sequential read         82,874        9            0      .000
SQL*Net more data from dblink        51,683      266            0      .010
db file scattered read               34,370      106            0      .000
direct path write                    31,568    1,166            0      .040
direct path read                     22,469      332            0      .010
db file parallel write               18,640    2,735            0      .150
wakeup time manager                  10,099  301,709       10,099    29.880
latch free                            2,405        2           80      .000
log file sequential read              2,292       13            0      .010
async disk IO                         1,834       71            0      .040
rdbms ipc reply                       1,033        6            0      .010
log buffer space                        966      218           32      .230
library cache pin                       953    1,435          477     1.510
enqueue                                 662      106           17      .160
SQL*Net more data to dblink             611        0            0      .000
log file single write                   588        8            0      .010
log file switch completion              496      120           32      .240
buffer busy waits                       413        2            0      .000
db file single write                    176        2            0      .010
log file switch                         129      118          107      .920
LGWR wait for redo copy                 128        0            0      .000
undo segment extension                   28        0           27      .000
control file single write                22        1            0      .020
process startup                          19        2            0      .080
library cache load lock                   5        0            0      .000
switch logfile command                    4        1            0      .200
reliable message                          3        2            2      .750
db file parallel read                     2        0            0      .020
instance state change                     2        0            0      .000
refresh controlfile command               1        0            0      .000
control file heartbeat                    1        4            1     4.010

38 rows selected.

async disk IO
control file parallel write
control file sequential read
db file parallel write
db file scattered read
db file sequential read
direct path read
direct path write
log file parallel write
log file sync

SELECT distinct p1raw
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
AND state = 'WAITING'
;

tti "Object that is Blocking"
COL owner format a8
COL object format a70

SELECT kglnaown AS owner, kglnaobj as Object
FROM sys.x$kglob
WHERE kglhdadr=(SELECT distinct p1raw
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
AND state = 'WAITING')
;

SELECT s.sid||','||s.serial# SID_SERIAL, kglpnmod "Mode Held", kglpnreq "Request"
FROM sys.x$kglpn p, sys.v_$session s
WHERE p.kglpnuse = s.saddr
AND kglpnhdl = (SELECT distinct p1raw
FROM sys.v_$session_wait
WHERE event = 'library cache pin'
AND state = 'WAITING')
;