Re: Oracle RBS

nsouto_at_optushome.com.au.nospam (Nuno Souto) wrote in message news:<3b82384f.5252792_at_news>...
> On Tue, 21 Aug 2001 12:06:15 +0200, Jan Israelsson
> <janisraelsson_at_hotmail.com> wrote:
>
> >having problems with ORA 1555 "snapshot to old, rollback segment XX to
>
> Loose the "optimal":
> >min_extents 2, max_extents unlimited, optimal 4096K, pct_increase 0
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam

There are several indirect causes to the 1555 error which basically means the data you want to read is no longer available. Since your segments are may be as small as 4M it doesn't take very much activity to cause change data to be overlaid. You can increase the chances of the change data being retained by increasing the size of the segments.  Try minextents = 10 so that each segment is at least 20M before it overlays any data ( which means you will be using 200M of rbs at all times, 10 segs X 20M ea). You will need to set the optimal up to match and maxextents should be at least twice the min extents, if not larger. Maxextents must allow a segment to grow large enough to hold the largest transaction you want to allow as a normal operation.

Jobs that read and update the same table with commits being issued during the task are very likely to run into 'snapshot' errors. You can attempt to decrease the frequency of commit, but generally the best fix is to read the table in one pass finding the rows to be updated and store the rowid and keys in a driving memory structure or table. The read the saved keys and update the target table in a second pass. The execution time of this two step method is usually faster than the one step job due the reduction of the need to read rbs segments to reconstruct data caused by updateing the same blocks multiple times encounted with the first method. This assumes that elimination of the commit is not practical because the table is subject to heavy concurrent update, which is common in OLTP environments.

• Mark D Powell --