Re: undo header/block

"Daud" <daud11_at_hotmail.com> wrote in message news:f0bf3cc3.0210260446.639c7830_at_posting.google.com...
> I have been monitoring v$waitstat in my production database and found
> that the count for undo header and block are increasing daily by about
> 4 and 20 respectively. What's causing this?
>
> Here is my rollback config:
>
> 15 of them in a LMT tablespace with uniform extent size of 0.5Mb.
> Each rollback has minextents of 6 and each extent is 0.5Mb. Optimal is
> not set!
> I don't see many concurrent transactions in the database. Max # of
> concurrent transactions so far is 10. They are, most of time, very
> small transactions.
>
> I believe I have seen a post from HJR somewhere in this newsgroup
> where he said he normally creates rollback segments with extent size
> of 8Mb or bigger.

Only in the sense that I tended not to find it worth the bother of creating lots of small segments, as most of the doco. tells you to do: lots of big segments works just as well.

The real point is that (in an OLTP environment) you need lots of them, and their precise size is less of a worry than the contention issue you get when there aren't enough of them.

Anyway: I confess these days to letting Oracle do its stuff with automatic undo (9i). Then you really do stop worrying about this sort of issue.

Undo header waits usually indicates a lack of segments. I'm just wondering how you determined that maximum number of concurrent transactions to be 10? Perhaps you need more segments. Is the rollback tablespace on a separate disk of its own? Perhaps there's I/O contention. It's a bit tricky to tell much more from the few statistics provided.

Regards
HJR
> That makes me wonder whether I have got the extent size wrong.
>
> Any advice anyone?
>
> rgds
> Daud
Received on Sat Oct 26 2002 - 14:31:43 CDT