List, Please help.

We have an 8k block size OLTP 108G database with up to 140 users and
numerous batch processes that start to run in the evening. A few days ago,
a batch job failed with "snapshot too old: rollback segment number 2 with
name "R01" too small.". I looked at the rollback segments R01 to R09 and
they were all set as:

Initial extent size = 304k
Next extent size = 1256k
Optimal size = Null
Minimum # = 5
Value = 505

I couldn't understand why these particular extent sizes were chosen except
for the fact that they were multiples of the 8k block size. Throughout the
day I dropped the segments and created them larger. The batch job again
failed. I then upped the segments to what they are today and added segments
as per suggestions in the Oracle 24x7 manual concerning ORA-1555, so that we
now have R01 to R12:

Initial extent size = 3200k
Next extent size = 3200k
Optimal size = 64000k
Minimum # = 8
Value = 505

Last night the batch job failed again with the same error. I read all the
information about rollback segments that seemed to pertain to this error and
don't see why I need to be bumping up the segments so large. I'm not sure
what the 'optimal' size should be set to.

Does anyone have any advice as to what these rbs settings should be? We
dba's are starting to look bad with these darn batch job failures....

TIA,
Ben



This communication is intended for the use of the recipient to which it is
addressed, and may contain confidential, personal and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take action
relying on it. Any communication received in error, or subsequent reply,
should be deleted or destroyed.
Oracle LazyDBA home page