Re: Question on UNDO tablespace retention guarantee

From: Seth Miller <sethmiller.sm_at_gmail.com>
Date: Wed, 27 Mar 2019 12:50:03 -0500
Message-ID: <CAEueRAVQscuKHvBEPWEnC4ugn5yC=Vh6+G5BO4eh8x-qM+Mj1A_at_mail.gmail.com>



Snapshot too old errors are not always caused by a lack of available undo blocks. The best way to diagnose this is to reproduce the problem and see if adding space to the undo tablespace fixes the problem. If it doesn't, guaranteed retention will not necessarily fix the problem.

Seth

On Wed, Mar 27, 2019 at 4:22 AM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:

>
> I thought I'd written something about this that I could point you to - but
> all I could find by searching my laptop was a copy of a presentation I gave
> in 2004 which said that if your session can't allocate any more undo space
> then it will crash with Oracle error ORA-30036: unable to extend segment by
> {value} in undo tablespace '{name}'. It didn't have any comments about when
> the extents it had used might be considered freeable available after the
> crash - and a very big transaction might take a very long time to rollback
> and allow its undo space to be reusable.
>
>
> Things may have changed since then, of course, and (as of 2004) if you had
> enabled a resumable session it would suspend for the resumable timeout
> rather than crashing - but then every segment that still had some free
> space in its current extents would presumably fill and all the other
> sessions would either crash or suspend, depending on whether they were
> resumable or not). Also, if the session were resumable and some other undo
> space became available because other sessions committed in a pattern that
> freed some extents, I assume your transaction could acquire those extents
> and continue a little longer.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Anurag Verma <anuragdba_at_gmail.com>
> Sent: 27 March 2019 02:03
> To: ORACLE-L
> Subject: Question on UNDO tablespace retention guarantee
>
>
> If I use the below to avoid "snapshot too old" errors, will it cause any
> performance issues to DML statements ?
>
> alter tablespace undotbs1 retention guarantee;
> --
>
> For example, if large transactions running and if it consumed all free
> UNDO space, will the new DMLs have to wait for getting free UNDO space ?
>
>
> Thanks,
> Anurag Verma
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 27 2019 - 18:50:03 CET

Original text of this message