| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Snapshot too old
Patrick - Just an idea for you, given that the jobs don't share tables.
Sounds as if you may have fetch across commit problem like Dick mentioned.
The best solution would be to fix the programs. A stopgap method in the
meantime would be to assign each job to its own rollback segment so the
blocks wouldn't be aged out quite so unpredictably. Nah, make 'em fix the
program.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Tuesday, January 07, 2003 4:55 PM
To: Multiple recipients of list ORACLE-L
Dear,
No, the different jobs use different tables.
As I already mentioned I did find in 5 programs something like this :
Cursor c1 is select * from x where id = xx;
For c1rec in c1 loop
Blabla
Blabla
Update table x set id = NULL where id = xx; Commit;
End loop;
This is for me a clear case of "fetch across commit". The syntax is not completely correct off course, but the programs are already corrected in the mean time and they never crash again.
Still in 2 jobs , I can not put my finger on it.
I do not want to spit in millions of lines of code. If some command,tool,trace,event whatever can make my life easier, let me know.
Rgds,
Patrick
-----Original Message-----
WILLIAMS
Sent: dinsdag 7 januari 2003 22:10
To: Multiple recipients of list ORACLE-L
Patrick - Do any of these jobs update the same tables? Or do any jobs
read a
table that other jobs are updating?
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Tuesday, January 07, 2003 2:15 PM
To: Multiple recipients of list ORACLE-L
Dear,
Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches)
When the job is running, certain batches stop with ORA-1555 : Snapshot
too
old. Other batches run well till the end.
Bizarre is that not always the same job stops.
When I do a trace I see nothing. With a normal trace I am pretty sure
that I
will never see it.
Rollback segments are rarely used. So making the rollbacks bigger or
smaller
is not the solution.
They also tried to change the commit rate. That was not the solution.
When I modified the optimal size to NULL value to avoid shrinking and
cached
3 heavily used sequences some runs went all the way but
since a week it stops again with the same annoying error.
After that I put an event in the init.ora file : event = "1555 trace
name
processstate forever, level 10"
A trace file was generated but I could not find the error in the trace file.
I am pretty sure that Oracle just dumps all open cursors in a file.
Since
there are 100 of cursors opened I do not have a clue which one
is provoking the error.
I already looked at the batches and I have identified in 5 of them a
"fetch
across commit".
Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !)
So my question is :
How can I know where in the code the error is generated ?
Must I change the definition of the event ? (I know there are other
options
but I can not find them right away)
Should I use DBMS_PROFILER ? (it generates massive files !)
Must they write exceptions everywhere in their code ?
Can somebody help me ?
Please do not send me an explanation of the "snapshot too old" error. I
wake
up with it and I go asleep with it.
Patrick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Patrick Van der Sande
INET: patrick.van.der.sande_at_skynet.be
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 07 2003 - 17:20:24 CST
 |
 |