Re: Troubleshooting ORA-01555 while using AUM

The most significant bit of the results is this:

BEGIN END MAXQUERYLEN UNXPSTEALCNT EXPSTEALCNT SSOLDERRCNT NOSPACEERRCNT

----------------- ----------------- ----------- ------------ ----------- ---
27-02-04 15:45:40 27-02-04 15:55:40         193            0           0
2             0
27-02-04 15:35:40 27-02-04 15:45:40         206            0           0
0             0
27-02-04 15:25:40 27-02-04 15:35:40         102            0           0
0             0

You have 9.2.0.4, retention time of 7200 seconds, and loads of free space in the undo tablespace.

This output says that in the 20 minutes leading up to the two snapshot too old errors, your longest running queries were a couple of hundred seconds. If you had been running a query of 7200 second, then a load of the preceding rows from v$undostat would have shown the maxquerylen gradually increasing towards 7,200.

So you seem to be getting snapshot too old when it should not be happening.

Are you using LOBs, and is it possible that the failures are due to LOB chunk overwrites. I haven't checked whether the LOB error is reported at the top level as 1555, or as the underlying 22924 - this could be a cause of confusion. I also don't know if a LOB snapshot too old would appear in v$undostat (I doubt it, as it's not caused by destruction of undo).

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof
  Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"Tech Geek" <Tech_Geek_at_Gawab-IHateSpam.com> wrote in message
news:UIP0c.25576$6K.5845_at_nwrddc02.gnilink.net...


>

> Thanks a lot for wonderful summary.  Here is the extract from v$undostat,

I


> am including only those which are more relevant to the problem. I included

> about 5 lines before the problem and after the problem.  I am sure, that

the


> following extract information will not show nicely formatted,if it is so,

> please check the values in the column five, which denotes values for the

> column SSOLDERRCNT (it has non-zero values of 2, 1, 4).

>