Re: Troubleshooting ORA-01555 while using AUM

Tech Geek wrote:

> 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

I will be interested in see what answers you get from those more knowledgeable than I. But until then ... two quick questions.

1. Does your SQL using incremental commits?
2. Have you looked at the UNDO size recommendations provided by OEM?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)