Re: ORA-01555 help needed

From: Steve Adams <steveadams_at_acslink.net.au>
Date: 1997/06/09
Message-ID: <339c3a99.4206815@nntp.peg.apc.org>#1/1

Hello,

I suspect that this is not a 'fetch across commits' problem as such. Your main procedure has a cursor open with a 'read consistent SCN' of S. Each time you read a block for this cursor, it needs to be made 'read consistent'. That is, any changes committed after S must be rolled back. Blocks read from disk may contain information in their header about the last transaction to have modified that block. If that transaction committed long ago, the query process must work out whether it committed before or after the query began. To do so, the transaction table in the rollback segment header block must be consulted. But that block is itself subject to frequent change, and it may be that the transaction slot in which relevant transaction was recorded has long since been reused. If so, it is enough to roll back the rollback segment header block to SCN S, to prove that the transaction in question must have committed before S, and therefore the block can be used as is. ORA-1555 however will occur if it is not possible to roll the transaction table header block back far enough. This requires there to have been more transactions in the rollback segment since the start of the query than there are slots available in the transaction table in the rollback segment header, and enough database activity to have caused at least one extent in the rollback segment to have been reused during that time. These two factors together are sufficient to make it impossible to do a consistent read on the segment header with respect to SCN S.

Your options are:
1. Reduce the rate at which rollback segment header transaction table slots are reused.

1a. Commit less frequently (say every 1000 inserts instead).
1b. Add more rollback segments.
1c. Rebuild database with a larger block size.

2. Increase the time taken before rollback segment extents are reused. 2a. Make the rollback segments larger.
2b. Leave a dummy transaction uncommitted in each rollback segment, from before the start of the query. This will cause all rollback segments to grow without bound for the duration of the query. 3. Reduce the time for which the cursor is held open. 3a. If possible, reopen the cursor say after each 10,000 rows. 3b. Improve the performance of the query, or entire instance. 4. Remove the need to compare SCNs by forcing cleanouts on the blocks needed for the query, before the query runs. 4a. You can 'select /* +noparallel */ * from tablename;' as long as the new 7.3 parameter delay_logging_block_cleanouts is FALSE.

HTH, Steve Adams

---

On Wed, 04 Jun 1997 08:37:54 GMT, saqib.zulfiqar_at_cressoft.com.pk (Saqib Zulfiqar) wrote:

>Hi All,
>I have written procedure which reads data from one table and inserts
>it in another table. When a row fails insertion, another procedure is
>called which inserts the rejected row in the rejected_rows table and
>then commits the transaction. Since the main procedure inserts 50 to
>60 thousand rows, I therefore commit after every 500 inserts.
>Some of the times when I run the process I get the error message.
>
>ORA-01555: snapshot too old: rollback segment number 5 with name "R04"
>to small.
>
>I have studied the messages manual. It says that either your rollback
>segments are too small or you are using too many inserts plus rows are
>being fetched after a commit in the database.
>My rollback segment size is 30Mb, and since I am committing after 500
>rows, the rollback segment hardly grows at all. The other things is
>that I have to commit after a certain no. of rows, so that I may know
>that the procedure is running smoothly.
>Now the problem is that this error occurs sometimes, not most of the
>times, that means when the server is on heavy load, then the
>possibility of receiving this error is maximum.
>I have studied a bit about fetch across commits, but need a little bit
>more elucidation. Moreover if anybody has any ideas to handle this
>position and avoid this error, then please do respond !.
>
>Thanks,
>////////////////////////////////////////////
>// //
>// Saqib Zulfiqar //
>// Software Engineer //
>// CresSoft Pvt Ltd. //
>// Lahore, Pakistan. //
>//E-Mail :saqib.zulfiqar_at_cressoft.com.pk //
>// //
>////////////////////////////////////////////
>
>I get melancholic sometimes, but I've learned to enjoy it
>
Received on Mon Jun 09 1997 - 00:00:00 CDT