Re: Troubleshooting ORA-01555 while using AUM

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);
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;
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.

 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 - 13:57:06 CST