Did someone perhaps turn on sql trace for the session, invalidating the plans ? Don't remember off the top of my head if dbms_system was already there in 8i, but I'd assume there was a way of doing it ? Stefan ========================= Stefan P Knecht Senior Consultant Systems Engineering OPITZ CONSULTING Schweiz GmbH Seestrasse 97 CH-8800 Thalwil Mobile +41-79-571 36 27 stefan.knecht@xxxxxxxxxxxxxxxxxxx http://www.opitz-consulting.ch OCP 9i/10g SCSA SCNA ========================= On Fri, Nov 21, 2008 at 4:05 AM, Dennis Williams < oracledba.williams@xxxxxxxxx> 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 >