Snapshot too old !! But I still got lots of space in rollback segment !!

[Chris Fung]

Hi there,

I just encounter the snpahsot too old error in our daily batch job. Since it runs quit smoothly for more than a year and I don't expect I will see such an "snapshot too old and rollback segment too small" error.

I am just wondering if the error is produced by user selecting the database while the database is doing its update/insert/delete ? Could it be one of the reason.

The database is a data warehouse running Oracle 8.1.7

Your advice is much appreciated.

Attached pls find the error log and some of the sql statement I ran against the rollback segment.

Cheers,

Chris,

[Indira Aramandla · Accepted solution]

Hi Chris,

Yes your assumtion is correct. It could be when the user is selecting the database while some datablocks are being changed. The above error is a result of Oracle trying to attain a 'read consistent' image

Reasons:
Whenever Oracle changes a database block (index, table, cluster) it stores a pointer in the header of the data block which identifies the rollback segment used to hold the rollback information for the changes made by the transaction. (This is required if the user later elects to not commit the changes and wishes to 'undo' the changes made.)

Upon commit, the database simply marks the relevant rollback segment header entry as committed. Now, when one of the changed blocks is revisited Oracle examines the header of the data block which indicates that it has been changed at some point. The database needs to confirm whether the change has been committed or whether it is currently uncommitted. To do this, Oracle determines the rollback segment used for the previous transaction (from the block's header) and then determines whether the rollback header indicates whether it has been committed or not.

The fundamental causes of the error ORA-01555 that are a result of Oracle trying to attain a 'read consistent' image.

These are :

1. The rollback information itself is overwritten so that Oracle is unable to rollback he (committed) transaction entries to attain a sufficiently old enough version of the block.
2. The transaction slot in the rollback segment's transaction table (stored in the rollback segment's header) is overwritten, and Oracle cannot rollback the transaction header sufficiently to derive the original rollback segment transaction slot.

To avoide this kind of errors, you could

o Increase the rollback segment size;
o Add more rollback segments;
o Commit less frequently.
o Assign a particular rbs to a particular transaction/operation. (judicious use of SET TRANSACTION USE ROLLBACK SEGMENT.)
o Decreasing LOG_CHECKPOINT_INTERVAL in init.ora
o Don't use the OPTIMAL storage parameter in the rollback segment;
o If possible, prevent updates from occurring simultaneously
o Write shorter well-tuned, parallel queries/tune the database;
o Avoid fetch across commit.


I hope this helps.

Indira A

[malay boy]

Indra,
Just to comment on your solution NOT
o Commit less frequently.
but
o Commit more frequently.

as you do commit the user process will be not reading from undo segment anymore.

regards
mB

[Indira Aramandla]

Hi Malay Boy,

Reducing the number of commits will reduce the likelihood of overwriting rollback information that is needed. If you have large rollback segments committing less often will REDUCE the probability of getting 'snapshot too old' error.

By Delaying commits. As long as an update is not committed, the rollback segment slot containing the old value is not released and therefore cannot be overwritten. This will work in VLDB if the application can enforce delayed commits and the number of updates is relatively low (or else you might run out of rollback space).

Indira A

[Hein van den Heuvel]

Chris,

My compliments for a well documented problem description. Refreshing. Thanks!
A good few IOs to that RBS file huh? 30 billion!


Indira, thanks for the extensive reply.
It sums it up nicely.

One potential cause is exceedng max-extend. But that is quite I seem to recall that if the system had hit max-extent in RBS3, then that would have been an explicit message to that 'extent' (sic) right? Furthermore, 500*20M is larger then the RBS file.

What I am a little uncomfortable about in your setup is the large pre-allocated on all segments, well above avg use.
You pre-allocate 10*20M = 200M/segment.
Times 16 segments = 3200M. Ok, since you have 9000M that does not seem to be a problem, but none-the-less you might reduce that some. Say to 4 initial segments and have more general free space to play with.

Now it is tempting to set a private, very large, RBS segment for the session running that long select, but unfortunately the problem is (best I understand) with the other transactions. Those modifying the DB, which are not holding enough RBS long enough.
For the entire duration of that select (how long is that?) any and all changes by other transactions need to be remembered for tx-consistent read support for the long query.

