Re: Help: Rollbacks Keep Blowing Up-v7.1.4

From: Steve Butler <sbut-is_at_seatimes.com>
Date: Fri, 3 Feb 1995 15:07:00 -0800
Message-ID: <Pine.SUN.3.91.950203145449.245B-100000_at_seatimes>

On 2 Feb 1995, Bill Hungerford wrote:

> Multiple SQL scripts running on system are blowing up due to rollback
> segments ("snapshot too old").
>
> Also, it appears that when these jobs run, the rollback segments aren't
> going into extents.
>

It sounds like there are several problems combining:

1. Must have one or more HUGE updates that need to be fixed. Try to commit in smaller increments. This will reduce the need for such big rollback segments.
2. The long running SELECTs are having to paw through the rollback segments to get 'old' data (data current as of the start of the select). However, there has been so much updated and committed that the old stuff has already been archived (you are archiving?) and no longer available.
3. A rollback segment does not release extra extents until it has cycled around and two things happen:
4. It no longer needs anything in the extents above the optimal
5. It has filled out the optimal extents and is looking to allocate another extent. (It then sees it can free some up.) This means that the 4 rollback segments could have all the space in the rollback table tied up (or more probably, 3 segments have the table tied up and the 4th needs to expand). You may need to have a larger rollback tablespace (or see #1 above).

Now, I'm not running v7.1 but am back on 7.0.16. I'm presuming the same things are happening in the later versions ('cause I've been caught with the same problem on 7.0.16).

Hope this helps.

--Steve

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-464-2905         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |

| Seattle, WA 98111 Packet: Not currently active |

+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Sat Feb 04 1995 - 00:07:00 CET