Re: Troubleshooting ORA-01555 while using AUM

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. I exercised utmost caution to look for these values only during the duration when the errors (1555) occurred. Rows are maintaiend for each minute duration. Just FYI, I did notice that the value in the column v$undostat.SSOLDERRCNT are incremented appropriately.

> 2. If the transaction needed more space and no expired extents were
> available, then Oracle 'stole' unexpired segments ( EXPSTEALCNT ++);
> Here, a 1555 could have happened because some unexpired segments
were
> over-written;

• Again, information provided for 1 above is still valid in this case too. An added piece of information is that I have about 13G of space and HWM for this Tablespace never passed 5G.

> 3. If, after steps (1) and (2), the transaction still needed more space,
> Oracle tried to autoextend the undo tablespace datafile(s) and failed
> causing the NOSPACEERRCNT to increment.

• There are only zeros for the column NOSPACEERRCNT. This will not be situation, if I am about 8G of free space - never used.

Please let me know if you need any additional information.

I appreciate your time and help.

Thanks
TG

>
>
> 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.
>
> I believe the 1555 happened because of step (2) above.
>
> > OR
> > - When there is no undo space.
> >
>
> This should cause ORA-1562 (unable to extend) rather than ORA-1555.
>
> > In any of the above situations, I should see non-zeros values in any of
> the
> > above mentioned columns.
> >
>
> But you do see them according to your description...
>
>
> > 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.
>
> Could you elaborate on the word 'incorrect' ?
>
> Any kind of extent 'stealing' means that either you want to fix your
> transactions (fetch across commit and such) or just need more undo space
to
> accomodate the longest running query.
>
>
> >
> > Thanks in advance for your time and help!
> >
> > TG
> >
> >
> >
>
> VC
>
>
Received on Sun Feb 29 2004 - 18:40:46 CST