Sorry, No anwers, just thinking out aloud.

Hope it helps some,
Regards,
Hein.

[Yogeeraj_1]

hi,
see Note:40689.1, ORA-01555 "Snapshot too old" - Detailed Explanation

This article discusses the reasons behind the error ORA-01555 "Snapshot too old", has provided a list of possible methods to avoid the error when it is encountered, and also provides simple PL/SQL scripts that illustrate the cases discussed.

see also: Note:62005.1, "Creating, Optimizing, and Understanding Rollback Segments"


You want lots of extents in a RBS generally. Myself like to start with about 15 or so (depending on the number of concurrent transactions) rollback segments -- each with initial=next=1MB and minextents=25, maxextents=100. So I have 15 to 25 25MB rollback segments that can grow to 100MB each. I'll shrink them before backups back down to 25MB.

Again you should use LMT for rollback segments.

You need to determine the longest running query you have.

You need to determine the length of the longest running transaction you do.

You need to ensure you have sufficient rollback permanently (not that it can GROW but rather that it is permanently allocated) so that you do not wrap in the period of time that is larger then the above two numbers.

Most people size for their insert/update/delete transactions. They forget about sizing for the read transactions. You can monitor the v$ tables to see when and how fast your RBS wrap. You need to determine how much rollback you generate in a period of time. Say you generate X blocks of undo in 1 minutes. You have a long running query that takes 15 minutes. You need to have more then 16 * X blocks of undo configured.

In 9i, this is simplified as you may use an UNDO tablespace instead of rollback segments. Here you configure a retention period (how long do you want to keep undo) and it sizes itself.

hope this helps too!
regards
Yogeeraj

[Sanjay Kumar Suri]

The extent size of 20 MB seems to be too small for a data warehouse kind of application. I suggest at aleast a 2 to 5 times of this value.

Can you also list wraps in the following SQL:

select substr(a.name,1,8) Name, b.writes, b.gets, b.waits, b.optsize, b.hwmsize, b.shrinks, b.extends, b.aveshrink, b.aveactive from v$rollname a, v$rollstat b where a.usn=b.usn;

It will give an idea on how often transactions have wrapped from one extent to another since instance startup.

sks

[Volker Borowski]

Hi all,

I like to throw in some thoughts about this COMMIT discussion. Actually there are two aspects.

First you have the transaction that gets the 1555. This is usually a secelct or an update which is implemented with cursors. The active SCN from BEGIN TRANSACTION defines which is the consistent state that the cursor-result needs to deliver. So if can read data in several packs, it makes sense to commit these sections, even if you only do SELECTS (!) because this advances the relevant SCN for your own transaction to get consistent data and eliminates the need to read older data. But keep in mind, that you might read diffrent data !
Example:

begin transaction (SCN 100)
SELECT ...one
commit;
begin transaction (SCN 200)
SELECT ...two

With the commit in between, "SELECT TWO" will have no need to go for ROLLBACK between SCN 100 and 200, and reduces the chance that this rollback info is overwritten. Therefore, commiting more often on the side that gets probably the 1555 reduces the chance to get the error. Again: be aware, that the commit may change your result of SELECT TWO, allthough you did no data-modification, but may be someone else did between SCN 100 and 200.

The second aspect is the modifing side that runs concurrently. As Indira pointed out, committing less often on this transaction keeps the rollback info longer alive and therfore reduces the chance for the error as well, because a possible request has a better chance to be resolved.

So it depends on which side you can modify.
Volker

[Chris Fung]

Hi All,

Thanks for your input. Luckily I didn't get the problem this morning otherwise I have spent another whole day to rerun the batch and explian the situation to the user and boss.

SKS, attached is the query results for the rollback segment status of this morning. Please take a look and give comments.

Right now, I am thinking writing a script to keep monitoring the rollback segment usage and what sql statement is running against them.

I would like to query the v$session, v$sql_area to get the results and the script will be running in 1 minute interval for the morning period.

Any comments ??

Cheers,

Chris,

[Chris Fung]

Hi SKS,

Just forgot the attachement.

Cheers,

Chris,