Oracle Scratchpad

May 16, 2007

v$session_wait_history

Filed under: Troubleshooting — Jonathan Lewis @ 10:07 am BST May 16,2007

One of the (free) aids to trouble-shooting that appeared in 10g was the view v$session_wait_history, a view that lists the last 10 waits for the currently live sessions. For example:

 
SQL> select 
  2  	seq#, event, p1, p2, p3 
  3  from 
  4  	v$session_wait_history 
  5  where 
  6  	sid = 146 
  7  order by 
  8  	seq# 
  9  ;        

   SEQ# EVENT                                  P1         P2         P3 
------- ------------------------------ ---------- ---------- ---------- 
      1 db file scattered read                  1      20409          8 
      2 db file scattered read                  1      20401          8 
      3 db file scattered read                  1      20393          8 
      4 db file scattered read                  1      20385          8 
      5 db file scattered read                  1      20377          8 
      6 db file scattered read                  1      20369          8 
      7 db file scattered read                  1      20361          8 
      8 db file scattered read                  1      20225          8 
      9 db file scattered read                  1      20217          8 
     10 db file scattered read                  1      20209          8        

10 rows selected. 

One of the first things I did with the wait_row() function that I described in my last blog, was to set up a simple way to emulate this view in 8i and 9i. The code, which has to be run under the sys schema is as follows:

 
create or replace function wait_row( 
	i_v	in	number, 
	i_sec	in	number default 0.01 
) return number 
	deterministic 
	parallel_enable 
as 
begin 
	sys.dbms_lock.sleep(i_sec); 
	return i_v; 
end; 
/       

spool wait_history       

create or replace view v$jpl_wait_history as 
select * from 
( 
select 
	5	wait_no, 
	sid, seq#, event, p1text, p1, p1raw, 
	p2text, p2, p2raw,p3text, p3, p3raw 
from	v$session_wait 
union all 
select 
	wait_row(-5), 
	null, null, null, null, null, null, 
	null, null, null, null, null, null 
from dual 
union all 
select 
	4, 
	sid, seq#, event, p1text, p1, p1raw, 
	p2text, p2, p2raw,p3text, p3, p3raw 
from	v$session_wait 
union all 
select 
	wait_row(-4), 
	null, null, null, null, null, null, 
	null, null, null, null, null, null 
from dual 
union all 
select 
	3, 
	sid, seq#, event, p1text, p1, p1raw, 
	p2text, p2, p2raw,p3text, p3, p3raw 
from 	v$session_wait 
union all 
select 
	wait_row(-3), 
	null, null, null, null, null, null, 
	null, null, null, null, null, null 
from dual 
union all 
select 
	2, 
	sid, seq#, event, p1text, p1, p1raw, 
	p2text, p2, p2raw,p3text, p3, p3raw 
from 	v$session_wait 
union all 
select 
	wait_row(-2), 
	null, null, null, null, null, null, 
	null, null, null, null, null, null 
from dual 
union all 
select 
	1, 
	sid, seq#, event, p1text, p1, p1raw, 
	p2text, p2, p2raw,p3text, p3, p3raw 
from	v$session_wait 
) 
where 
	wait_no > 0 
;       

create public synonym v$jpl_wait_history for v$jpl_wait_history; 
grant select on v$jpl_wait_history to public; 

It’s not a particularly clever bit of SQL and it does depend on several assumptions about how the optimizer will handle it – for example, will the separate query blocks in a union all be executed top to bottom, bottom to top, or in some other order; but it does a reasonable job.

Here’s a little sample of the output:

 
column wait_no	format 99 
column seq#	format 99,999 
column event	format a25       

select 
	wait_no, seq# , event, p1, p2, p3 
from 
	v$jpl_wait_history 
where 
	sid = 10 
order by 
	wait_no 
;           

WAIT_NO    SEQ# EVENT                             P1         P2         P3 
------- ------- ------------------------- ---------- ---------- ---------- 
      1  58,035 db file sequential read            1      30601          1 
      2  58,035 db file scattered read             1      30624          6 
      3  58,033 db file scattered read             1      30688          8 
      4  58,030 db file sequential read            1      30724          1 
      5  58,029 db file sequential read            1      30724          1 

As you can see, it’s not very high precision – we’ve missed some waits with our sleep of 1/100th of a second. You will also find that sometimes you report the same wait several times because a single wait may last (much) longer than 1/100th of a second.

Despite these drawbacks, there may be cases where this rapid sampling will give you enough clues about a problem to make some headway.

4 Comments »

  1. Jonathan, this is a very good idea, thanks for this.

    Comment by Yas — May 16, 2007 @ 12:37 pm BST May 16,2007 | Reply

  2. If you want to see a more dramatic, and finer-grained, implementation of the same sort of thing take a look at this blog by Tanel Poder.

    Comment by Jonathan Lewis — June 13, 2008 @ 7:45 pm BST Jun 13,2008 | Reply

  3. [...] time ago I wrote a note about using dbms_lock() to slow down your SQL, and followed up with an example of using this type of code to help with [...]

    Pingback by Event snapshots « Oracle Scratchpad — September 28, 2008 @ 7:40 pm BST Sep 28,2008 | Reply

  4. [...] couple of examples of usage: Modelling (approximately) v$session_wait_history in older versions of Oracle. Rapid snapshots of the event histogram view. Comments [...]

    Pingback by Slow Code « Oracle Scratchpad — December 30, 2009 @ 3:11 pm BST Dec 30,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,267 other followers