Re: V$session_wait.seconds_in_wait

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 21 Jul 2008 17:28:05 -0700 (PDT)
Message-ID: <84aee1a9-0430-4e1e-ac5d-7e44e56cda74@f36g2000hsa.googlegroups.com>


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

Original text of this message