Re: Optimizer issue

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@xxxxxxxxxxxx> 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

Other related posts: