RE: Undo Usage and Read consistency - ORA-1555
From: Nancy Iles <nancy_iles_at_hotmail.com>
Date: Tue, 14 Jul 2009 17:04:24 -0500
Message-ID: <SNT121-W212E6CE37B7D573FB26DE1F8230_at_phx.gbl>
> From: jkstill_at_gmail.com
> Date: Tue, 14 Jul 2009 08:56:04 -0700
> Subject: Re: Undo Usage and Read consistency - ORA-1555
> To: nancy_iles_at_hotmail.com
> CC: cicciuxdba_at_gmail.com; oracle-l_at_freelists.org
>
> On Tue, Jul 14, 2009 at 8:35 AM, Nancy Iles> wrote:
>
>
>
>
> We have sporadic ora-1555 on an exceptionally simple statement that occurs frequently in the application. How can you analyze why this tiny, simple statement is causing an ORA-1555? The statement is:
>
>
>
> SELECT RESV_NAME_ID , RESORT FROM RESERVATION_NAME WHERE CONFIRMATION_NO = :1
>
>
>
> Is an appropriate index being used by the SELECT?
>
> Normally I would expect to see something such as CONFIRMATION_NO to
> be accessed via indexed lookup, but it doesn't hurt to confirm that.
>
>
>
> Barring that, have you tried running a 10046 trace on that statement so
> you can see what is taking place to satisfy the query?
>
> Lots of guessing here, because there isn't really much to go on in this post.
>
>
>
> Jared
>
Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290-- http://www.freelists.org/webpage/oracle-l Received on Tue Jul 14 2009 - 17:04:24 CDT
Date: Tue, 14 Jul 2009 17:04:24 -0500
Message-ID: <SNT121-W212E6CE37B7D573FB26DE1F8230_at_phx.gbl>
Yes, the there is an index on CONFIRMATION_NO and I have found that _OPTIM_PEEK_USER_BINDS=FALSE on all 6 RAC nodes. So, now on to research the other suggestions! Thanks to everyone for your input.
Nancy
> From: jkstill_at_gmail.com
> Date: Tue, 14 Jul 2009 08:56:04 -0700
> Subject: Re: Undo Usage and Read consistency - ORA-1555
> To: nancy_iles_at_hotmail.com
> CC: cicciuxdba_at_gmail.com; oracle-l_at_freelists.org
>
> On Tue, Jul 14, 2009 at 8:35 AM, Nancy Iles> wrote:
>
>
>
>
> We have sporadic ora-1555 on an exceptionally simple statement that occurs frequently in the application. How can you analyze why this tiny, simple statement is causing an ORA-1555? The statement is:
>
>
>
> SELECT RESV_NAME_ID , RESORT FROM RESERVATION_NAME WHERE CONFIRMATION_NO = :1
>
>
>
> Is an appropriate index being used by the SELECT?
>
> Normally I would expect to see something such as CONFIRMATION_NO to
> be accessed via indexed lookup, but it doesn't hurt to confirm that.
>
>
>
> Barring that, have you tried running a 10046 trace on that statement so
> you can see what is taking place to satisfy the query?
>
> Lots of guessing here, because there isn't really much to go on in this post.
>
>
>
> Jared
>
Lauren found her dream laptop. Find the PC that’s right for you. http://www.microsoft.com/windows/choosepc/?ocid=ftp_val_wl_290-- http://www.freelists.org/webpage/oracle-l Received on Tue Jul 14 2009 - 17:04:24 CDT