Oracle-L: Re:Snapshot too old

From: <dgoulet_at_vicr.com>
Date: Tue, 07 Jan 2003 13:05:26 -0800
Message-ID: <F001.0052925E.20030107130526@fatcity.com>

Patrick,

Chasing down the culprit of a randomly happening ORA-1555 can be a true pain in the ^%%.

First thing to check is if you have a commit happening across a cursor. This little jewel happens when the duhveloper decided that he needed a cursor to retrieve some data from a table and then does some sporadic updates on the same table and commits the action. A lot of other database will then invalidate the cursor, similarly to what happens with a cursor that has the "for update" clause, but Oracle allows one to do that with a normal cursor without closing and reopening. The end result are some intermittent ORA-1555's. One solution to that, if possible, is to add an order by or group by to the cursor's statement which forces Oracle to create a temp table. The other solution is to not do that.

Second thing is to look around and see if some one else is running a bulk data load/update/delete. Many more times than I care to remember I have found that long running jobs fail with an intermittent ORA-1555 not because of anything their doing, but what someone else is doing in the database at the same time. This is a hard one to find and a harder one to fix since the duhveloper who creates the offending job does not see the error.

Lots of luck!!

Dick Goulet

____________________Reply Separator____________________
Author: "Patrick Van der Sande" <patrick.van.der.sande_at_skynet.be>
Date:       1/7/2003 12:14 PM

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

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">

<meta name=ProgId content=Word.Document>
<meta name=Generator content="Microsoft Word 10">
<meta name=Originator content="Microsoft Word 10">
<link rel=File-List href="cid:filelist.xml_at_01C2B691.CEA35900">
<!--[if gte mso 9]><xml>
<w:WordDocument>

  <w:SpellingState>Clean</w:SpellingState>
  <w:GrammarState>Clean</w:GrammarState>
  <w:DocumentKind>DocumentEmail</w:DocumentKind>
  <w:EnvelopeVis/>
  <w:Compatibility>
   <w:BreakWrappedTables/>
   <w:SnapToGridInCell/>
   <w:WrapTextWithPunct/>

<w:UseAsianBreakRules/>
</w:Compatibility>

  <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
 </w:WordDocument>

</xml><![endif]-->
<style>
<!--

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

        {mso-style-parent:"";
        margin:0cm;
        margin-bottom:.0001pt;
        mso-pagination:widow-orphan;
        font-size:12.0pt;
        font-family:"Times New Roman";
        mso-fareast-font-family:"Times New Roman";}
a:link, span.MsoHyperlink
        {color:blue;
        text-decoration:underline;
        text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
        {color:purple;
        text-decoration:underline;
        text-underline:single;}
span.EmailStyle17
        {mso-style-type:personal-compose;
        mso-style-noshow:yes;
        mso-ansi-font-size:10.0pt;
        mso-bidi-font-size:10.0pt;
        font-family:Arial;
        mso-ascii-font-family:Arial;
        mso-hansi-font-family:Arial;
        mso-bidi-font-family:Arial;
        color:windowtext;}
span.SpellE
        {mso-style-name:"";
        mso-spl-e:yes;}
span.GramE
        {mso-style-name:"";
        mso-gram-e:yes;}
@page Section1
        {size:612.0pt 792.0pt;
        margin:72.0pt 90.0pt 72.0pt 90.0pt;
        mso-header-margin:35.4pt;
        mso-footer-margin:35.4pt;
        mso-paper-source:0;}
div.Section1
        {page:Section1;}

-->
</style>
<!--[if gte mso 10]>
<style>

/* Style Definitions */
table.MsoNormalTable

        {mso-style-name:"Table Normal";
        mso-tstyle-rowband-size:0;
        mso-tstyle-colband-size:0;
        mso-style-noshow:yes;
        mso-style-parent:"";
        mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
        mso-para-margin:0cm;
        mso-para-margin-bottom:.0001pt;
        mso-pagination:widow-orphan;
        font-size:10.0pt;
        font-family:"Times New Roman";}

</style>
<![endif]-->
</head>

<body lang=EN-US link=blue vlink=purple style='tab-interval:36.0pt'>

<div class=Section1>

Dear,<o:p></o:p>

<o:p> </o:p>

Since a few weeks I am tuning a big conversion batch written in PL/SQL (millions of lines of code split over 7 batches)<o:p></o:p>

When the job is running, certain batches stop with ORA-1555 : Snapshot too old. Other batches run well till the end.<o:p></o:p>

<o:p> </o:p>

Bizarre is that not always the same job stops.<o:p></o:p>

<o:p> </o:p>

When I do a trace I see nothing. With a normal trace I am pretty sure that I will never see it.<o:p></o:p>

Rollback segments are rarely used. So making the rollbacks bigger or smaller is not the solution.<o:p></o:p>

They also tried to change the commit rate. That was not the solution.<o:p></o:p>

<o:p> </o:p>

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
<o:p></o:p>

<span
style='font-size:10.0pt;font-family:Arial'>since a week it stops again with the same annoying error.<o:p></o:p>

<o:p> </o:p>

After that I put an event in the init.ora
file : event = "1555 trace name processstate forever, level 10"<o:p></o:p>

A trace file was generated but I could not find the error in the trace file.<o:p></o:p>

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<o:p></o:p>

<span
style='font-size:10.0pt;font-family:Arial'>is provoking the error.<o:p></o:p>

<o:p> </o:p>

I already looked at the batches and I have identified in 5 of them a “fetch across commit”.<o:p></o:p>

Still they have the error. But in the 2 remaining I can not find this.(surely the 2 biggest ones, nice !)<o:p></o:p>

<o:p> </o:p>

So my question is :
<o:p></o:p>

<o:p> </o:p>

How can I know where in the code the error is generated ?<o:p></o:p>

Must I change the definition of the event ?
(I know there are other options but I can not find them right away)<o:p></o:p>

Should I use DBMS_PROFILER ? (it generates massive files !)<o:p></o:p>

Must they write exceptions everywhere in their code ?<o:p></o:p>

<o:p> </o:p>

Can somebody help me ?<o:p></o:p>

<o:p> </o:p>

Please do not send me an explanation of the “snapshot too old” error. I wake up with it and I go asleep with it.<o:p></o:p>

<o:p> </o:p>

Patrick<o:p></o:p>

         &n bsp;  <o:p></o:p>

</div>

</body>

</html>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: dgoulet_at_vicr.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).

Received on Tue Jan 07 2003 - 15:05:26 CST