Re: An ancient mystery

All,

Thank you for your excellent suggestions.

• No stats on any of the tables involved in this query.
• Timestamps on the index objects for this table are really old.
• No bind variables
• No partitioning. It is a mystery, but at least I can assure my people that the leading Oracle experts on the planet have examined the situation.

Thanks again,
Dennis Williams

On Fri, Nov 21, 2008 at 2:41 AM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> Is partitioning or another feature that implies the cost based
> optimiser in use?
>
> On 11/21/08, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
> > List,
> >
> > The situation:
> >
> > Oracle 8.1.7.4 database on Solaris 8 (soon to be upgraded to 10g)
> > Rule-based Optimizer
> >
> > A query which has run for years using an indexed access to a very large
> > table (maybe 100 million rows)
> > suddenly decides to use a full-table scan, shutting down a critical
> business
> > process.
> >
> > Then after several hours, it switches back to using the index.
> > All concerned claim that nothing changed before or after.
> >
> > Needless to say the business users are nervous. They think maybe the
> > database grew beyond some limit.
> >
> > Can anyone think of an explanation?
> >
> > Thanks,
> > Dennis Williams
> >
>
> --
> Sent from Google Mail for mobile | mobile.google.com
>
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

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