Index not been used

A strange occurrence happened on my production database today:

DB version 8.0.4 on Solaris 2.6. The load on the server went suddenly up. There were a number of processes that pilled up querying the database through a standard query, which usually end up instantaneously. The load on the unix server skyrocketed. The query queries a view which is a join of five tables (one table being joined with itself). I did explain plan (rule based optimizer) and so that the query is doing a full table scan on that one (BIG) table that joins to itself. I shutdown the application that is generating the query, to bring the pressure down and started investigating whether there is an index that suddenly got dropped. While I was doing that the load naturally went to down to its normal values. When I could not find a missing index I reran the explain plan (again RULE base optimizer and nothing got changed - no analyze run, etc.) and it gave the good old plan which was using the index that existed all the time. I restarted the app and everything is running OK now (knock, knock)

Has anybody experienced similar behavior - that for whatever reason oracle with rule based optimizer decides temporarily not to use an index but to do a full table scan, and than suddenly decides again that it wants to use the index again. BTW, the first thing I checked (before the explain plan) that all the indexes were valid, there were no locks on the system. Any ideas why this might have happened ?

Thanks. Received on Wed Nov 15 2000 - 16:46:39 CST