Re: Delayed block cleanout causing ora-1555?

On Mar 2, 11:10 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> 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.

Maybe it has nothing to do with the undo you are generating, but rather the undo generated by others. Maybe you simply need to kill off any old sessions before you start the nightly processing, the others might have been hanging around for more than 7 hours, as soon as you hit one of those blocks oracle can't reconstruct it.

I can't seem to find the one I once used just now, but there are scripts floating about that estimate how big your undo space should be, and for one OLTP db it told me 40G. For me to keep undo at 10G, I need to have a nightly kill program (which I use before exports anyways, but I saw ORA-1555's on Mondays after people would run MRP or something over the weekend and not log off). Actual data (as in an export) is about 30G.

>
> 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
The volume is irrelevant, the time other sessions modified the blocks your process needs and whether the volume has pushed them out is relevant.

jg

--
@home.com is bogus.
I know!  Let's get rid of recycleable light bulbs and replace them
with mercury containing light sources that need special hazardous
material disposal and then make it difficult to dispose of hazardous
material.  That ought to save energy and help the environment.