Waits
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Waits

  1. #1
    Join Date
    Jun 2001
    Posts
    19

    Angry Waits

    Hello..

    I am posting the result of selecting from v$waitstat here:

    SQL> select * from v$waitstat;

    CLASS COUNT TIME
    ------------------ ---------- ----------
    data block 1086905 3391010
    sort block 0 0
    save undo block 0 0
    segment header 479 120669
    save undo header 0 0
    free list 2 118
    system undo header 0 0
    system undo block 0 0
    undo header 715 3514
    undo block 352 4600

    My question is how come I am experiencing so many waits for undo header/block?? I had 50 2 meg rollback segments and added 10 more yesterday and the count for header waits has increased by almost 100!! I am not getting any errors, it's just that my users constantly complain about slow response time and I am trying to do something about it. This is a 67 gig OLTP/Batch database that does mainly OLTP during the day and batch at night. I do have a lot of chained rows in some tables due to the use of long datatype. I just do not understand why my undo waits keep going up??? Any advice would be greatly appreciated.

    Thank you..

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    I'm surprised that you seem to overlook the wait on the data blocks.

    BTW, you have to turn off timed_statistics as this can be a drag on your OLTP performance.

    How many days has your database been up?

    IMHO, I thing you have a lot of performance tuning to be done on your db.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by ggnanaraj
    BTW, you have to turn off timed_statistics as this can be a drag on your OLTP performance.
    If you scratch around, you will find that general opinion is that this is NOT true.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    BTW, what version of db are you running?

    Check this doc...
    http://technet.oracle.com/docs/produ...0_io.htm#15999

    Happy tuning!

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Dapi is right about TIMED_STATISTICS=TRUE

    Its performance impact on a database is negligible on a database in general (there is an issue in Oracle 8.1.5 where by shared SQL might be defeated if TIMED_STATISTICS is set to true).

    The performance overhead due to this is not measurable.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Originally posted by DaPi
    If you scratch around, you will find that general opinion is that this is NOT true.
    There is a small overhead incurred if this is set to TRUE but the
    benefit is more meaningful information when monitoring the database.
    http://metalink.oracle.com/metalink/...T&p_id=30824.1

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by ggnanaraj
    [There is a small overhead incurred if this is set to TRUE but the
    benefit is more meaningful information when monitoring the database.
    This small overhead is negligible, there are other performance issues to be more bothered about.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think there's a little difference between "small overhead" and "drag".
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: Waits

    Originally posted by ocp8
    Any advice would be greatly appreciated.
    Check this out for ...Reducing Waits / Wait times...
    http://metalink.oracle.com/metalink/...T&p_id=34405.1

    HTH.

  10. #10
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Yet another one for data blocks...

    http://metalink.oracle.com/metalink/..._id=323556.995

Posting Permissions

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



Click Here to Expand Forum to Full Width