RE: Undo Usage and Read consistency - ORA-1555
Date: Tue, 14 Jul 2009 11:44:58 -0500
I agree with Jared. The first thing that went through my mind was whether or not CONFIRMATION_NO is indexed. You might also consider doing a 10053 trace which will show you how the optimizer came to pick the plan it did.
If the statement seems to run fast sometimes and long others bind variable peeking is also a possibility (10g or later).
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jared Still
Sent: Tuesday, July 14, 2009 10:56 AM
Cc: cicciuxdba_at_gmail.com; oracle-l_at_freelists.org Subject: Re: Undo Usage and Read consistency - ORA-1555
On Tue, Jul 14, 2009 at 8:35 AM, Nancy Iles <nancy_iles_at_hotmail.com> 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.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4242 (20090714) __________
The message was checked by ESET NOD32 Antivirus.Received on Tue Jul 14 2009 - 11:44:58 CDT