| ORA-01555 snapshot too old [message #613717] |
Fri, 09 May 2014 12:18  |
|
|
Experts,
One of my oracle batch job is failing with ORA-01555 snapshot too old error. It is a long running job approx. 4-5 hours and scheduled to after 6 PM everyday. It uses two views which in turns joins multiple tables to fetch data.
Root cause
-----------
Recently i have introduced a update and commit statement within a function in a package, then onwards the job is failing with ORA-01555. So i have reverted my changes in the pkb and redeployed(only the pkb, no changes to the view which uses this pkb). But still the job is failing with ORA-01555. I am triaging further to identify the root cause.
Note : If i schedule this job after 12 AM(midnight) it is completing successfully.
Please help me to identify the root cause or steps for triaging this issue. Thanks in advance.
|
|
|
|
|
|
|
|
| Re: ORA-01555 snapshot too old [message #613720 is a reply to message #613719] |
Fri, 09 May 2014 12:37  |
|
|
Michel & BlackSwan,
Yes, you're correct. I have removed the update & commit from the .pkb and redeployed the .pks and .pkb. The view which uses the function is not redeployed.
One more observation, the view is slightly modified so the job is now running for 11-12 hours and failing with ORA-01555. Also few more indexes were added to tables which were used in the view.
|
|
|
|
|
|
| Re: ORA-01555 snapshot too old [message #613722 is a reply to message #613720] |
Fri, 09 May 2014 12:45 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I cannot advise on your specific problem, but I can share my experience of a similar problem. It was a job that ran for between 18 and 24 hours. We tried setting undo_retention to 90000, but even that didn't stop the 1555s. The error was on the SQL that populates a cursor, then the code looped through the cursor doing row-by-row processing. My first "fix" was to run the query and write the rows to a global temporary table, then base the cursor on that. Problem solved. The better fix was to tune the SQLs in the loop, so that the process ran in only 6 hours. The real fix would have been to re-write the whole thing so that it wouldn't row-by-row processing.
|
|
|
|
|
|
|
|
| Re: ORA-01555 snapshot too old [message #613729 is a reply to message #613727] |
Fri, 09 May 2014 13:56 |
|
|
Per my understanding, long running SELECT is the root cause for ORA-01555. Below are only changes added to the view,
1)Added few WHEN and ELSE conditions to a COALESCE function in the SELECT query. It has TO_CHAR function added to one of the column too.
2)Added non-unique INDEX to few tables used in the view(May be this could have modified the execution path of the SELECT query in VIEW)
|
|
|
|
| Re: ORA-01555 snapshot too old [message #613730 is a reply to message #613729] |
Fri, 09 May 2014 14:00 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
May, might, could, would, can, will etc. is understood by people only when we can see the code and the error. Else, it's almost impossible to comment/suggest without a test case. So why don't you post a test case and lets try to fix your issue.
|
|
|
|
|
|
| Re: ORA-01555 snapshot too old [message #613827 is a reply to message #613732] |
Mon, 12 May 2014 15:13 |
|
|
|
Thanks all for your valuable inputs. I was able to resolve this issue by changing the job's date parameters. It was configured records back from 01-JAN-2013 as a cutoff, so it was doing a Full table scan on transactional table, which it should do a Index scan. Now i have modified to fetch from 3 months old records, so it is doing a Index scan on the table and running successfully.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
