The Oracle (tm) Users' Co-Operative FAQ
Why is Explain Plan showing a different execution path from tkprof ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 26th July 2001 Oracle version(s): 7.3 - 8.1.7.0 |
| When I run explain plan for a query, the plan generated does not match the plan I find in the output when I run tkprof against a trace file containing the same SQL statement. Why not ? |
There are a several possibilities, based on the fact that tkprof shows you the path that actually took place at run time and explain plan tells you the path that would be followed now. (In fact if you do tkprof explain=, you can get both, possibly contradictory, paths in the same output file).
a) The user at runtime is not the user explaining, so the query is actually addressing different objects, despite the names being the same. (Or perhaps it is the same user, but a synonym or view has been redefined to point to a different object).
b) The optimizer mode for the run-time session is not the same as the optimizer mode for the explain session (or any one of 50 other parameters that affect the optimizer could have changed)
c) Indexes have been created or dropped between the generation of the trace file and the explain plan
d) Analyze (or dbms_stats) has been run against some of the objects in the SQL between the generation of the trace file and the explain plan
e) The SQL includes bind variables - explain plan will assume these are character variables, at run-time Oracle may have known that there were not, and therefore different coercion effects may have affected the availability of indexes.
I can't think of any more reasons at the moment, but I'm sure that there are probably a couple of other reasons.
Further reading: N/A
