RE: An ancient mystery

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

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