This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>---------------------------------------------------------------------------|
| |
| To: |
| cc: |
| Subject: RE: not enough rollback segment |
>---------------------------------------------------------------------------|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>---------------------------------------------------------------------------|
| |
| To: |
| cc: |
| Subject: RE: not enough rollback segment |
>---------------------------------------------------------------------------|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
Sorry, but this is a pointless exercise. At best, this may change the probabilities of this error occurring, but it does not address the fundamental problem. The DataStage job is reading the same rows of a table that another process is updating. This is primarily a design issue and needs to be addressed as such. Refer my other post on this issue.
I wish Oracle would report a more meaningful message for ORA-01555.
Cheers
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:17 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Xiong,
Heres the steps forwarded by our DBAs:
1. Take problem rollback segments offline (not SYSTEM rollback segment). 2. Drop the tablespace containing problem RBS. 3. Recreate the tablespace. 4. Recreate all the RBS and keep them of the same size. If need be increase the INTIAL_EXTENT, NEXT_EXTENT and MAX_EXTENT parameters. 5. Put them on line.
----- Forwarded by Nowell Henry/NYLIC on 08/07/01 10:16 AM -----
|--------+----------------------->
| | "Li, Xiong |
| | (Corporate |
| | Manukau)" |
| | |
| | |
| | 08/06/01 |
| | 05:54 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>-----------------------------------------------------------------------
>----
|
|
|
| To: "datastage-users@oliver.com"
|
| cc:
|
| Subject: not enough rollback segment
|
>-----------------------------------------------------------------------
>----
|
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
I wish Oracle would report a more meaningful message for ORA-01555.
Cheers
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:17 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Xiong,
Heres the steps forwarded by our DBAs:
1. Take problem rollback segments offline (not SYSTEM rollback segment). 2. Drop the tablespace containing problem RBS. 3. Recreate the tablespace. 4. Recreate all the RBS and keep them of the same size. If need be increase the INTIAL_EXTENT, NEXT_EXTENT and MAX_EXTENT parameters. 5. Put them on line.
----- Forwarded by Nowell Henry/NYLIC on 08/07/01 10:16 AM -----
|--------+----------------------->
| | "Li, Xiong |
| | (Corporate |
| | Manukau)" |
| | |
| | |
| | 08/06/01 |
| | 05:54 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>-----------------------------------------------------------------------
>----
|
|
|
| To: "datastage-users@oliver.com"
|
| cc:
|
| Subject: not enough rollback segment
|
>-----------------------------------------------------------------------
>----
|
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
not enough rollback segment
Xiong, David,
Please let us know when you get the rollback segment issue resolved, and what solution worked for you. One of the largest issues as many of us know is Datastage vs. Oracle interaction. User-defined SQL especially has been a source of difficulty in the past. Its good to see these types of problems, and see the resolutions.
Thanks for the postings. Thanks to you David especially for diving so thoroughly into the fundamental problem at hand. I look forward to reading more Oracle-related issues.
Regards,
Nowell
Please let us know when you get the rollback segment issue resolved, and what solution worked for you. One of the largest issues as many of us know is Datastage vs. Oracle interaction. User-defined SQL especially has been a source of difficulty in the past. Its good to see these types of problems, and see the resolutions.
Thanks for the postings. Thanks to you David especially for diving so thoroughly into the fundamental problem at hand. I look forward to reading more Oracle-related issues.
Regards,
Nowell
Clarification time.
I was not suggesting that queries are assigned rollback segments. I was saying the same thing as you, in that queries use the rollback segments to maintain read consistency.
The problem you talk about with running out of rollback space does not result in ORA-01555. It results in a different error ORA-01562: failed to extend rollback segment. I suggest that you read the Oracle documentation on ORA-01555 - it talks about a long running query not being able to reconstruct a snapshot of a block as the rollback data is not available.
I would also point out that the error was reported by the INPUT QUERY, not the update transaction. I suspect your DBA did not realise that this error was being reported by a read only transaction. Changing the transaction size of the output update transaction will only impact the input query if it is updating one of the input tables (which in this case it is not).
Yes, changing the size of the rollback segments and the size of the rollback tablespace can impact on the probability and frequency of this error, but it will not fix it 100%.
ORA-01555 is an error reported by queries which are trying to use the rollback segments to maintain read consistency but are unable to as the required before image in the rollback segment is no longer there. For it to be no longer there, the transaction which created it must have committed (or rolled back) and then a subsequent transaction has then reused that block in the rollback segment or caused Oracle to shrink the rollback segment back to its optimal (minimum) size (and the block in question was beyond the minimum size).
I would go so far as to say that in some situations (I can give examples if required), decreasing the transaction size of an update (remember the error occurred on a query, not an update) can make this problem worse.
Oracle can guarantee read consistency to a query while some other update remains uncommitted. Once a commit is issued, there is no longer a guarantee that read consistency can be maintained.
Perhaps I can illustrate with an ordered sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. Query 1 attempts to read the row updated by Update 2. 4. A commit is issued for update 2
In this scenario, there is no problem. Oracle can maintain read consistency for Query 1 as in step 3, it will go to the rollback segments to find the before image. However, consider this next sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. A commit is issued for update 2 4. Query 1 finally attempts to read the row updated by Update 2 (it was a big table, or the DataStage job is slow for some other reason)
Step 4 now has a problem. It cant use the committed row from the table due to read consistency requirements. It must go to the rollback segments to find an earlier version of the row which is consistent as at the time the query started. If you are lucky, the row is still there. If not, the block has been reused, the row is not there and error ORA-01555 results.
A DBA can fiddle with the rollback segments to alter the balance of probabilities as to whether or not that before image is still in the rollback segments after the commit.
The only real solution is not to get into the situation in the second scenario. Of course, this is easier said than done. A batch process reading a lot of rows from a table at the same time as an OLTP system is updating individual rows is an open invitation for this error.
The standard solution to this situation for an Oracle developer is to break the input query down into a number of smaller queries. However, this is not easily implemented in DataStage.
Where does this story end? Each situation needs to be considered individually. I have seen many different situations result in this error, although in the final analysis, they are all attempting my second scenario above. I have seen just as many solutions. There is no single answer.
The lesson for those who have not had this error is to be mindful, when you are building DataStage jobs, of what could be updating the tables in you input queries.
You are all probably sick of hearing from me by now, so Ill give my keyboard a rest and go to bed.
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:15 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>-----------------------------------------------------------------------
>----
|
|
|
| To:
|
| cc:
|
| Subject: RE: not enough rollback segment
|
>-----------------------------------------------------------------------
>----
|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
I was not suggesting that queries are assigned rollback segments. I was saying the same thing as you, in that queries use the rollback segments to maintain read consistency.
The problem you talk about with running out of rollback space does not result in ORA-01555. It results in a different error ORA-01562: failed to extend rollback segment. I suggest that you read the Oracle documentation on ORA-01555 - it talks about a long running query not being able to reconstruct a snapshot of a block as the rollback data is not available.
I would also point out that the error was reported by the INPUT QUERY, not the update transaction. I suspect your DBA did not realise that this error was being reported by a read only transaction. Changing the transaction size of the output update transaction will only impact the input query if it is updating one of the input tables (which in this case it is not).
Yes, changing the size of the rollback segments and the size of the rollback tablespace can impact on the probability and frequency of this error, but it will not fix it 100%.
ORA-01555 is an error reported by queries which are trying to use the rollback segments to maintain read consistency but are unable to as the required before image in the rollback segment is no longer there. For it to be no longer there, the transaction which created it must have committed (or rolled back) and then a subsequent transaction has then reused that block in the rollback segment or caused Oracle to shrink the rollback segment back to its optimal (minimum) size (and the block in question was beyond the minimum size).
I would go so far as to say that in some situations (I can give examples if required), decreasing the transaction size of an update (remember the error occurred on a query, not an update) can make this problem worse.
Oracle can guarantee read consistency to a query while some other update remains uncommitted. Once a commit is issued, there is no longer a guarantee that read consistency can be maintained.
Perhaps I can illustrate with an ordered sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. Query 1 attempts to read the row updated by Update 2. 4. A commit is issued for update 2
In this scenario, there is no problem. Oracle can maintain read consistency for Query 1 as in step 3, it will go to the rollback segments to find the before image. However, consider this next sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. A commit is issued for update 2 4. Query 1 finally attempts to read the row updated by Update 2 (it was a big table, or the DataStage job is slow for some other reason)
Step 4 now has a problem. It cant use the committed row from the table due to read consistency requirements. It must go to the rollback segments to find an earlier version of the row which is consistent as at the time the query started. If you are lucky, the row is still there. If not, the block has been reused, the row is not there and error ORA-01555 results.
A DBA can fiddle with the rollback segments to alter the balance of probabilities as to whether or not that before image is still in the rollback segments after the commit.
The only real solution is not to get into the situation in the second scenario. Of course, this is easier said than done. A batch process reading a lot of rows from a table at the same time as an OLTP system is updating individual rows is an open invitation for this error.
The standard solution to this situation for an Oracle developer is to break the input query down into a number of smaller queries. However, this is not easily implemented in DataStage.
Where does this story end? Each situation needs to be considered individually. I have seen many different situations result in this error, although in the final analysis, they are all attempting my second scenario above. I have seen just as many solutions. There is no single answer.
The lesson for those who have not had this error is to be mindful, when you are building DataStage jobs, of what could be updating the tables in you input queries.
You are all probably sick of hearing from me by now, so Ill give my keyboard a rest and go to bed.
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:15 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>-----------------------------------------------------------------------
>----
|
|
|
| To:
|
| cc:
|
| Subject: RE: not enough rollback segment
|
>-----------------------------------------------------------------------
>----
|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
Thanks David.. very insightful.
David Barham wrote: Clarification time.
I was not suggesting that queries are assigned rollback segments. I was saying the same thing as you, in that queries use the rollback segments to maintain read consistency.
The problem you talk about with running out of rollback space does not result in ORA-01555. It results in a different error ORA-01562: failed to extend rollback segment. I suggest that you read the Oracle documentation on ORA-01555 - it talks about a long running query not being able to reconstruct a snapshot of a block as the rollback data is not available.
I would also point out that the error was reported by the INPUT QUERY, not the update transaction. I suspect your DBA did not realise that this error was being reported by a read only transaction. Changing the transaction size of the output update transaction will only impact the input query if it is updating one of the input tables (which in this case it is not).
Yes, changing the size of the rollback segments and the size of the rollback tablespace can impact on the probability and frequency of this error, but it will not fix it 100%.
ORA-01555 is an error reported by queries which are trying to use the rollback segments to maintain read consistency but are unable to as the required before image in the rollback segment is no longer there. For it to be no longer there, the transaction which created it must have committed (or rolled back) and then a subsequent transaction has then reused that block in the rollback segment or caused Oracle to shrink the rollback segment back to its optimal (minimum) size (and the block in question was beyond the minimum size).
I would go so far as to say that in some situations (I can give examples if required), decreasing the transaction size of an update (remember the error occurred on a query, not an update) can make this problem worse.
Oracle can guarantee read consistency to a query while some other update remains uncommitted. Once a commit is issued, there is no longer a guarantee that read consistency can be maintained.
Perhaps I can illustrate with an ordered sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. Query 1 attempts to read the row updated by Update 2. 4. A commit is issued for update 2
In this scenario, there is no problem. Oracle can maintain read consistency for Query 1 as in step 3, it will go to the rollback segments to find the before image. However, consider this next sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. A commit is issued for update 2 4. Query 1 finally attempts to read the row updated by Update 2 (it was a big table, or the DataStage job is slow for some other reason)
Step 4 now has a problem. It cant use the committed row from the table due to read consistency requirements. It must go to the rollback segments to find an earlier version of the row which is consistent as at the time the query started. If you are lucky, the row is still there. If not, the block has been reused, the row is not there and error ORA-01555 results.
A DBA can fiddle with the rollback segments to alter the balance of probabilities as to whether or not that before image is still in the rollback segments after the commit.
The only real solution is not to get into the situation in the second scenario. Of course, this is easier said than done. A batch process reading a lot of rows from a table at the same time as an OLTP system is updating individual rows is an open invitation for this error.
The standard solution to this situation for an Oracle developer is to break the input query down into a number of smaller queries. However, this is not easily implemented in DataStage.
Where does this story end? Each situation needs to be considered individually. I have seen many different situations result in this error, although in the final analysis, they are all attempting my second scenario above. I have seen just as many solutions. There is no single answer.
The lesson for those who have not had this error is to be mindful, when you are building DataStage jobs, of what could be updating the tables in you input queries.
You are all probably sick of hearing from me by now, so Ill give my keyboard a rest and go to bed.
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:15 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | | | .hm> |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>-----------------------------------------------------------------------
>----
|
|
|
| To:
|
| cc:
|
| Subject: RE: not enough rollback segment
|
>-----------------------------------------------------------------------
>----
|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
---------------------------------
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
David Barham wrote: Clarification time.
I was not suggesting that queries are assigned rollback segments. I was saying the same thing as you, in that queries use the rollback segments to maintain read consistency.
The problem you talk about with running out of rollback space does not result in ORA-01555. It results in a different error ORA-01562: failed to extend rollback segment. I suggest that you read the Oracle documentation on ORA-01555 - it talks about a long running query not being able to reconstruct a snapshot of a block as the rollback data is not available.
I would also point out that the error was reported by the INPUT QUERY, not the update transaction. I suspect your DBA did not realise that this error was being reported by a read only transaction. Changing the transaction size of the output update transaction will only impact the input query if it is updating one of the input tables (which in this case it is not).
Yes, changing the size of the rollback segments and the size of the rollback tablespace can impact on the probability and frequency of this error, but it will not fix it 100%.
ORA-01555 is an error reported by queries which are trying to use the rollback segments to maintain read consistency but are unable to as the required before image in the rollback segment is no longer there. For it to be no longer there, the transaction which created it must have committed (or rolled back) and then a subsequent transaction has then reused that block in the rollback segment or caused Oracle to shrink the rollback segment back to its optimal (minimum) size (and the block in question was beyond the minimum size).
I would go so far as to say that in some situations (I can give examples if required), decreasing the transaction size of an update (remember the error occurred on a query, not an update) can make this problem worse.
Oracle can guarantee read consistency to a query while some other update remains uncommitted. Once a commit is issued, there is no longer a guarantee that read consistency can be maintained.
Perhaps I can illustrate with an ordered sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. Query 1 attempts to read the row updated by Update 2. 4. A commit is issued for update 2
In this scenario, there is no problem. Oracle can maintain read consistency for Query 1 as in step 3, it will go to the rollback segments to find the before image. However, consider this next sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. A commit is issued for update 2 4. Query 1 finally attempts to read the row updated by Update 2 (it was a big table, or the DataStage job is slow for some other reason)
Step 4 now has a problem. It cant use the committed row from the table due to read consistency requirements. It must go to the rollback segments to find an earlier version of the row which is consistent as at the time the query started. If you are lucky, the row is still there. If not, the block has been reused, the row is not there and error ORA-01555 results.
A DBA can fiddle with the rollback segments to alter the balance of probabilities as to whether or not that before image is still in the rollback segments after the commit.
The only real solution is not to get into the situation in the second scenario. Of course, this is easier said than done. A batch process reading a lot of rows from a table at the same time as an OLTP system is updating individual rows is an open invitation for this error.
The standard solution to this situation for an Oracle developer is to break the input query down into a number of smaller queries. However, this is not easily implemented in DataStage.
Where does this story end? Each situation needs to be considered individually. I have seen many different situations result in this error, although in the final analysis, they are all attempting my second scenario above. I have seen just as many solutions. There is no single answer.
The lesson for those who have not had this error is to be mindful, when you are building DataStage jobs, of what could be updating the tables in you input queries.
You are all probably sick of hearing from me by now, so Ill give my keyboard a rest and go to bed.
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:15 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | | | .hm> |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>-----------------------------------------------------------------------
>----
|
|
|
| To:
|
| cc:
|
| Subject: RE: not enough rollback segment
|
>-----------------------------------------------------------------------
>----
|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
---------------------------------
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
David is right on the money with this one. I concur with Davids suggestion to more quickly extract your source data. DataStage acts like a pipeline between two passive stages. If your source stage is read data from a source (file or table) and writing to a target (file or table), the slowest part of either end is the determining factor as to the speed of the job.
Thus, select from a table and writing to a table is probably going to be driven by the speed of the insert/update. This will stretch out the open connection to the source table as the insert/update is putting backpressure
on the sourcing. Therefore, its in your best interest to break the
DataStage jobs into multiple jobs, dedicated to a specific letter of ETL (Extract, Transform, Load). If you are selecting from a table and writing to a sequential file, you will probably mitigrate your problem away, because you are creating a more instantenous snapshot of your source data.
Example:
All-in-one job sources from table, transforms, and inserts/updates a target table runs at 200 rows/s.
Split this into three jobs:
source table to sequential file (you will probably see fantastic performance, like 2000 rows/s)
sequential file thru transformers to sequential file (good performance, like 1000 rows/s)
sequential file to target table (heres where the limiting 200 rows/s come in for this example)
Doing this accomplishes many things:
1. You have modularity
2. You have restart points in processing (good during development) 3. Easier to debug 4. Audit trail (sequential snapshot files at milestones) 5. You have indentifiable bottlenecks (not just guesses) 6. Faster selection of source data creates more closer snapshot of data at point in time (important in a rapidly changing source, as you are seeing)
Remember, its called DataSTAGE, as in stage your data throughout the process. Otherwise itd be called DataDONTSETTHEDATADOWN.
Good Luck!
-Ken
david@barham.hm on 07-Aug-2001 12:03
Please respond to datastage-users@oliver.com
To: datastage-users
cc:
Subject: RE: not enough rollback segment
Clarification time.
I was not suggesting that queries are assigned rollback segments. I was saying the same thing as you, in that queries use the rollback segments to maintain read consistency.
The problem you talk about with running out of rollback space does not result in ORA-01555. It results in a different error ORA-01562: failed to extend rollback segment. I suggest that you read the Oracle documentation on ORA-01555 - it talks about a long running query not being able to reconstruct a snapshot of a block as the rollback data is not available.
I would also point out that the error was reported by the INPUT QUERY, not the update transaction. I suspect your DBA did not realise that this error was being reported by a read only transaction. Changing the transaction size of the output update transaction will only impact the input query if it is updating one of the input tables (which in this case it is not).
Yes, changing the size of the rollback segments and the size of the rollback tablespace can impact on the probability and frequency of this error, but it will not fix it 100%.
ORA-01555 is an error reported by queries which are trying to use the rollback segments to maintain read consistency but are unable to as the required before image in the rollback segment is no longer there. For it to be no longer there, the transaction which created it must have committed (or rolled back) and then a subsequent transaction has then reused that block in the rollback segment or caused Oracle to shrink the rollback segment back to its optimal (minimum) size (and the block in question was beyond the minimum size).
I would go so far as to say that in some situations (I can give examples if required), decreasing the transaction size of an update (remember the error occurred on a query, not an update) can make this problem worse.
Oracle can guarantee read consistency to a query while some other update remains uncommitted. Once a commit is issued, there is no longer a guarantee that read consistency can be maintained.
Perhaps I can illustrate with an ordered sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. Query 1 attempts to read the row updated by Update 2. 4. A commit is issued for update 2
In this scenario, there is no problem. Oracle can maintain read consistency for Query 1 as in step 3, it will go to the rollback segments to find the before image. However, consider this next sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. A commit is issued for update 2 4. Query 1 finally attempts to read the row updated by Update 2 (it was a big table, or the DataStage job is slow for some other reason)
Step 4 now has a problem. It cant use the committed row from the table due to read consistency requirements. It must go to the rollback segments to find an earlier version of the row which is consistent as at the time the query started. If you are lucky, the row is still there. If not, the block has been reused, the row is not there and error ORA-01555 results.
A DBA can fiddle with the rollback segments to alter the balance of probabilities as to whether or not that before image is still in the rollback segments after the commit.
The only real solution is not to get into the situation in the second scenario. Of course, this is easier said than done. A batch process reading a lot of rows from a table at the same time as an OLTP system is updating individual rows is an open invitation for this error.
The standard solution to this situation for an Oracle developer is to break the input query down into a number of smaller queries. However, this is not easily implemented in DataStage.
Where does this story end? Each situation needs to be considered individually. I have seen many different situations result in this error, although in the final analysis, they are all attempting my second scenario above. I have seen just as many solutions. There is no single answer.
The lesson for those who have not had this error is to be mindful, when you are building DataStage jobs, of what could be updating the tables in you input queries.
You are all probably sick of hearing from me by now, so Ill give my keyboard a rest and go to bed.
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:15 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>
---------------------------------------------------------------------------
|
|
|
| To:
|
| cc:
|
| Subject: RE: not enough rollback segment
|
>
---------------------------------------------------------------------------
|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as
well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
Thus, select from a table and writing to a table is probably going to be driven by the speed of the insert/update. This will stretch out the open connection to the source table as the insert/update is putting backpressure
on the sourcing. Therefore, its in your best interest to break the
DataStage jobs into multiple jobs, dedicated to a specific letter of ETL (Extract, Transform, Load). If you are selecting from a table and writing to a sequential file, you will probably mitigrate your problem away, because you are creating a more instantenous snapshot of your source data.
Example:
All-in-one job sources from table, transforms, and inserts/updates a target table runs at 200 rows/s.
Split this into three jobs:
source table to sequential file (you will probably see fantastic performance, like 2000 rows/s)
sequential file thru transformers to sequential file (good performance, like 1000 rows/s)
sequential file to target table (heres where the limiting 200 rows/s come in for this example)
Doing this accomplishes many things:
1. You have modularity
2. You have restart points in processing (good during development) 3. Easier to debug 4. Audit trail (sequential snapshot files at milestones) 5. You have indentifiable bottlenecks (not just guesses) 6. Faster selection of source data creates more closer snapshot of data at point in time (important in a rapidly changing source, as you are seeing)
Remember, its called DataSTAGE, as in stage your data throughout the process. Otherwise itd be called DataDONTSETTHEDATADOWN.
Good Luck!
-Ken
david@barham.hm on 07-Aug-2001 12:03
Please respond to datastage-users@oliver.com
To: datastage-users
cc:
Subject: RE: not enough rollback segment
Clarification time.
I was not suggesting that queries are assigned rollback segments. I was saying the same thing as you, in that queries use the rollback segments to maintain read consistency.
The problem you talk about with running out of rollback space does not result in ORA-01555. It results in a different error ORA-01562: failed to extend rollback segment. I suggest that you read the Oracle documentation on ORA-01555 - it talks about a long running query not being able to reconstruct a snapshot of a block as the rollback data is not available.
I would also point out that the error was reported by the INPUT QUERY, not the update transaction. I suspect your DBA did not realise that this error was being reported by a read only transaction. Changing the transaction size of the output update transaction will only impact the input query if it is updating one of the input tables (which in this case it is not).
Yes, changing the size of the rollback segments and the size of the rollback tablespace can impact on the probability and frequency of this error, but it will not fix it 100%.
ORA-01555 is an error reported by queries which are trying to use the rollback segments to maintain read consistency but are unable to as the required before image in the rollback segment is no longer there. For it to be no longer there, the transaction which created it must have committed (or rolled back) and then a subsequent transaction has then reused that block in the rollback segment or caused Oracle to shrink the rollback segment back to its optimal (minimum) size (and the block in question was beyond the minimum size).
I would go so far as to say that in some situations (I can give examples if required), decreasing the transaction size of an update (remember the error occurred on a query, not an update) can make this problem worse.
Oracle can guarantee read consistency to a query while some other update remains uncommitted. Once a commit is issued, there is no longer a guarantee that read consistency can be maintained.
Perhaps I can illustrate with an ordered sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. Query 1 attempts to read the row updated by Update 2. 4. A commit is issued for update 2
In this scenario, there is no problem. Oracle can maintain read consistency for Query 1 as in step 3, it will go to the rollback segments to find the before image. However, consider this next sequence of events:
1. Query 1 starts reading table A (table scan for simplicity) 2. Update 2 updates a single row (via an index lookup) of table A 3. A commit is issued for update 2 4. Query 1 finally attempts to read the row updated by Update 2 (it was a big table, or the DataStage job is slow for some other reason)
Step 4 now has a problem. It cant use the committed row from the table due to read consistency requirements. It must go to the rollback segments to find an earlier version of the row which is consistent as at the time the query started. If you are lucky, the row is still there. If not, the block has been reused, the row is not there and error ORA-01555 results.
A DBA can fiddle with the rollback segments to alter the balance of probabilities as to whether or not that before image is still in the rollback segments after the commit.
The only real solution is not to get into the situation in the second scenario. Of course, this is easier said than done. A batch process reading a lot of rows from a table at the same time as an OLTP system is updating individual rows is an open invitation for this error.
The standard solution to this situation for an Oracle developer is to break the input query down into a number of smaller queries. However, this is not easily implemented in DataStage.
Where does this story end? Each situation needs to be considered individually. I have seen many different situations result in this error, although in the final analysis, they are all attempting my second scenario above. I have seen just as many solutions. There is no single answer.
The lesson for those who have not had this error is to be mindful, when you are building DataStage jobs, of what could be updating the tables in you input queries.
You are all probably sick of hearing from me by now, so Ill give my keyboard a rest and go to bed.
-----Original Message-----
From: Nowell Henry [mailto:Nowell_Henry@newyorklife.com]
Sent: Wednesday, 8 August 2001 12:15 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Xiong,
We have run across this error here as well. I agree with what you say David as far as reading data. However, rollback segments CAN cause problems though if you have your transaction size of 0. The reason is that rollback segments not only provide read consistency, but they are used for rollback. This means that before a commit is issued, you can enter a "rollback" command to undo all the updates that you performed against a target table. Until a commit is issued, the particular rollback segment that you are using in a transaction is not released to be overwritten. If your transaction is large enough without committing, your rollback segment will fail. You will write and write entries in the rollback segment until it loops back around and tries to overwrite the first rollback info you logged. You wont be able to overwrite that entry because your transaction hasnt been committed yet. For more info on rollback segments check the Oracle8 concepts guide on http://technet.oracle.com or ask you Oracle DBAs.
Now, for your specific problem: We ran across it as well. I asked our DBAs for you. What you did in reducing your transaction size should have worked. I disagree with David because querries are NEVER assigned rollback segments. Queries use rollback segments for read consistency, but it is the update transactions that are actually assigned the rollback segments. Heres the proof, from the Oracle8 concepts guide (again, on technet):
"Oracle can assign a transaction automatically to the next available rollback segment. The transaction assignment occurs when you issue the first DML or DDL statement in the transaction. Oracle never assigns read-only transactions (transactions that contain only queries) to a rollback segment, regardless of whether the transaction begins with a SET TRANSACTION READ ONLY statement. "
There is a background process in Oracle called SMON that is supposed to coalesce the free extents (space) in the rollback segment, but sometimes it is a little slow Im guessing. Our DBA recommended that your DBAs: 1) take the problematic rollback segments OFFLINE 2) drop the tablespace containing these problem rollback segments, and 3) re-creating the tablespace and the rollback segments.
This fixed the problem for us after we changed our transaction sizes as you did.
Regards,
Nowell Henry
Data Specialist
New York Life Insurance
|--------+----------------------->
| | "David |
| | Barham" |
| | |
| | |
| | 08/06/01 |
| | 06:36 PM |
| | Please |
| | respond to |
| | datastage-use|
| | rs |
| | |
|--------+----------------------->
>
---------------------------------------------------------------------------
|
|
|
| To:
|
| cc:
|
| Subject: RE: not enough rollback segment
|
>
---------------------------------------------------------------------------
|
This error message is misleading.
Im guessing that the CLARITY stage in your job is an input to the job, or if that OCI stage has inputs and outputs, it is the link that reads data (an output of the stage) that caused the problem.
Changing the transaction size will not help. What you observed was simply coincidence. Increasing the number of rollback segments (as opposed to RBS
space) MIGHT help.
Here is why ORA-01555 happens:
Your job is reading data from Oracle. It probably takes a while to run, especially compared with the length of transactions in the OLTP system you are reading from. Once your input query starts, Oracle (and the ANSI
standard) guarantees that you will get consistent data.
However, after your query starts, another transaction updates AND COMMITS some data in the table that you reading, but that you have not read yet. When your query gets to this updated data, Oracle realises that it cannot give it you. At this point, the ANSI standard says that your read query should fail. Oracle goes a step further and tries to reconstruct the data for you from the before images in the rollback segments. If the before images are still there, you are in luck and your query continues. If not, you get error ORA-01555.
Remember, the before images are kept for the purposes of rolling back an incomplete update transaction. Once that transaction is committed, the before images are no longer required. This means that a job which updates the table it is reading would be better off with transaction size of 0 so that it does not commit until completion and all the before images are available. (There are other ways of dealing with this sort of job as
well.)
Without seeing you job, it is hard to know where you problem is. Often the job design can be changed to minimise or even eliminate this problem.
If a job writes back to a table that it reads from, you are asking for this error (but there are work arounds).
If your source system is different to your target system, and you only read from the source, this can be the hardest of all to solve.
It is worth noting that you usually only get this error on the primary input query. Unless you have some shocking reference queries, you should never see this error on a reference lookup. If you can figure out which table is being updated while you are reading it, this may help you design a solution. An example where this applies is if your input query joins a number of tables and the problem is with a table you are joining to rather than the main input table. In this case, you can change your job design to read the main table only and lookup the joined table.
If you can reduce the time your input query is running, this may help too. Often it is all the lookups and final update that slow a job down. If the input stage could write its output direct to a flat file, then the rest of the job reads from this file, you may find the input query is much quicker and your problem is solved. This depends on you situation.
These are all general comments. If you want to send me an export of your job, I could have a look at it to try and figure out what is causing the problem in your situation. My email address is in the footer.
David Barham
Information Technology Consultant
InformAtect Pty Ltd
Brisbane, Australia
e-mail: david@barham.hm
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [mailto:Xiong.Li@chh.co.nz]
Sent: Tuesday, 7 August 2001 7:54 AM
To: datastage-users@oliver.com
Subject: not enough rollback segment
Hi, all
My overnight job aborted last week along with the message:
ProcessFSEBITClassification.CLARITY: ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small.
It seems that the rollback segment is not enough, my oracle DBA thought The RBS are large enough for an OLTP system along with the amount of RBS. So I changed ORAOCI8 stage "Transaction Size" from 2000 to 100, then it worked OK.
This morning, it aborted again, with the same problem. What can I do now? reduce Transaction Size again, or add more rollback segment? Can anybody give me a recommendation?
Thanks in advance
Xiong
DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Carter Holt Harvey is not responsible for any changes made to this message and / or any attachments after sending by Carter Holt Harvey. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
Hi, Nowell, David
I would like to specify my job problem clearer.
1. This job is in an overnight processing batch job.
2. It abort with the same problem, ORA-01555: snapshot too old: rollback segment number 13 with name "RBS12" too small, but please notice each time, the seg number is changed. 3. I re-ran it in day time each time it aborted. It worked just fine. so I suppose "not enough rollback segment" is not the only reason. The other reason might be that the rollback segments were shared by too many DML processes at that time. 4. For my situation, what is the effective way to update rollback segment if I need to do so.
By the way, my DBA told me the Transaction_Per_Rollback_Segment=5, (which is the number of transactions you expect each rollback segment to have to
handle)
Thanks in advance
Xiong Li
Project Clarity
Carter Holt Harvey - Forests
VPN: 96094
Direct: 09 2626094
Mob: 021 1412588
> -----Original Message-----
> From: Nowell Henry [SMTP:Nowell_Henry@newyorklife.com]
> Sent: Wednesday, 8 August 2001 04:03
> To: datastage-users@oliver.com
> Subject: not enough rollback segment
>
> Xiong, David,
>
> Please let us know when you get the rollback segment issue resolved,
> and what solution worked for you. One of the largest issues as many
> of us know is Datastage vs. Oracle interaction. User-defined SQL
> especially has been a source
> of difficulty in the past. Its good to see these types of problems, and
> see
> the resolutions.
>
> Thanks for the postings. Thanks to you David especially for diving so
> thoroughly into the fundamental problem at hand. I look forward to
> reading more Oracle-related issues.
>
> Regards,
>
> Nowell
>
DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.
I would like to specify my job problem clearer.
1. This job is in an overnight processing batch job.
2. It abort with the same problem, ORA-01555: snapshot too old: rollback segment number 13 with name "RBS12" too small, but please notice each time, the seg number is changed. 3. I re-ran it in day time each time it aborted. It worked just fine. so I suppose "not enough rollback segment" is not the only reason. The other reason might be that the rollback segments were shared by too many DML processes at that time. 4. For my situation, what is the effective way to update rollback segment if I need to do so.
By the way, my DBA told me the Transaction_Per_Rollback_Segment=5, (which is the number of transactions you expect each rollback segment to have to
handle)
Thanks in advance
Xiong Li
Project Clarity
Carter Holt Harvey - Forests
VPN: 96094
Direct: 09 2626094
Mob: 021 1412588
> -----Original Message-----
> From: Nowell Henry [SMTP:Nowell_Henry@newyorklife.com]
> Sent: Wednesday, 8 August 2001 04:03
> To: datastage-users@oliver.com
> Subject: not enough rollback segment
>
> Xiong, David,
>
> Please let us know when you get the rollback segment issue resolved,
> and what solution worked for you. One of the largest issues as many
> of us know is Datastage vs. Oracle interaction. User-defined SQL
> especially has been a source
> of difficulty in the past. Its good to see these types of problems, and
> see
> the resolutions.
>
> Thanks for the postings. Thanks to you David especially for diving so
> thoroughly into the fundamental problem at hand. I look forward to
> reading more Oracle-related issues.
>
> Regards,
>
> Nowell
>
DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.
Xiong,
Did you look through the fairly long message I sent you yesterday after looking at you job? (Im hoping that I actually clicked the send button and that it is not still open on my PC at home).
The message that I am trying to get across is stop looking to the rollback segments for a solution. You wont find the solution there. Try the things I suggested in my earlier message (sorry, guys and gals, this was one I sent to Xiong directly - maybe I should post it to the group as well if there is enough interest - it was even longer than all my other postings on this subject).
You have to look at why you are reading and writing to the same table at the same time. If it cant be avoided, the try to minimise the time that your input query is active (eg interpose a sequential stage).
To address you specific points.
1. Does this mean that users wont be executing OLTP transactions
against this table? If so this is good. You MUST find out what is updating the table while you are reading it and if possible separate the it from this job. If it is not conflict with the OLTP system, then you might need to look at what other jobs are running at the same time.
2. Segment number is irrelevant.
3. We have transactions_per_rollback_segment set to 20. Youll have to
believe me on this as I dont have the time for a full explanation, but multiple transactions per rollback segment can actually help reduce the probability of this error. Other uncommitted transactions in the same rollback segment can have the effect of protecting the before image that you require, allowing it to survive longer.
4. As I said above. Stop looking to database administration for a
solution and start looking to application design.
If you took error ORA-01555 to a good Oracle DBA, he will throw it back in your face and say "not my problem, go fix your application design". (Although he would probably also have a quick look at the rollback segment statistics to make sure they are not dramatically undersized.)
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [SMTP:Xiong.Li@chh.co.nz]
Sent: Wednesday, August 08, 2001 8:00 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Hi, Nowell, David
I would like to specify my job problem clearer.
1. This job is in an overnight processing batch job.
2. It abort with the same problem, ORA-01555: snapshot too old: rollback segment number 13 with name "RBS12" too small, but please notice each time, the seg number is changed. 3. I re-ran it in day time each time it aborted. It worked just fine. so I suppose "not enough rollback segment" is not the only reason. The other reason might be that the rollback segments were shared by too many DML processes at that time. 4. For my situation, what is the effective way to update rollback segment if I need to do so.
By the way, my DBA told me the Transaction_Per_Rollback_Segment=5, (which is the number of transactions you expect each rollback segment to have to
handle)
Thanks in advance
Xiong Li
Project Clarity
Carter Holt Harvey - Forests
VPN: 96094
Direct: 09 2626094
Mob: 021 1412588
> -----Original Message-----
> From: Nowell Henry [SMTP:Nowell_Henry@newyorklife.com]
> Sent: Wednesday, 8 August 2001 04:03
> To: datastage-users@oliver.com
> Subject: not enough rollback segment
>
> Xiong, David,
>
> Please let us know when you get the rollback segment issue resolved,
> and what solution worked for you. One of the largest issues as many
> of us know is Datastage vs. Oracle interaction. User-defined SQL
> especially has been a source
> of difficulty in the past. Its good to see these types of problems, and
> see
> the resolutions.
>
> Thanks for the postings. Thanks to you David especially for diving so
> thoroughly into the fundamental problem at hand. I look forward to
> reading more Oracle-related issues.
>
> Regards,
>
> Nowell
>
DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.
*************************************************************************
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
*************************************************************************
Did you look through the fairly long message I sent you yesterday after looking at you job? (Im hoping that I actually clicked the send button and that it is not still open on my PC at home).
The message that I am trying to get across is stop looking to the rollback segments for a solution. You wont find the solution there. Try the things I suggested in my earlier message (sorry, guys and gals, this was one I sent to Xiong directly - maybe I should post it to the group as well if there is enough interest - it was even longer than all my other postings on this subject).
You have to look at why you are reading and writing to the same table at the same time. If it cant be avoided, the try to minimise the time that your input query is active (eg interpose a sequential stage).
To address you specific points.
1. Does this mean that users wont be executing OLTP transactions
against this table? If so this is good. You MUST find out what is updating the table while you are reading it and if possible separate the it from this job. If it is not conflict with the OLTP system, then you might need to look at what other jobs are running at the same time.
2. Segment number is irrelevant.
3. We have transactions_per_rollback_segment set to 20. Youll have to
believe me on this as I dont have the time for a full explanation, but multiple transactions per rollback segment can actually help reduce the probability of this error. Other uncommitted transactions in the same rollback segment can have the effect of protecting the before image that you require, allowing it to survive longer.
4. As I said above. Stop looking to database administration for a
solution and start looking to application design.
If you took error ORA-01555 to a good Oracle DBA, he will throw it back in your face and say "not my problem, go fix your application design". (Although he would probably also have a quick look at the rollback segment statistics to make sure they are not dramatically undersized.)
-----Original Message-----
From: Li, Xiong (Corporate Manukau) [SMTP:Xiong.Li@chh.co.nz]
Sent: Wednesday, August 08, 2001 8:00 AM
To: datastage-users@oliver.com
Subject: RE: not enough rollback segment
Hi, Nowell, David
I would like to specify my job problem clearer.
1. This job is in an overnight processing batch job.
2. It abort with the same problem, ORA-01555: snapshot too old: rollback segment number 13 with name "RBS12" too small, but please notice each time, the seg number is changed. 3. I re-ran it in day time each time it aborted. It worked just fine. so I suppose "not enough rollback segment" is not the only reason. The other reason might be that the rollback segments were shared by too many DML processes at that time. 4. For my situation, what is the effective way to update rollback segment if I need to do so.
By the way, my DBA told me the Transaction_Per_Rollback_Segment=5, (which is the number of transactions you expect each rollback segment to have to
handle)
Thanks in advance
Xiong Li
Project Clarity
Carter Holt Harvey - Forests
VPN: 96094
Direct: 09 2626094
Mob: 021 1412588
> -----Original Message-----
> From: Nowell Henry [SMTP:Nowell_Henry@newyorklife.com]
> Sent: Wednesday, 8 August 2001 04:03
> To: datastage-users@oliver.com
> Subject: not enough rollback segment
>
> Xiong, David,
>
> Please let us know when you get the rollback segment issue resolved,
> and what solution worked for you. One of the largest issues as many
> of us know is Datastage vs. Oracle interaction. User-defined SQL
> especially has been a source
> of difficulty in the past. Its good to see these types of problems, and
> see
> the resolutions.
>
> Thanks for the postings. Thanks to you David especially for diving so
> thoroughly into the fundamental problem at hand. I look forward to
> reading more Oracle-related issues.
>
> Regards,
>
> Nowell
>
DISCLAIMER: This electronic message together with any attachments is
confidential. If you are not the intended recipient, do not copy, disclose or
use the contents in any way. Please also advise us by return e-mail that you
have received the message and then please destroy. Carter Holt Harvey is not
responsible for any changes made to this message and / or any attachments after
sending by Carter Holt Harvey. We use virus scanning software but exclude all
liability for viruses or anything similar in this email or any attachment.
*************************************************************************
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
*************************************************************************