Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer issue

Re: Optimizer issue

From: Kirtikumar Deshpande <>
Date: Fri, 19 May 2006 16:46:38 -0700 (PDT)
Message-ID: <>

Hi Riyaj,

The case we encountered was not due to gathering statistics.

Vendor used index_desc hint to "derive" a result set.

There was a block corruption on the index and it was recreated with a different name while the DBA worked with Oracle Support to analyze the problem. Of course, the index hint was useless and the SQL produced unwanted results.


> Quoting Manjula:
> >>and some data would not be pulled up in the application. Is that
> possible?
> Huh ? Generally not possible. But, I know of one exception. I think
> Kirti Deshpande encountered this: One of the application was relying
> upon an index order to return the rows in a specific order. Optimizer
> selected a different index, after collecting statistics, affecting the
> functionality. Specifically, if the code is also interested in top N
> rows with this strategy, then incorrect (from the application point of
> view) will be pulled in to the application, causing user irritation.
> But, the problem here is that incorrect and potentially treacherous
> strategy. Unless 'order by' clause is used, RDBMS will return rows
> anyway it sees fit. So, you might want to ask them to provide more
> details as to exactly why collecting statistics will not work and ask
> for code/strategy that will break.
> --
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA (ver 7.0 - 9i)
> Allocation & Assortment planning systems
> JCPenney
> Manjula Krishnan wrote:
> > I have an application on Oracle 9i. The database (designed by the
> > vendor) was set with optimizer_mode=CHOOSE. A week after we went live
> > with the application, performance started degrading. I discovered that
> > statistics were missing and built them. Immediately the application
> > problems were resolved.
> >
> > The vendor claims that with the statistics, it would not use the right
> > indexes and some data would not be pulled up in the application. Is
> > that possible?
> >
> > Thanks,
> >
> > Manjula
> > The information transmitted is intended only for the person or entity to
> which it is addressed and may contain confidential and/or privileged
> material. If the reader of this message is not the intended recipient,
> you are hereby notified that your access is unauthorized, and any review,
> dissemination, distribution or copying of this message including any
> attachments is strictly prohibited. If you are not the intended
> recipient, please contact the sender and delete the material from any
> computer.

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
Received on Fri May 19 2006 - 18:46:38 CDT

Original text of this message