Mark raises a very valid point. I wish I had thought of it: How do you know that the plan changed? Is there a trace on all the time? Oracle 8i does not have the v$sql_plan views that 9i+ versions have.
At 04:49 PM 11/21/2008, Mark W. Farnham wrote:
If the query has run for years, I’m impressed with your operations staff for keeping the machines up that long! (rimshot).I don’t really think a running query can change plans, how you would know it had, or how you would know it changed back (another rimshot).But seriously folks… Is this a single table query?Does validate index (which I don’t believe leaves any timestamp evidence behind) lock anything? I think it might (if not with recent releases, then with 8.1.7.4). Easier for you to check than for me to check.If that doesn’t get you an answer (the validate index text could still be in the shared pool), then back to the question about the other tables in the query. Someone adding an index to one of them could make that table the one using an index and result in an fts on the big table. Then they drop that index and the evidence is gone. The intent of the index temporarily there might have even been a hueristic tie with the desired index and rule has no idea which table is bigger. So look for the guy with the sheepish grin who had his unrelated query on one of the other tables in the query run much faster that day and who then dropped his neat new index when he heard the hubbub. Or, less cynically, an index created nightly for a batch run and dropped because an ancient dba knew it would have this effect on the “daytime” query ran later than usually scheduled.Good luck. I hope you discover the answer to your mystery.
Regards Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com
