RE: Undo Usage and Read consistency - ORA-1555

From: Randy Johnson <oraclelist_at_sbcglobal.net>
Date: Tue, 14 Jul 2009 11:44:58 -0500
Message-ID: <003301ca04a2$6c846730$458d3590$_at_net>



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).  

   -Randy  

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
To: nancy_iles_at_hotmail.com
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.  

Jared    

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4242 (20090714) __________  

The message was checked by ESET NOD32 Antivirus.  

http://www.eset.com

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jul 14 2009 - 11:44:58 CDT

Original text of this message