Re: Delayed block cleanout causing ora-1555?

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Fri, 02 Mar 2007 19:10:19 GMT
Message-ID: <vE_Fh.33$nU5.31@trnddc04>

Mark D Powell wrote:
> On Mar 1, 2:06 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:

>> Chuck schreef:> Oracle 9.2.0.7
>>
>>> Is there any way determine authoritatively if an ORA-1555 "snapshot too
>>> old" error was caused by delayed block cleanout? I suspect it is
>>> happening on overnight processing but am not 100% sure. The query starts
>>> at 2am and failed at 3:30. Undo retention is 7 hours so that's not the
>>> problem, and a query of v$undostat for that time period shows only about
>>> 1g of undo being written during that time. The undo tablespace is 4g.
>>> TIA

>
> Chuck, do you have enough undo allocated to retain 7 hours of undo
> data? How much undo was being generated during this time period?
>
> You can query v$undostat for the answers to my questions.
>
> HTH -- Mark D Powell --
>

Based on 7 hours retention, the most undo we should ever need is about 1g. We have 4 allocated to the tablespace. This is what's confusing me.

Here is the query I used to determine the max undo written over a 7 hour period.

SELECT
  begin_time,
  SUM (undoblks) OVER
    (ORDER BY begin_time
    RANGE BETWEEN (7 / 24) PRECEDING AND 0 FOLLOWING     ) * 8 / 1024 undo_mb
    FROM v$undostat
ORDER BY 2 DESC; Received on Fri Mar 02 2007 - 13:10:19 CST