Re: Optimizer issue

Well,
'Missing data' can come from 2 sources:

1. A bug, this can happen
2. A different ORDER that makes the data missing from the first page. If the SQL does not an ORDER BY, but relies on a plan using a given index to have the data ordered as the app wishes, using another index will make the order different, and hence the data that used to be on the first pages go the other ones, and so seem missing.

rgds

On 5/20/06, Kirtikumar Deshpande <kedeshpande_at_yahoo.com> wrote:
>
> 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
>
>
>

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