Re: Optimizer issue

From: Kirtikumar Deshpande <kedeshpande_at_yahoo.com>
Date: Fri, 19 May 2006 16:46:38 -0700 (PDT)
Message-ID: <20060519234638.35446.qmail@web50101.mail.yahoo.com>

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.

Regards,

• Kirti
  • Riyaj Shamsudeen <rshamsud_at_jcpenney.com> wrote:

> 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 http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 19 2006 - 18:46:38 CDT