Re: How much undo is too much?

rjqjunk_at_vt.edu wrote:
> I've read in this forum that you can't just go around allocating
> 'oodles' of undo space because the management of said space fills up
> the buffer. So how do you tell when you've reached the 'oodles' stage
> and you should stop allocating?
>
> I'm trying to balance the overall performance of the db with the needs
> of a couple of jobs that are periodically run.
>

The text-book answer is to create a very large undo tablespace and let your application do its stuff. Then query v$undostat, which will tell you how much undo was generated every 10 minutes. From that, you find the maximum undo generation rate per second.

You then work out how much undo retention you want. Multiply the rate per second by how many seconds of undo your application or safety needs warrant, add a bit for fudge factor, and Bob's your uncle.

If you've got a peaky workload, size for the peak.

Regards
HJR Received on Fri May 14 2004 - 09:32:13 CDT