"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1078077751.452081_at_yasure...
> 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?
Thanks Daniel,
- No we are not using incremental commits.
- OEM indicates that I need about 8G of space to maintain an
undo_retention of 2 hours, and I have 13G space.
Received on Sun Feb 29 2004 - 18:32:23 CST
