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.
Jonathan, this is a very good idea, thanks for this.
Comment by Yas — May 16, 2007 @ 12:37 pm BST May 16,2007 |
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 |
[…] 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 |
[…] 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 GMT Dec 30,2009 |