Re: Is using OPTIMAL in RBS ORA-1555 prone?

slootsky_at_erols.com (Victor Slootsky) wrote in message news:<b0748262.0202191059.1a078578_at_posting.google.com>...
> Don't use "optimal" if you run big reports and many small transactions
> simultaniously. Or you may consider running big reports during night
> shift when the transaction activity is low.

We run just such an environment where we average more than 16 transactions per second over a 24 hour day per statspack. We have a mix of very long batch and large transactions taking place and until business turned down we were running 24 X 7 so there are no quiet periods where manually shrinking can be done without risk of freeing blocks that contain recently changed data. We use optimal and can count the number of snapshot too old errors we get per year on one hand. It it not the use of the optimal parameter that is the problem; it is the setting of the optimal value too small in relation to the average active rbs segment size that causes the problem. If you set optimal to at least 5 times to 10 times this number then you can greatly relieve snapshot too old problems. I also believe you should consider setting the extent size to at least the average active rbs segment size then allocate about 10 of these to each rbs segment to hold changed data for consistent readers. You can then set the optimal to extent size X min extents and the result seems to work.

Using this kind of arrangement we have obtained effectively no contention for rbs segment resources per statspack and the Oracle rbs tuning SQL (hit ratio), have not have a sinle unable to extend rbs segment error in years, and do not suffer from excessive snapshot too old. Because applications vary this plan may not be optimal for your application but it sure has worked well for us.

• Mark D Powell --