Re: question about automatic undo management

Richard,

I agree with your points about the why's and wherefore's of tuning the cache by tuning the load requirements. However, AFTER you've done all the PROPER things, I suspect that increasing the cache size would not help reduce the writes on the undo tablespace because the rate of writing now depends on the init.ora parameters affecting the incremental checkpoint.

In simple terms, the Oracle kernel now has directives like "never allow more than 1,000 buffers to be dirty". So it's the rate at which you dirty blocks that causes writes, not the fraction of the buffer that is dirty that causes writes.

Of course, the above is a generalisation so I will try to cover all points by noting also that you can set the parameters to allow the entire buffer to be dirty - in which case a logswitch checkpoint may be a bit vicious. Similarly you could have a cache which is currently so small that the search for free buffers forces writes that would otherwise not need to have been written by the incremental checkpoint.

OCR ca. 55%

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Richard Foote wrote in message ...


>

>I/O activity in relation to the database buffer cache has to depend

on


>the buffer cache size to some degree. Much has been made of the poor

>buffer cache hit rate recently but if the cache is too small to cope

>with the current demand, then we have an issue. Of course, reducing

the


>current demand is the *key* to tuning the buffer cache and sizing

>rollback/undo segments appropriately is potentially of importance in

>reducing this demand. However, once the undo segments have

>been sized as efficiently as possible (eg. no significant contention,

no


>significant growth/shrink rates, no significant numbers of unwanted

1555s)


>but they're still being aged and written out to disk, then yes, we

may need


>to look at increasing the buffer cache if practical.

>




>Richard

>

>