OWI... wan-kenobi?

Sure you remember the Star Wars zaga ... and Obi-wan-kenobi as the first 'Force'-instructor of Luke. Well, my point here is how to use the Force with Oracle Server... with OWI or Oracle Wait Interface as its long name stands.

That is not an 'interface' in the way you pass values to the DB Kernel, instead you get information of waits generated in the system and wait events within the sessions... yes, prepare you laser-saber to fight all those annoying performance issues, your mind will broad his perception.

v$session_event (9i)
Lists all wait events for all sessions, with number of ocurrences and timing stats.

v$session_wait (9i)
Shows current wait event for sessions.

This is one of my favourite scripts that shows ans example of OWI usage. I've been using this script to analyze a performance issue with Peoplesoft: disk contention.

SELECT
a.sid,
a.osuser,
a.process,
substr(a.client_info,1,40) as "ClientInfo" ,
a.LOGON_TIME,
b."TimeWaited_Minutes",
((sysdate-LOGON_TIME)*24*60) as "Sess_TIME"
FROM v$session a,
(SELECT
sid,
total_waits,
TO_CHAR((time_waited/100)/60,'9,990.999') as "TimeWaited_Minutes"
FROM v$session_event c
WHERE c.EVENT = 'db file sequential read') b
WHERE username = '{PS owner username}'
AND status = 'ACTIVE'
AND a.SID = b.sid

v$event_histogram (10g)
Shows current instance histrograms for every wait event, organized on time interval buckets that progress following the formula 2^n milliseconds. For instance:

EVENT# EVENT         WAIT_TIME_MILLI WAIT_COUNT
11 Log archive I/O     1                29069
11 Log archive I/O     2                  115
11 Log archive I/O     4                  211
11 Log archive I/O     8                  391
11 Log archive I/O    16               140973
11 Log archive I/O    32               103846
11 Log archive I/O    64                21678
11 Log archive I/O   128                 4953
11 Log archive I/O   256                  822
11 Log archive I/O   512                   31

It's important to mention that the initizialization parameter TIMED_STATISTICS must be set to TRUE, and statistics are lost (or reset) when the database is shutdown or started up.

v$eventmetric (10g)
With this view you'll be able to see the last 60 seconds of wait event metrics, which gives a more recent time frame than v$event_histogram. This is a small sample of its content.

BEGIN_TIME   END_TIME  INTSIZE_CSEC EVENT# EVENT_ID NUM_SESS_WAITING TIME_WAITED WAIT_COUNT
29/02/2008 11:49:48 a.m. 29/02/2008 11:50:47 a.m. 5960 0 2516578839 0   0  0 
29/02/2008 11:49:48 a.m. 29/02/2008 11:50:47 a.m. 5960 1 3539483025 1   5564.8417 32 
29/02/2008 11:49:48 a.m. 29/02/2008 11:50:47 a.m. 5960 2 3934444552 0   0  0 
29/02/2008 11:49:48 a.m. 29/02/2008 11:50:47 a.m. 5960 3 866018717 10   50716.6575 9871 
29/02/2008 11:49:48 a.m. 29/02/2008 11:50:47 a.m. 5960 4 3083157888 0   0  0 
29/02/2008 11:49:48 a.m. 29/02/2008 11:50:47 a.m. 5960 5 2324796046 0   0  0 

You'll get a more meaningful output if you use (or modify) the following script:

SELECT
      b.NAME,
      to_char(a.BEGIN_TIME,'DD-MON-YYYY') as BeginDay,
      to_char(a.BEGIN_TIME,'HH24:MI:SS') as BeginTime,
      to_char(a.END_TIME, 'HH24:MI:SS' ) as EndTime,
      a.NUM_SESS_WAITING, a.TIME_WAITED, a.WAIT_COUNT
FROM  v$eventmetric a, v$event_name b
WHERE   a.EVENT_ID = b.EVENT_ID
ORDER BY time_waited DESC

Master these tables and you'll be invincible when fighting the Dark Side.

Subscribe to Oracle Database Disected by Email