Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    2

    Question Unanswered: Tuning db file scattered read

    hi : all

    Yesterday i got a problem about db file scattered read.

    I found Oracle read a block using about 4000seconds!!!!!

    First I using
    select * from v$session_wait where sid = 181
    SID SEQ# EVENT
    --------- --------- ----------------------------------------------------------------
    P1TEXT P1 P1RAW
    ---------------------------------------------------------------- --------- --------
    P2TEXT P2 P2RAW
    ---------------------------------------------------------------- --------- --------
    P3TEXT P3 P3RAW WAIT_TIME
    ---------------------------------------------------------------- --------- -------- ---------
    SECONDS_IN_WAIT STATE
    --------------- -------------------
    181 53954 db file scattered read
    file# 9 00000009
    block# 35490 00008AA2
    blocks 6 00000006 -1
    4078 WAITED SHORT TIME

    for some times , and i found there is no change on p1/p2/p3 the only changed values is in seconds_in_wait field.

    Then I check OS using command vmstat
    Also found there is no IO.
    r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id
    0 0 15 556416 91976 0 48 12 0 0 0 0 0 0 1 1 538 603 134 30 1 69
    0 0 15 557176 92552 0 39 0 0 0 0 0 0 0 0 0 525 234 95 28 1 71
    0 0 15 557176 92552 0 39 0 0 0 0 0 0 0 0 0 521 333 120 30 1 69
    0 0 15 557176 92544 0 39 1 0 0 0 0 0 0 0 0 521 223 86 28 0 72

    Then I check v$sess_io
    I found ONLY CONSISTENT_GETS was changed.

  2. #2
    Join Date
    Jul 2002
    Posts
    2
    so I think it maybe read data from rollback segment for CONSISTENT.
    but I get a puzzle for why this wait is so long for about 4000seconds.

    by the way. in the past when we are runinng the pl/sql it got "snapshot too old" 's errror


    I really got puzzle. why?and how can i resolve it

    Thanks for All

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    2
    Try posting the Utlbstat report/ Statspack report so that the exact nature of the problem can be found out. Preferably with a report that was taken when things were runing fine and a report that you can take right now( with the waits). You could upload the files and I shall get back...

    K.Diwakar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •