Re: V$session_wait.seconds_in_wait

On Jul 21, 6:00 pm, mak..._at_gmail.com wrote:
>    SELECT      sid ,event,state,
>                seconds_in_wait siw,wait_time,p1text p1txt,p1,p2text
> p2txt,p2,p3text p3txt,p3
>    FROM        v$session_wait WHERE       sid = &sid
>
>    SID Wait Event           Wait State       SIW    wait_time
> P1TXT                P1 P2TXT         P2       P3TXT               P3
> ------ -------------------- ------------- -------- --------
> ----------   ------------ --------      -------- --------
> --------
>   4000 direct path read temp WAITED KNOWN      909        1     file
> numbe         4523 first db      2651740  block cn             1
>   4000 direct path read temp WAITED KNOWN      913        1     file
> numbe         4523 first db      2651740  block cn             1
>   4000 direct path read temp WAITED KNOWN      916        1     file
> numbe         4523 first db      2651740  block cn             1
>
> Can someone interpret this 3 successive reading for above query for
> me… Seconds_in_wait & wait_time are confusing me..
>
> Is it one DPRT (direct path read temp) taking 900+ seconds or is it
> 900+ DPRT that are taking 1/100 sec each?  If it’s latter, then why
> the block# is not changing? Why it’s reading the same block again &
> again? I’m trying to figure if I have a i/o issue or not…

Based on the notes that I have, SECONDS_IN_WAIT is meaningless when STATE is anything other than WAITING. The session waited roughly 1 second reading 1 block from file# 4523, possibly for the purpose of reading the results of a sort to disk.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Jul 21 2008 - 19:28:05 CDT