Explain Plans

Explain Plans can be had via

• Explain Plan (version ? ancient)
• Trace Files (starting 7.3)
• Autotrace (starting 8.1.7)
• V$SQL_PLAN (starting 9iR2)

I was wondering which methods are dependable and which are not. Classically an explain plan can easily differ between the analyzer's session (such as a DBA's session) and the session being analyzed (like an end user session) . One of my first bug I logged back at Oracle in versin 6 on tkprof explain giving a different plan than actually happened in the trace. Now that there is v$sql_plan which contains the "live" plans for executed SQL, I was wondering if the other method had caught up. It doesn't seem so. It seemes that Autotrace uses EXPLAIN PLAN command even through 11gR1 despite the fact it does sampling.

Here is some info on Jonathan Lewis' scratchpad

http://jonathanlewis.wordpress.com/testing-autotrace/#comment-34301

Example of wrong plan because of bind variables:

http://www.jlcomp.demon.co.uk/wrong.html

Another blog post on Explain Plan giving wrong plan
http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/

Example from Tom Kyte where Explain Plans assuming bind vars are characters even if they are defined as numbers

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:503512000346581816

An example of autotrace which uses Explain Plan giving the wrong execution plan:

Full presentation available on:

http://www.perfvision.com/ftp/Nocoug_Nov_2009_sqltuning_nointro.ppt