ORA-01555: snapshot too old: rollback segment number 4 with
ORA-01555: snapshot too old: rollback segment number 4 with
This is a topic for an orphaned message.
Thanks for the comments guys,
I had read the post before, but couldnt relate it to my scenario. The problem I have is that I dont understand how the error could be occurring. This is because it is happening on a test environment that has one user which is under my control. The offending job is one of a number that is sequenced to run daily as a stress test and to establish average load times. The errored job had run successfully the previous night with a few thousand committed inserts/updates. In between that and the next run, which errored, the DB was taken down for backup and brought back up. As I understand it this would have updated the data block with all commits, meaning that the select transaction would not require verification from the transaction table of the rollback segment.
So why is it that the error is occurring? Is my understanding correct?
I am aware that this is a complex area and I could have easily missed something, but I would very much appreciate any help.
Thanks,
Regu.
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: 20 August 2002 23:30
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for that Chris. You just saved me from another long reply.
Regu - if that link still does not answer your question, let me know. There is still one really obscure way to get ORA-01555 which is not covered by those posts, but it is extremely rare and not worth the effort of trying to explain if I dont have to. Yes, I know, simply by stating this, I will surely arouse someones interest. Sorry, I dont have a spare 20 minutes just at the moment.
-----Original Message-----
From: Chris Kearns [mailto:chrisk@zing.com.au]
Sent: Wednesday, 21 August 2002 8:10 AM
To: datastage-users@oliver.com
Subject: Re: ORA-01555: snapshot too old: rollback segment number 4
with name "R03" too small
Ive come accross the error before and was helped by the following post found
in www.tools4datastage.com:
http://www.tools4datastage.com/forum/to ... C_ID=81513
Chris.
www.altis.com.au
Quoting Regu.Saliah@astrazeneca.com:
> Fellow DS users.
>
> Im running DS 5.2 on AIX with Oracle 9i and Im getting the above
> Oracle error intermittently. Ive come across ORA-01555 it a previous
> life when
> Ive tried updating a table that Im also querying, but never when the
> only
> transaction happening on the offending table is the select.
>
> The DS job concerned is selecting data from the table into an hashed
> file which is then used as a lookup to update/insert into the same
> table. Because Im selecting into the Hashed file and the
> insert/update is looked
> up against the same hashed file, the update/insert should only happen
> when
> the select is finished. The log confirms this assumption, but I still
> occasionally get the above error while selecting.
>
> Has anyone come across an ORA-01555 when just selecting?
>
> Thanks,
> Regu.
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
I had read the post before, but couldnt relate it to my scenario. The problem I have is that I dont understand how the error could be occurring. This is because it is happening on a test environment that has one user which is under my control. The offending job is one of a number that is sequenced to run daily as a stress test and to establish average load times. The errored job had run successfully the previous night with a few thousand committed inserts/updates. In between that and the next run, which errored, the DB was taken down for backup and brought back up. As I understand it this would have updated the data block with all commits, meaning that the select transaction would not require verification from the transaction table of the rollback segment.
So why is it that the error is occurring? Is my understanding correct?
I am aware that this is a complex area and I could have easily missed something, but I would very much appreciate any help.
Thanks,
Regu.
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: 20 August 2002 23:30
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for that Chris. You just saved me from another long reply.
Regu - if that link still does not answer your question, let me know. There is still one really obscure way to get ORA-01555 which is not covered by those posts, but it is extremely rare and not worth the effort of trying to explain if I dont have to. Yes, I know, simply by stating this, I will surely arouse someones interest. Sorry, I dont have a spare 20 minutes just at the moment.
-----Original Message-----
From: Chris Kearns [mailto:chrisk@zing.com.au]
Sent: Wednesday, 21 August 2002 8:10 AM
To: datastage-users@oliver.com
Subject: Re: ORA-01555: snapshot too old: rollback segment number 4
with name "R03" too small
Ive come accross the error before and was helped by the following post found
in www.tools4datastage.com:
http://www.tools4datastage.com/forum/to ... C_ID=81513
Chris.
www.altis.com.au
Quoting Regu.Saliah@astrazeneca.com:
> Fellow DS users.
>
> Im running DS 5.2 on AIX with Oracle 9i and Im getting the above
> Oracle error intermittently. Ive come across ORA-01555 it a previous
> life when
> Ive tried updating a table that Im also querying, but never when the
> only
> transaction happening on the offending table is the select.
>
> The DS job concerned is selecting data from the table into an hashed
> file which is then used as a lookup to update/insert into the same
> table. Because Im selecting into the Hashed file and the
> insert/update is looked
> up against the same hashed file, the update/insert should only happen
> when
> the select is finished. The log confirms this assumption, but I still
> occasionally get the above error while selecting.
>
> Has anyone come across an ORA-01555 when just selecting?
>
> Thanks,
> Regu.
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in
error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
OK, here goes.
99.9% of the time, ORA-01555 implies that between the start of your generally long running query and the time of the error, another process updated and committed changes to the table you are reading (etc- all covered in other posts).
Obviously, the first thing to do is try every way possible to find if any of the tables in your input query could be subject to this. This may even mean examining views like v$sqlarea to see what other queries have been updating your tables. This would be especially useful so soon after you bring the database up as you would only find queries here that have executed since the restart of the database.
Leave all that with you. Assuming you can still prove that nothing else has updated the table, it still leaves one unfortunate possibility.
It goes like this. (Caveat - Im not an Oracle DBA, so this may not be precisely right, but it gives the general idea. If you want some really detailed explanations, you could go to www.ixora.com.au)
Some background about updates, commits and delayed block cleanout.
1. When you update a block in the database, it is first copied to the rollback segment, then the block is updated and the fact that it is uncommitted also written to the block. 2. When you commit the update, Oracles behaviour depends on how much was updated. If it is only small, the updated blocks are changed to reflect the new SCN (system change number) and the fact that they are now committed. If there are a lot of changes, the individual blocks are NOT updated. Oracle then relies on a concept called "Delayed Block Cleanout". 3. In this situation, Oracle uses subsequent queries to update the individual blocks when they are next read. The subsequent query will discover that the block is dirty and go to the rollback segment to find the clean copy of the block. If it finds it, it can then determine that the block has actually been committed and the SCN of the commit is now written back to the original block. If the change was committed before the query started, then this SCN will be prior to the SCN of the query. 4. If enough time has passed, it is unlikely to find the block it is looking for in the rollback segment, so it simply uses the lowest SCN in the rollback segment as an upper bound for the SCN of the commit. But, this is where the problem can arise as this SCN is later than the actual SCN of the committed change.
Here, then, is an example of how the error occurs.
1. You perform a large enough number of updates (and commit them) to table A so that Oracle will rely on delayed block cleanout. 2. You then start you long running query, which involves table A. 3. A large number of other updates occur in the database, to the extent that they completely use the rollback segment containing the changes for table A. The blocks in the RBS for table A are now lost (no big deal). 4. Your long running query eventually reaches a changed block in table A and goes to the rollback segment to check on the state of play. It does not find the block it wants, thereby concluding that the change was actually committed. As it cannot find the actual SCN of the change, it uses the lowest one that appears in that RBS. Problem is, this SCN happens to be later than the SCN of your query, leading to the incorrect assumption that the block has been updated since your query started, and leading to ORA-01555.
Happens very rarely, but I believe I have had one example in our update process that visits us from time to time. If you know enough about Oracle tracing, I think it is possible to prove if this is what is happening, but Im not going there (Im definitely not a DBA).
What can you do about it? If you can delay the unrelated updates which happen between the start of your query and the error; that would help. If you can change the processing so that your query runs faster, that might help too. In my own situation, this looks a likely possibility.
From your message, though, it seems that this might not even explain you problem. Still, it might give you some clues. I dont think the shutdown/restart is going to make a difference one way or the other. Do you do a shutdown immediate or a normal shutdown?
Maybe looking at what queries have executed since the database started might help (eg v$sqlarea and v$sqltext).
Good luck.
-----Original Message-----
From: Regu.Saliah@astrazeneca.com [mailto:Regu.Saliah@astrazeneca.com]
Sent: Wednesday, 21 August 2002 5:57 PM
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for the comments guys,
I had read the post before, but couldnt relate it to my scenario. The problem I have is that I dont understand how the error could be occurring. This is because it is happening on a test environment that has one user which is under my control. The offending job is one of a number that is sequenced to run daily as a stress test and to establish average load times. The errored job had run successfully the previous night with a few thousand committed inserts/updates. In between that and the next run, which errored, the DB was taken down for backup and brought back up. As I understand it this would have updated the data block with all commits, meaning that the select transaction would not require verification from the transaction table of the rollback segment.
So why is it that the error is occurring? Is my understanding correct?
I am aware that this is a complex area and I could have easily missed something, but I would very much appreciate any help.
Thanks,
Regu.
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: 20 August 2002 23:30
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for that Chris. You just saved me from another long reply.
Regu - if that link still does not answer your question, let me know. There is still one really obscure way to get ORA-01555 which is not covered by those posts, but it is extremely rare and not worth the effort of trying to explain if I dont have to. Yes, I know, simply by stating this, I will surely arouse someones interest. Sorry, I dont have a spare 20 minutes just at the moment.
-----Original Message-----
From: Chris Kearns [mailto:chrisk@zing.com.au]
Sent: Wednesday, 21 August 2002 8:10 AM
To: datastage-users@oliver.com
Subject: Re: ORA-01555: snapshot too old: rollback segment number 4
with name "R03" too small
Ive come accross the error before and was helped by the following post found in www.tools4datastage.com:
http://www.tools4datastage.com/forum/to ... C_ID=81513
Chris.
www.altis.com.au
Quoting Regu.Saliah@astrazeneca.com:
> Fellow DS users.
>
> Im running DS 5.2 on AIX with Oracle 9i and Im getting the above
> Oracle error intermittently. Ive come across ORA-01555 it a previous
> life when
> Ive tried updating a table that Im also querying, but never when the
> only
> transaction happening on the offending table is the select.
>
> The DS job concerned is selecting data from the table into an hashed
> file which is then used as a lookup to update/insert into the same
> table. Because Im selecting into the Hashed file and the
> insert/update is looked
> up against the same hashed file, the update/insert should only happen
> when
> the select is finished. The log confirms this assumption, but I still
> occasionally get the above error while selecting.
>
> Has anyone come across an ORA-01555 when just selecting?
>
> Thanks,
> Regu.
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
99.9% of the time, ORA-01555 implies that between the start of your generally long running query and the time of the error, another process updated and committed changes to the table you are reading (etc- all covered in other posts).
Obviously, the first thing to do is try every way possible to find if any of the tables in your input query could be subject to this. This may even mean examining views like v$sqlarea to see what other queries have been updating your tables. This would be especially useful so soon after you bring the database up as you would only find queries here that have executed since the restart of the database.
Leave all that with you. Assuming you can still prove that nothing else has updated the table, it still leaves one unfortunate possibility.
It goes like this. (Caveat - Im not an Oracle DBA, so this may not be precisely right, but it gives the general idea. If you want some really detailed explanations, you could go to www.ixora.com.au)
Some background about updates, commits and delayed block cleanout.
1. When you update a block in the database, it is first copied to the rollback segment, then the block is updated and the fact that it is uncommitted also written to the block. 2. When you commit the update, Oracles behaviour depends on how much was updated. If it is only small, the updated blocks are changed to reflect the new SCN (system change number) and the fact that they are now committed. If there are a lot of changes, the individual blocks are NOT updated. Oracle then relies on a concept called "Delayed Block Cleanout". 3. In this situation, Oracle uses subsequent queries to update the individual blocks when they are next read. The subsequent query will discover that the block is dirty and go to the rollback segment to find the clean copy of the block. If it finds it, it can then determine that the block has actually been committed and the SCN of the commit is now written back to the original block. If the change was committed before the query started, then this SCN will be prior to the SCN of the query. 4. If enough time has passed, it is unlikely to find the block it is looking for in the rollback segment, so it simply uses the lowest SCN in the rollback segment as an upper bound for the SCN of the commit. But, this is where the problem can arise as this SCN is later than the actual SCN of the committed change.
Here, then, is an example of how the error occurs.
1. You perform a large enough number of updates (and commit them) to table A so that Oracle will rely on delayed block cleanout. 2. You then start you long running query, which involves table A. 3. A large number of other updates occur in the database, to the extent that they completely use the rollback segment containing the changes for table A. The blocks in the RBS for table A are now lost (no big deal). 4. Your long running query eventually reaches a changed block in table A and goes to the rollback segment to check on the state of play. It does not find the block it wants, thereby concluding that the change was actually committed. As it cannot find the actual SCN of the change, it uses the lowest one that appears in that RBS. Problem is, this SCN happens to be later than the SCN of your query, leading to the incorrect assumption that the block has been updated since your query started, and leading to ORA-01555.
Happens very rarely, but I believe I have had one example in our update process that visits us from time to time. If you know enough about Oracle tracing, I think it is possible to prove if this is what is happening, but Im not going there (Im definitely not a DBA).
What can you do about it? If you can delay the unrelated updates which happen between the start of your query and the error; that would help. If you can change the processing so that your query runs faster, that might help too. In my own situation, this looks a likely possibility.
From your message, though, it seems that this might not even explain you problem. Still, it might give you some clues. I dont think the shutdown/restart is going to make a difference one way or the other. Do you do a shutdown immediate or a normal shutdown?
Maybe looking at what queries have executed since the database started might help (eg v$sqlarea and v$sqltext).
Good luck.
-----Original Message-----
From: Regu.Saliah@astrazeneca.com [mailto:Regu.Saliah@astrazeneca.com]
Sent: Wednesday, 21 August 2002 5:57 PM
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for the comments guys,
I had read the post before, but couldnt relate it to my scenario. The problem I have is that I dont understand how the error could be occurring. This is because it is happening on a test environment that has one user which is under my control. The offending job is one of a number that is sequenced to run daily as a stress test and to establish average load times. The errored job had run successfully the previous night with a few thousand committed inserts/updates. In between that and the next run, which errored, the DB was taken down for backup and brought back up. As I understand it this would have updated the data block with all commits, meaning that the select transaction would not require verification from the transaction table of the rollback segment.
So why is it that the error is occurring? Is my understanding correct?
I am aware that this is a complex area and I could have easily missed something, but I would very much appreciate any help.
Thanks,
Regu.
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: 20 August 2002 23:30
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for that Chris. You just saved me from another long reply.
Regu - if that link still does not answer your question, let me know. There is still one really obscure way to get ORA-01555 which is not covered by those posts, but it is extremely rare and not worth the effort of trying to explain if I dont have to. Yes, I know, simply by stating this, I will surely arouse someones interest. Sorry, I dont have a spare 20 minutes just at the moment.
-----Original Message-----
From: Chris Kearns [mailto:chrisk@zing.com.au]
Sent: Wednesday, 21 August 2002 8:10 AM
To: datastage-users@oliver.com
Subject: Re: ORA-01555: snapshot too old: rollback segment number 4
with name "R03" too small
Ive come accross the error before and was helped by the following post found in www.tools4datastage.com:
http://www.tools4datastage.com/forum/to ... C_ID=81513
Chris.
www.altis.com.au
Quoting Regu.Saliah@astrazeneca.com:
> Fellow DS users.
>
> Im running DS 5.2 on AIX with Oracle 9i and Im getting the above
> Oracle error intermittently. Ive come across ORA-01555 it a previous
> life when
> Ive tried updating a table that Im also querying, but never when the
> only
> transaction happening on the offending table is the select.
>
> The DS job concerned is selecting data from the table into an hashed
> file which is then used as a lookup to update/insert into the same
> table. Because Im selecting into the Hashed file and the
> insert/update is looked
> up against the same hashed file, the update/insert should only happen
> when
> the select is finished. The log confirms this assumption, but I still
> occasionally get the above error while selecting.
>
> Has anyone come across an ORA-01555 when just selecting?
>
> Thanks,
> Regu.
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
Hi David,
Thanks v. much for that detailed explanation. I think Ill go back to the beginning and get our DBA to put a trace on the DB to prove that my query is the only thing running on that table.
The rare scenario that you have explained is the one that I was worried about, but I think that this would only be a possibility if I were selecting from a table very soon after it had been updated and committed. In my case there has been a number of checkpoints between the updates and the select that would have written the commits to the block, thereby ruling this out.
Thanks again for your time on this.
Cheers,
Regu.
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: 21 August 2002 12:55
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
OK, here goes.
99.9% of the time, ORA-01555 implies that between the start of your generally long running query and the time of the error, another process updated and committed changes to the table you are reading (etc- all covered in other posts).
Obviously, the first thing to do is try every way possible to find if any of the tables in your input query could be subject to this. This may even mean examining views like v$sqlarea to see what other queries have been updating your tables. This would be especially useful so soon after you bring the database up as you would only find queries here that have executed since the restart of the database.
Leave all that with you. Assuming you can still prove that nothing else has updated the table, it still leaves one unfortunate possibility.
It goes like this. (Caveat - Im not an Oracle DBA, so this may not be precisely right, but it gives the general idea. If you want some really detailed explanations, you could go to www.ixora.com.au)
Some background about updates, commits and delayed block cleanout.
1. When you update a block in the database, it is first copied to the rollback segment, then the block is updated and the fact that it is uncommitted also written to the block. 2. When you commit the update, Oracles behaviour depends on how much was updated. If it is only small, the updated blocks are changed to reflect the new SCN (system change number) and the fact that they are now committed. If there are a lot of changes, the individual blocks are NOT updated. Oracle then relies on a concept called "Delayed Block Cleanout". 3. In this situation, Oracle uses subsequent queries to update the individual blocks when they are next read. The subsequent query will discover that the block is dirty and go to the rollback segment to find the clean copy of the block. If it finds it, it can then determine that the block has actually been committed and the SCN of the commit is now written back to the original block. If the change was committed before the query started, then this SCN will be prior to the SCN of the query. 4. If enough time has passed, it is unlikely to find the block it is looking for in the rollback segment, so it simply uses the lowest SCN in the rollback segment as an upper bound for the SCN of the commit. But, this is where the problem can arise as this SCN is later than the actual SCN of the committed change.
Here, then, is an example of how the error occurs.
1. You perform a large enough number of updates (and commit them) to table A so that Oracle will rely on delayed block cleanout. 2. You then start you long running query, which involves table A. 3. A large number of other updates occur in the database, to the extent that they completely use the rollback segment containing the changes for table A. The blocks in the RBS for table A are now lost (no big deal). 4. Your long running query eventually reaches a changed block in table A and goes to the rollback segment to check on the state of play. It does not find the block it wants, thereby concluding that the change was actually committed. As it cannot find the actual SCN of the change, it uses the lowest one that appears in that RBS. Problem is, this SCN happens to be later than the SCN of your query, leading to the incorrect assumption that the block has been updated since your query started, and leading to ORA-01555.
Happens very rarely, but I believe I have had one example in our update process that visits us from time to time. If you know enough about Oracle tracing, I think it is possible to prove if this is what is happening, but Im not going there (Im definitely not a DBA).
What can you do about it? If you can delay the unrelated updates which happen between the start of your query and the error; that would help. If you can change the processing so that your query runs faster, that might help too. In my own situation, this looks a likely possibility.
From your message, though, it seems that this might not even explain you problem. Still, it might give you some clues. I dont think the shutdown/restart is going to make a difference one way or the other. Do you do a shutdown immediate or a normal shutdown?
Maybe looking at what queries have executed since the database started might help (eg v$sqlarea and v$sqltext).
Good luck.
-----Original Message-----
From: Regu.Saliah@astrazeneca.com [mailto:Regu.Saliah@astrazeneca.com]
Sent: Wednesday, 21 August 2002 5:57 PM
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for the comments guys,
I had read the post before, but couldnt relate it to my scenario. The problem I have is that I dont understand how the error could be occurring. This is because it is happening on a test environment that has one user which is under my control. The offending job is one of a number that is sequenced to run daily as a stress test and to establish average load times. The errored job had run successfully the previous night with a few thousand committed inserts/updates. In between that and the next run, which errored, the DB was taken down for backup and brought back up. As I understand it this would have updated the data block with all commits, meaning that the select transaction would not require verification from the transaction table of the rollback segment.
So why is it that the error is occurring? Is my understanding correct?
I am aware that this is a complex area and I could have easily missed something, but I would very much appreciate any help.
Thanks,
Regu.
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: 20 August 2002 23:30
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for that Chris. You just saved me from another long reply.
Regu - if that link still does not answer your question, let me know. There is still one really obscure way to get ORA-01555 which is not covered by those posts, but it is extremely rare and not worth the effort of trying to explain if I dont have to. Yes, I know, simply by stating this, I will surely arouse someones interest. Sorry, I dont have a spare 20 minutes just at the moment.
-----Original Message-----
From: Chris Kearns [mailto:chrisk@zing.com.au]
Sent: Wednesday, 21 August 2002 8:10 AM
To: datastage-users@oliver.com
Subject: Re: ORA-01555: snapshot too old: rollback segment number 4
with name "R03" too small
Ive come accross the error before and was helped by the following post found in www.tools4datastage.com:
http://www.tools4datastage.com/forum/to ... C_ID=81513
Chris.
www.altis.com.au
Quoting Regu.Saliah@astrazeneca.com:
> Fellow DS users.
>
> Im running DS 5.2 on AIX with Oracle 9i and Im getting the above
> Oracle error intermittently. Ive come across ORA-01555 it a previous
> life when
> Ive tried updating a table that Im also querying, but never when the
> only
> transaction happening on the offending table is the select.
>
> The DS job concerned is selecting data from the table into an hashed
> file which is then used as a lookup to update/insert into the same
> table. Because Im selecting into the Hashed file and the
> insert/update is looked
> up against the same hashed file, the update/insert should only happen
> when
> the select is finished. The log confirms this assumption, but I still
> occasionally get the above error while selecting.
>
> Has anyone come across an ORA-01555 when just selecting?
>
> Thanks,
> Regu.
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************
Thanks v. much for that detailed explanation. I think Ill go back to the beginning and get our DBA to put a trace on the DB to prove that my query is the only thing running on that table.
The rare scenario that you have explained is the one that I was worried about, but I think that this would only be a possibility if I were selecting from a table very soon after it had been updated and committed. In my case there has been a number of checkpoints between the updates and the select that would have written the commits to the block, thereby ruling this out.
Thanks again for your time on this.
Cheers,
Regu.
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: 21 August 2002 12:55
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
OK, here goes.
99.9% of the time, ORA-01555 implies that between the start of your generally long running query and the time of the error, another process updated and committed changes to the table you are reading (etc- all covered in other posts).
Obviously, the first thing to do is try every way possible to find if any of the tables in your input query could be subject to this. This may even mean examining views like v$sqlarea to see what other queries have been updating your tables. This would be especially useful so soon after you bring the database up as you would only find queries here that have executed since the restart of the database.
Leave all that with you. Assuming you can still prove that nothing else has updated the table, it still leaves one unfortunate possibility.
It goes like this. (Caveat - Im not an Oracle DBA, so this may not be precisely right, but it gives the general idea. If you want some really detailed explanations, you could go to www.ixora.com.au)
Some background about updates, commits and delayed block cleanout.
1. When you update a block in the database, it is first copied to the rollback segment, then the block is updated and the fact that it is uncommitted also written to the block. 2. When you commit the update, Oracles behaviour depends on how much was updated. If it is only small, the updated blocks are changed to reflect the new SCN (system change number) and the fact that they are now committed. If there are a lot of changes, the individual blocks are NOT updated. Oracle then relies on a concept called "Delayed Block Cleanout". 3. In this situation, Oracle uses subsequent queries to update the individual blocks when they are next read. The subsequent query will discover that the block is dirty and go to the rollback segment to find the clean copy of the block. If it finds it, it can then determine that the block has actually been committed and the SCN of the commit is now written back to the original block. If the change was committed before the query started, then this SCN will be prior to the SCN of the query. 4. If enough time has passed, it is unlikely to find the block it is looking for in the rollback segment, so it simply uses the lowest SCN in the rollback segment as an upper bound for the SCN of the commit. But, this is where the problem can arise as this SCN is later than the actual SCN of the committed change.
Here, then, is an example of how the error occurs.
1. You perform a large enough number of updates (and commit them) to table A so that Oracle will rely on delayed block cleanout. 2. You then start you long running query, which involves table A. 3. A large number of other updates occur in the database, to the extent that they completely use the rollback segment containing the changes for table A. The blocks in the RBS for table A are now lost (no big deal). 4. Your long running query eventually reaches a changed block in table A and goes to the rollback segment to check on the state of play. It does not find the block it wants, thereby concluding that the change was actually committed. As it cannot find the actual SCN of the change, it uses the lowest one that appears in that RBS. Problem is, this SCN happens to be later than the SCN of your query, leading to the incorrect assumption that the block has been updated since your query started, and leading to ORA-01555.
Happens very rarely, but I believe I have had one example in our update process that visits us from time to time. If you know enough about Oracle tracing, I think it is possible to prove if this is what is happening, but Im not going there (Im definitely not a DBA).
What can you do about it? If you can delay the unrelated updates which happen between the start of your query and the error; that would help. If you can change the processing so that your query runs faster, that might help too. In my own situation, this looks a likely possibility.
From your message, though, it seems that this might not even explain you problem. Still, it might give you some clues. I dont think the shutdown/restart is going to make a difference one way or the other. Do you do a shutdown immediate or a normal shutdown?
Maybe looking at what queries have executed since the database started might help (eg v$sqlarea and v$sqltext).
Good luck.
-----Original Message-----
From: Regu.Saliah@astrazeneca.com [mailto:Regu.Saliah@astrazeneca.com]
Sent: Wednesday, 21 August 2002 5:57 PM
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for the comments guys,
I had read the post before, but couldnt relate it to my scenario. The problem I have is that I dont understand how the error could be occurring. This is because it is happening on a test environment that has one user which is under my control. The offending job is one of a number that is sequenced to run daily as a stress test and to establish average load times. The errored job had run successfully the previous night with a few thousand committed inserts/updates. In between that and the next run, which errored, the DB was taken down for backup and brought back up. As I understand it this would have updated the data block with all commits, meaning that the select transaction would not require verification from the transaction table of the rollback segment.
So why is it that the error is occurring? Is my understanding correct?
I am aware that this is a complex area and I could have easily missed something, but I would very much appreciate any help.
Thanks,
Regu.
-----Original Message-----
From: David Barham [mailto:David.Barham@Anglocoal.com.au]
Sent: 20 August 2002 23:30
To: datastage-users@oliver.com
Subject: RE: ORA-01555: snapshot too old: rollback segment number 4 with n ame "R03" too small
Thanks for that Chris. You just saved me from another long reply.
Regu - if that link still does not answer your question, let me know. There is still one really obscure way to get ORA-01555 which is not covered by those posts, but it is extremely rare and not worth the effort of trying to explain if I dont have to. Yes, I know, simply by stating this, I will surely arouse someones interest. Sorry, I dont have a spare 20 minutes just at the moment.
-----Original Message-----
From: Chris Kearns [mailto:chrisk@zing.com.au]
Sent: Wednesday, 21 August 2002 8:10 AM
To: datastage-users@oliver.com
Subject: Re: ORA-01555: snapshot too old: rollback segment number 4
with name "R03" too small
Ive come accross the error before and was helped by the following post found in www.tools4datastage.com:
http://www.tools4datastage.com/forum/to ... C_ID=81513
Chris.
www.altis.com.au
Quoting Regu.Saliah@astrazeneca.com:
> Fellow DS users.
>
> Im running DS 5.2 on AIX with Oracle 9i and Im getting the above
> Oracle error intermittently. Ive come across ORA-01555 it a previous
> life when
> Ive tried updating a table that Im also querying, but never when the
> only
> transaction happening on the offending table is the select.
>
> The DS job concerned is selecting data from the table into an hashed
> file which is then used as a lookup to update/insert into the same
> table. Because Im selecting into the Hashed file and the
> insert/update is looked
> up against the same hashed file, the update/insert should only happen
> when
> the select is finished. The log confirms this assumption, but I still
> occasionally get the above error while selecting.
>
> Has anyone come across an ORA-01555 when just selecting?
>
> Thanks,
> Regu.
>
*************************************************************************
This e-mail and any files transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender by return e-mail, and delete this e-mail from your in-box. Do not copy it to anybody else
*************************************************************************