Oracle Study Notes by Denis: Oracle Wait Event Tuning

Thursday, September 13, 2007

Check this article:

yds said...: http://www.remote-dba.net/oracle_10g_tuning/t_oracle_v$session_wait.htm

select

username,

event,

p1,

p2

from

v$session_wait

where

sid = 74;

The output of this query for a particular session with SID 74 might look like this:

USERNAME EVENT SID P1 P2

---------- ----------------- --- -- ---

PCS buffer busy waits 74 4 155

Columns P1 and P2 allow the DBA to determine file and block numbers that caused this wait event. The query below retrieves the object name that owns data block 155, the value of P2 above:

select

segment_name,

segment_type

from

dba_extents

where

file_id = 4

and

155 between

(block_id and block_id + blocks – 1);

SEGMENT_NAME SEGMENT_TYPE

------------------------------ ---------------

orders TABLE; September 14, 2007 5:48 AM

Oracle Study Notes by Denis