Advanced wait detail analysis in Oracle9i   Donald K. Burleson

When performing system-wide tuning of an Oracle9i database, the Oracle professional often needs to get detailed event based information from the Oracle data dictionary.  The following query is used by many senior or professionals in order to get details off specific system events within the Oracle database, and categorize the events by specific sessions and processes.

The ability to break down individual offenses by sessions and processes is critical to the tuning of an Oracle system because the Oracle professional will be able to drill-down and see the details for specific event processes within the Oracle system.

The following query joins the v_$session_event view with v$session and v$bgprocess views to develop quick picture all of all event waits categorized by session and background process name.

Please note the query filters-out specific events that are not germane to specific sessions and background processes.  This article waters the output and makes it easier to very quickly identify because of an event waits related slowdowns.

select

   b.sid                                     c1,

   decode(b.username,NULL,c.name,b.username) c2,

   event                                     c3,

   a.total_waits                             c4,

   round((a.time_waited / 100),2)            c5,

a.total_timeouts                             c6,

   round((average_wait / 100),2)             c7,

   round((a.max_wait / 100),2)               c8

from

   sys.v_$session_event a,

       sys.v_$session b,

       sys.v_$bgprocess c

 where

   event NOT LIKE 'DFS%'

and

   event NOT LIKE 'KXFX%'

and

   a.sid = b.sid

and

   b.paddr = c.paddr (+)

and

   event NOT IN

   (

   'lock element cleanup',

   'pmon timer',

   'rdbms ipc message',

   'smon timer',

   'SQL*Net message from client',

   'SQL*Net break/reset to client',

   'SQL*Net message to client',

   'SQL*Net more data to client',

   'dispatcher timer',

   'Null event',

   'io done',

   'parallel query dequeue wait',

   'parallel query idle wait - Slaves',

   'pipe get',

   'PL/SQL lock timer',

   'slave wait',

   'virtual circuit status',

   'WMON goes to sleep'

          )

order by 4 desc

;

Here is a sample of the output.

Wed Oct 23                                                       page    1

                                System-wide Wait Analysis

                                 for current wait events

                                                                    Average

Event                                 Total  Seconds        Total      Wait

Name                                  Waits  Waiting     Timeouts (in secs)

------------------------------ ------------ -------- ------------ ---------

db file sequential read             115,000      368            0      .003

SQL*Net more data from client        11,218      579            0      .052

log file parallel write               6,047      198            0      .033

direct path read                      4,732        0            0      .000

log file sync                         4,504      128            0      .028

control file parallel write           3,259       75            0      .023

log file sequential read              3,218        2            0      .001

db file scattered read                2,473       44            0      .018

file open                             2,188        0            0      .000

latch free                            1,499        5          833      .003

db file parallel write                1,273        0            0      .000

control file sequential read          1,060        0            0      .000

direct path write                       549        0            0      .000

wakeup time manager                     312    9,556          311    30.628

library cache pin                       304        3            0      .011

LGWR wait for redo copy                 299        0            2      .000

file identify                           138        0            0      .001

db file parallel read                   115        2            0      .017

buffer busy waits                        91        0            0      .004

refresh controlfile command              82        0            0      .002

enqueue                                  24       24            7     1.013

log file single write                    18        0            0      .016

rdbms ipc reply                          16        5            1      .314

process startup                          13        1            0      .102

library cache load lock                  13        0            0      .038

db file single write                      5        0            0      .026

log file switch completion                2        1            0      .320

instance state change                     2        0            0      .005

single-task message                       2        0            0      .005

reliable message                          1        0            0      .000

buffer deadlock                           1        0            1      .000

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: