Posted: Mon Aug 06, 2001 10:36 pm
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.
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.