RE: An ancient mystery
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracledba.williams@xxxxxxxxx>, "'Oracle Discussion List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 21 Nov 2008 18:49:40 -0500
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,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dennis Williams
Sent: Thursday, November 20, 2008 10:05 PM
To: Oracle Discussion List
Subject: An ancient mystery
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
Other related posts:
