Re: Understanding Rollback Segment Sizes

Hi Steve, I think others have correctly identified better alternative choices, so I'm going to take a shot at answering the original questions.

1. The second datafile being at 500m instead ot 1000m is probably an error - assuming you are using some type of reverse engineering tool. Most likely the files were all 500m at some point and 3 out of 4 have been manually extended to 1000m. They could be all the same size or you could include the space in one datafile in a locally managed tablespace (LMT). The four data files could have orignally spread the IO load across multiple disks, were needed because of an OS max file size limitation, or made use of multiple freelists for the tablespace. Since you are creating the four files in the same file system, they are not spreading the IO load and LMT avoids freelist issues and your OS probaly doesn't have a 2G max file size limit anymore.

2&3) You are correct that with the current settings the rollback segment will only reach 500m each or 2000m total. Remember when you are looking at the usage you are only seeing one moment in time. The optimial setting means even if the rollback segments had consumed 58% of the space a couple of hours ago, after skrinking back to the "optimal" size, only 30% is being used now. Not having enough space causes query and process failures. Unless you are running out of storage, you are probably better off not to reducing the available RBS resources. If you have the tablespace size equal to the _current_ total maximum rollback size, then changing any rollback setting or adding an additional rollback segment requires you remembering to adjust the tablespace. If you don't, then on some later day... problems.

Changing the RBS setup, whether moving from manual RBS to automatic UNDO or reducing storage resources, introduces an "untested" configuration change. If you don't feel you can move to UNDO at this point in your project, you probably shouldn't be considering reducing the RBS tablespace size either.

Regards,
Margaret Received on Thu Jan 18 2007 - 09:48:23 CST