Re: Question regarding rollback segment

wdh_at_epsilon.com (William D. Hankard) wrote:
>Hello,
>
> We are running Oracle 7.1.6 on an HP 9000 T500 running
> HP-UX 9.04
>
> Here is the problem we are having; our rollback table
> space is 3.0Gbytes in size.
>
> Our initial extent is 5Mbytes
> Our next extent is 5Mbytes also.
> Our OPTIMAL parameter is set to 220Mbytes.
> Our MINEXTENTS is 2
> Our MAXEXTENTS is 505
> Our Pro*C program commits after every 100 updates
> A row size is less than 200Bytes
>
> After about 100000 updates which is a 1000 commits, I get the 1555
> Oracle error:
>
> 01555, 00000, "snapshot too old (rollback segment too small)"
>
>
> We have tried the following to fix the problem:
> a. commit after every row
> b. commit after every 50 rows
> c. rollback segment in a non-RAID device
> d. 20Mbyte extents.
> e. restarted Oracle (reboot etc)
> f. recreated rollback tablespace
>
> None of these things have helped. Any suggestions or insight into this would
> be of great help. Thanks in advance.
>
>

How many rollback segments do you have in this tablespace? The OPTIMAL size combined with a large number of rollback segments could take up your space rather quickly.

Are you sure your process is using the rollback segment in your tablespace? You can guarantee this by using the "SET TRANSACTION USE ROLLBACK SEGMENT XXX" _before_ any DML statements and _immediately_ after every commit.

If you have a SELECT statement open on a table that is getting updated by other users/processes, and you commit while leaving the SELECT open, you can generate this error. It's even worse if _you_ are the one updating the table in question. The commit will free up the extent in the rollback segment and probably get used again once you have gotten to the end of your OPTIMAL size, thus wiping out the information needed for a consistent read. Do you see your rollback segment getting larger than the optimal size when your process is running?

Here's a couple of possible solutions:

1. Brute Force Method:
2. Create a rollback segment in a large tablespace with the largest OPTIMAL size you can possibly make it.
3. Use the SET TRANSACTION USE ROLLBACK SEGMENT command in your Pro*C program to explicitly use this segment before you change any data and immediately after any commit.
4. Repeated Pass Method:

    X = number of rows to fetch before doing a commit.

1. Set up a SELECT statement that selects rows based on the primary key of the table and orders them by the primary key. The WHERE clause would have something like WHERE key > :lastkey.
2. Set the lastkey variable to an arbitrarily low value before opening the SELECT cursor for the first time so that you get the first record.
3. After fetching and updating X number of rows, save the key of the last row you updated in lastkey and close the SELECT cursor.
4. Re-open the cursor using lastkey to get the next set of records to update.
5. Repeat C and D until you've processed all the rows in the table.

Both of these solutions are kludgy. The second method will work when you have limited disk resources and can't change the rollback segment size easily. You can tune performance by changing X to process the largest number of rows possible before doing a commit. The first method may or may not work as I've never tried it.

-- 
-----------------------------------------------------------------------------
Tom Remisoski                                (remisosk_at_forklift.mi04.zds.com)
Database Analyst                                (T.Remisoski_at_zds.com)
Zenith Data Systems