You are hereOracle Wait Interface-Introduction

Oracle Wait Interface-Introduction


By OracleDisected - Posted on 23 April 2008

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 favorite scripts that shows an 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 histograms 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 initialization 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.


View my page on Oracle Community
View my blog on Blogger

Subscribe in a reader

Tags


Syndicate

Syndicate content

Follow DatabasesLA on Twitter

Who's online

There are currently 0 users and 5 guests online.

Estadisticas

Locations of visitors to this page

hidden hit counter