-
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..
-
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.
-
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
-
BTW, what version of db are you running?
Check this doc...
http://technet.oracle.com/docs/produ...0_io.htm#15999
Happy tuning!
-
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."
-
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
-
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."
-
I think there's a little difference between "small overhead" and "drag".
Jeff Hunter
-
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.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|