Troubleshooting ORA-01555 while using AUM

Hi Gurus,

I am trying to troubleshoot the dreaded ORA-01555 - Snapshot too old errors. Here is the relevant info:

OS : Sun Solaris 2.8
Oracle: Oracle9i Rel 2 (9.2.0.4)
undo_retention = 7200 (2 hours)

My understanding after going through the oracle documentation and other white papers, documents on the subject, I understand that the values in the column v$undostat. UNXPSTEALCNT would be incremented by 1, whenever an attempt is made to obtain undo space for new transaction by stealing the unexpired extents from other transactions (committed or inactive). Also, the values in the column v$undostat.nospaceerrcnt would be incremented by 1, whenever a space request was made and there was no free space.

Given this understanding, whenever, I hit ORA-1555, I should see non-zeros in any of the two columns. I do see the value equal to the number of ORA-1555s I encounted in the column v$undostat.SSOLDERRCNT, but am I always seeing non-zeros values in unxpstealcnt and nospacerrcnt columns. This is making troubleshooting ORA-1555s tough. With AUM, the probability of getting this error can happen to most extent by any of these two situations:
- When Oracle uses the undo space of inactive but committed transactions,
which is needed by the current transaction for building a Read Consistent view.
OR
- When there is no undo space.

In any of the above situations, I should see non-zeros values in any of the above mentioned columns.

Any help in understanding this or pointers more information would be highly appreciated. My question here is, is that the information provided by v$undostat will really help us troubleshooting these errors or not. If it really does, why is that I am getting incorrect information.

Thanks in advance for your time and help!

TG Received on Sun Feb 29 2004 - 10:24:44 CST