Re: Troubleshooting ORA-01555 while using AUM

"VC" <boston103_at_hotmail.com> wrote in message news:kmw0c.86423$Xp.400036_at_attbi_s54...
> Pls. see below:
>
> "Tech Geek" <Tech_Geek_at_Gawab-IHateSpam.com> wrote in message
> news:iEv0c.19155$6K.16090_at_nwrddc02.gnilink.net...
> > Hi VC,
> >
> > Comments in-line.
> >
> > "VC" <boston103_at_hotmail.com> wrote in message
> > news:mur0c.87693$4o.109763_at_attbi_s52...
> > > Hello,
> > >
> > > "Tech Geek" <Tech_Geek_at_Gawab-IHateSpam.com> wrote in message
> > > news:gno0c.10382$C65.9964_at_nwrddc01.gnilink.net...
> > > > 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.
> > >
> > > I am probably missing something since, judging by your description,
you
> > see
> > > a consistent picture.
> > >
> > > Here's what I think was happening:
> > >
> > > The undo segment ran out of space _and_ the undo tablespace did not
have
> > any
> > > more space to allow for the segment growth . Oracle went possibly
> through
> > > these stages:
> > >
> > > 1. It tried to acquire some undo space from unused(expired) extents
> from
> > > another segment (the UNXPSTEALCNT was incremented);
> >
> > -- Here is the difficult part, values for almost all of columns
> especially -
> > UNXPSTEALCNT, EXPSTEALCNT
> > are zeros.
>
> OK, I see now... Your original message was confusing because you wrote:
> "but am I always
> seeing non-zeros values in unxpstealcnt and nospacerrcnt columns"

• My bad. Please read as zeros, instead of non-zeros.

>
> If the values are zeroes and you are experiencing the 1555 error, then
the
> above columns in v$undostat view are indeed useless.
>
> Could you supply more information about under exactly what circumstances
> the 1555 is happening in your case ?

This database is being used by end-users for short transactions 24x7 and some adhoc batch jobs, which update/insert new records and also does some SELECTS. Mostly, the batch jobs and the daily export job run at time of the day, ends up with Ora-1555.

Once again, thanks for your time.

Regards
TG

>
> VC
>
>
Received on Sun Feb 29 2004 - 20:01:38 CST