Waits

[ocp8]

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..

[ggnanaraj]

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.

[DaPi]

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.

[ggnanaraj]

BTW, what version of db are you running?

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

Happy tuning!

[adewri]

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.

[ggnanaraj]

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

[adewri]

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.

[marist89]

I think there's a little difference between "small overhead" and "drag".

[ggnanaraj]

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.

[ggnanaraj]

Yet another one for data blocks...

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