V$session_wait.seconds_in_wait
mak...@gmail.com
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…
Charles Hooper
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.
Mark D Powell
>
> - Show quoted text -
A very complete answer to this same question was posted on the OTN
database forum today.
The Oracle version# Reference Manual includes coverage of this v$ view
and the meaning of its columns. The time units in the view vary by
Oracle release and are the values are only approximate wait times.
HTH -- Mark D Powell --
Charles Hooper
> A very complete answer to this same question was posted on the OTN
> database forum today.
>
> The Oracle version# Reference Manual includes coverage of this v$ view
> and the meaning of its columns. The time units in the view vary by
> Oracle release and are the values are only approximate wait times.
>
Thanks for the heads up. Jonathan's answer is a bit different than
mine... read that as I think that I have found that my notes are
either out of date or were never correct, but in either case,
Jonathan's answer is both correct and complete.
I believe that my noted originated from this document, one one very
similar (the original source could have been the author's book):
"Tuning Oracle Without Cache-Hit Ratios"
http://www.quest-pipelines.com/newsletter-v3/0402_C.htm
"If State = (Waiting or Waited Unknown Time or Waited Short Time)
then
Wait_Time = Irrelevant;
End If;
If State = (Waited Known Time) then
Wait_Time = Actual wait time, in seconds;
End If;"
It appears that the syntax in the Oracle reference manuals has been
clarified related to this performance view:
8i Release 2 Reference:
http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch3142.htm
WAIT_TIME, If > 0 - WAITED KNOWN TIME (WAIT_TIME = duration of last
wait)
10g Release 2 Reference:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2094.htm
WAIT_TIME, If > 0 - WAITED KNOWN TIME (WAIT_TIME = duration of last
wait)
11g Release 1 Reference:
http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28320/dynviews_3023.htm
WAIT_TIME, If > 0 - Value is the duration of the last wait in
hundredths of a second
I am beginning to wonder if WAIT_TIME was ever expressed in whole
seconds, and not always as hundredths of a second.
Time to update my notes...
Mladen Gogala
> The Oracle version# Reference Manual includes coverage of this v$ view
> and the meaning of its columns. The time units in the view vary by
> Oracle release and are the values are only approximate wait times.
Seconds in wait column is deprecated in the latest version of Oracle.
Let me quote the reference manual for 11g:
SECONDS_IN_WAIT NUMBER If the session is currently waiting, then
the value is the amount of time waited for the current wait. If the
session is not in a wait, then the value is the amount of time since the
start of the last wait.
This column has been deprecated in favor of the columns WAIT_TIME_MICRO
and TIME_SINCE_LAST_WAIT_MICRO.
Now, if we assume that all of us have converted all of our databases to
11g, there should be no problem.
--
Mladen Gogala
http://mgogala.freehostia.com