4 Replies Latest reply: Jul 22, 2008 8:14 AM by max71 RSS


      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…
        • 1. Re: V$session_wait.seconds_in_wait

          First of all, you REALLY should learn to include a version in ALL of your posts.
          The units of those columns has changed several times between releases.
          This brings me to the second remark.
          All views are documented. In the table dict, in the table dict_columns, in the table dict_comments and in the Oracle Reference Manual for your version.
          If you have a query about the meaning of a column, PLEASE visit http://tahiti.oracle.com and look up the column yourself.
          Please do NOT ask volunteers to abstract the information for three or four different versions.

          That said, if I recall correctly, since 9i wait_time is expressed in centiseconds.
          and seconds_in_wait is the total acummulated time Oracle has waited for this block.
          Your unformatted output simply shows the block has to be revisited.
          Direct path read temp is a result of operations involving sort and the like, so yes, revisits are quite probable.

          As seconds_in_wait is huge, you appear to have an I/O bottleneck, however it looks like you have bigger problems: I never saw a file with the number 4523.

          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: V$session_wait.seconds_in_wait
            Jonathan Lewis
            max 71,

            "Seconds in wait" is a bad name for the column. A more realistic name would be something like: "Approximate number of seconds, plus or minus 3, since the last wait event began".

            Your STATE is 'waited known time' - so you are currently not WAITING. This means your LAST wait was the direct path read temp, it happened roughly 916 seconds ago (seconds in wait is in seconds, and is updated roughly every 3 seconds), and you wait for approximately 1 centi-second (wait time is in hundredths of second).

            This means the session has (probably) been hammering the CPU for the last 916 seconds.

            Jonathan Lewis

            "The greatest enemy of knowledge is not ignorance,
            it is the illusion of knowledge." (Stephen Hawking)
            • 3. Re: V$session_wait.seconds_in_wait

              "SECONDS_IN_WAIT" is the wait time of either the current wait or the last wait, which is to be interpreted based on STATE. (Jonathan has also explain this).

              File number 4523 might make sense if DB_FILES is high. Since this is a 'direct path read' wait it could be a temp segment read from a temporary tablespace, where File#s reported in waits are always DB_FILES + 1 and higher.
              • 4. Re: V$session_wait.seconds_in_wait

                Crystal Clear explantion!! Thx.


                My apologies for not including release.. It was 10.2. Thanks anyways for your reply.