v$session_wait_history

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.