| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Rollback Problem (HELP!)
We recently had a case where a long-running report program(over 2.5 hours) was aborted with an ORA-1555 error. This error indicates that rollback management aged out the before images needed to create a consistent query.
Oracle uses the before images to re-create the rows that were modified after the query started. That way, every row retrieved is a snapshot as of the starting time of the query.
The before images age out only when new transactions over-write them. Oracle will not overwrite any before image while it is in use by a transaction. When the transaction is committed (or rolled back), the image blocks are free for use by a new transaction. The blocks are reused FIFO, so some time will pass before they are aged out.
The time it takes to age out is inversely proportional to the system insert, update, delete activity. The more activity, the less time the before images are retained. Activity is not constant. The following table was computed from the V$LOG_HISTORY
Expanded Expanded
rollback (5X) rollback (25X)
Percent of Long query long query long query
the month maximum time maximum time maximum time
10% 864 minutes (or more) 4320 minutes (or more) 21600 minutes
(or more)
205 450 minutes (or more 2250 minutes (or more) 11250 minutes
(or more)
30% 285 minutes (or more) 1425 minutes (or more) 7125 minutes
(or more)
40% 176 minutes (or more) 800 minutes (or more) 4400 minutes
(or more)
50% 48 minutes (or more) 240 minutes (or more) 1200 minutes
(or more)
60% 29 minutes (or more) 145 minutes (or more) 725 minutes
(or more)
70% 16 minutes (or more) 80 minutes (or more) 400 minutes
(or more)
80% 10 minutes (or more) 50 minutes (or more) 250 minutes
(or more)
90% 7 minutes (or more) 35 minutes (or more) 175 minutes
(or more)
95% 6 minutes (or more) 30 minutes (or more) 150 minutes
(or more)
99% 4 minutes (or more) 20 minutes (or more) 100 minutes
(or more)
This is based on 10 rollback segments of 20MB each, a total of 200MB of the available 1500MB. This allows a single rollback segment to expand to 1320MB for a large transaction and then shrink back to 20MB. If we expanded each segment to 100MB, the largest possible transaction would be reduced to 600MB. The table shows the increased maximum query time.
This long-running query must currently be run in the least active 40% of the time to be sure the rollback segments can accommodate it. Increasing the rollback segments by a factor of 5 only expands the window for this long-running query from 40% to 50%. In fact, expanding the rollback segments by a factor of 25 (to 500MB each) only gives us a 95% window for a 2.5 hour report.
This reveals a truth about Oracle: Long-running queries can not be run during periods of heavy updates on the same tables.
We must either:
expand the rollback tablespace from 1500MB to 6000MB and each rollback
segment therein to 500MB,
re-schedule the long-running queries,
re-schedule the periods of heavy update
or fix the report program to run much quicker.
Joseph Sumalbag wrote:
>
> Snapshot too Old is a typical problem .... Using a larger
> rollback segment only delays the appearance of the error ( and
> hope your transaction finishes before the error occurs ) ...
> Kurt Tucker wrote:
> >
> > I'm using OWGS 7.3.3.0 for NT and recently we have found the need for both
> > Large and small RBS's (We used to only need very small) I have tried to set
> > transaction to use the newly created LARGE_RBS but at some point The query
> > bounces out to the older Small RBS's. I feel that the part of the query
> > that calls function FOXUTL_PACK.DTFTMIN & DTFTMAX may be causing this.
> > Below is an explanation of the utiliy and below that is the actual query.
> > Of course the real problem is (Snapshot too old! ) :-( Any help with this
> > newby question would be greatly appreciated. Thanx...
> >
Received on Tue Jan 06 1998 - 00:00:00 CST
 |
 |