ORA-01555 error

[ak77]

Hi

I read all the 11 posts about this problem

Code: Select all

ORA-01555: snapshot too old: rollback segment number 4 with name "RBS03" too small

I am running 2 jobs in parallel
Both reading from the same table
one job just reading from the table and writing in a sequential file
Array Size = 5000

Second reads form the table, does a look up againt a Hash file and updates the same table
Input-- Array Size 5000
Output -- Array Size 3000, Transaction Size 3000, and rows per transaction 3000

These two jobs ran successfully for the whole 29M records before ( Job1 processed 29 million rows; Job 2 processed 4M rows)

Today I am getting this error

All the posts discuss a lot about this problem
There is no clear cut solution

This is what I understan

1. Increase the roll back segment
2. Avoid doing jobs like this
3. Something about using before SQL and after SQL to set the rollback segment size i guess
4. Write to a sequential file and update the table

Can someone tell me if there is some other way or how they did it when they came across this situtaion?

If this error occured while the select and update took place simultaneoulsy, then it makes sense to me. But the update job finished 3 hrs before this error Aborted the other job where i just read the data from the table

Help me with this

Thanks

Kishan[/code]

[ArndW]

Kishan,

the rollback segments are used by all Oracle processes; so it might be that another job was using a lot of space and then yours came along and demanded more than was available and thus got the error.

[ak77]

Thanks ArnDw,

This huge data volume is making me learn a lot of stuff
I just restarted the sequence
Now I am just reading in this job
i removed the update job as it has already finished successfully

Then again i am doing updates in the later job but all the data are coming from a sequential file
So is there any chance of getting this error while just updating

Thanks
Kishan

[trokosz]

Have the DBA increase the amount of extents or better yet increase the "time" so the snapshot is not "too old"

[ak77]

Thanks Trokosz,

Before I go to the DBA, is there any problem with the Array Size, Transaction Size and the Rows per transaction size?


Thanks again
Kishan

[trokosz]

Well a 0 means commit after all rows are processed or one giant commit....when too many rows you can see this is not a good idea....for small dimension tables its typically done....

Best practice is to set Array Size high to lets say 5,000 when processing large amounts....

[ak77]

Thanks again

I am using these numbers
one job just reading from the table and writing in a sequential file
Array Size = 5000

the jobs that update the same table
Input-- Array Size 5000
Output -- Array Size 3000, Transaction Size 3000, and rows per transaction 3000


kishan

[kcbland]

Yeah but how wide are your rows? If the problem started today, then it means today there's no large amount of rollback space available. There's no way to get around using rollback unless you switch to other methods that bypass transactional processing, such as sqlldr with DIRECT path, but you'll still need index/constraint/other types of processing that will use rollback.

The short answer is the recommendations you found are excellent ones. Figure out why rollback is short today or get DBA's to solve it. Maybe someone left a query against a large table sitting on their screen, chewing up rollback and parallel query slaves.

[ak77]

Thanks Bland,

The table has like 64 fields averaging char size of 7
I am selecting only 10 fields out of this
But this is a join between three tables
The other two tables are smaller

I restarted the job again and it seems to be running fine till now (24M records processed till now, got another 1.5M records to finish this job)

The following jobs in the sequence are going to update the same table. But no more reading from the table

What is the chance of this occuring again? I havent taken this issue to the DBA yet. I will do that after making sure that I am not doing anything wrong.

No offense to any DBA, they always try to find fault with your job


Thanks for the help and insight
Kishan

[yaminids]

Kishan,

We came across the same problem couple of months back. Our job is designed to SELECT rows from a huge table (about 100M rows) everynight based on SYSDATE. On a normal day, the job used to extract 2-3 million rows.
We got the same error and after discussing with the DBA, we came to know that our transaction (SELECT) is of less priority from Oracle standpoint. Hence every time a higher priority transaction came into picture Oracle aborted our job. We thought of increasing the values of 'Rollback Segment' and 'Retention' but DBAs were against to it as there were other teams accessing the same database. As a work around, we created a Stored Procedure which extracts data from Production database and writes into a Temporary schema. We then directed DataStage to get data from the Temporary schema. In doing so we reduced the amount of time we spent in the Production database

Hope this information is helpful
Yamini

[chinek]

If this error occured while the select and update took place simultaneoulsy, then it makes sense to me. But the update job finished 3 hrs before this error Aborted the other job where i just read the data from the table

Help me with this

Thanks

Kishan[/code][/quote]

Since you run your jobs in parallel, even though your update job finished before your Aborted job, the Aborted job was trying to look for the before image in your rollback segments. To avoid this run your jobs in serial, first run the update job then when it's finished run your next job.
You will find that the performance would not be too badly off either in fact may even be better because your Aborted job will run terribly since it has to look for the before image of your data in the rollback. And it runs the risk of not being able to find those changes in your rollback if other transactions has overwritten those rollback segments which is why you get ORA-01555.

[ak77]

Thanks everybody,

That is what I thought and since i started this, i am going to let it finish
But when i actually turn it over to the other side, i am going to serialize
the two jobs

Already it seems like the job s going to take like 10+ hrs to finish, another 3 hrs would not matter i guess

This will ensure that I dont get that error

Thanks again
Kishnan