Re: snapshot too old - between dates vs and

On May 18, 11:47 am, DA Morgan <damor..._at_psoug.org> wrote:
> ton de w wrote:
> > Hello,
> > I have been on the receiving end of "ORA-01555 snapshot too old
> > rollback segment number 1 _SYYSSMU1$ too small. a couple of times
> > while running queries against a 9i db.
>
> > It seem this is because my query takes so long that, cos Oracle gives
> > me a consistent read, there is no room for the rollback stuff.
>
> > OK fair enough - I should speed up my query.
>
> > two questions therefore:
> > Does using between rather than and help?
> > LastEvent between to_date('16/05/2007 00:00:00','DD/MM/YYYY
> > HH24:MI:SS')
> > and to_date('17/05/2007 23:59:59','DD/MM/YYYY HH24:MI:SS')
> > vs
> > LastEvent <to_date('17/05/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
> > and
> > LastEvent >= to_date('16/05/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
>
> > does putting say integer comparisons first in my where clause help?
>
> > does putting tests that are likely to fail towards the left in a
> > series of and clause help?
>
> > Sorry that's 3 questions!
>
> > TIA
>
> > Ton
>
> One at a time.
>
> The solution to the problem is to size your undo tablespace
> appropriately and set an appropriate undo retention time.
>
> BETWEEN and AND have nothing to do with the issue nor does your
> other suggestion. Read the concept docs and review the advise on
> Tom Kyte's asktom.oracle.com website with respect to ORA-01555.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

If the LastEvent column is indexed and the explain plan shows it is used then Daniel has provided the solution. Take a look at dba_undo_extents and v$undostat to determine if Daniel has suggested the correct solution..

Being that the size of the undo tablespace and existence of concurrent DML activity are unknows in the OP I will point out that in general the longer a query runs the longer the need to retain old versions of data in the UNDO tablespace.

No explain plan was posted either. As the query is written an index on LastEvent would be usable. If no such index exists and this is a large table then adding an index on LastEvent might speed up the query enough to avoid the 1555 error. Might.

Even with a large UNDO tablespace compared to the size of the total database if the application design/demands are bad/large enough it is possible to hit 1555 conditions in the absence of setting retention guarantee since the application can require the use of more UNDO space than realistically can be allocated.

HTH -- Mark D Powell -- Received on Fri May 18 2007 - 11:45:17 CDT