top of page
  • Writer's picturekyle Hailey

Explain Plans

Explain Plans can be had via

  1. Explain Plan (version ? ancient)

  2. Trace Files (starting 7.3)

  3. Autotrace (starting 8.1.7)

  4. 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

Example of wrong plan because of bind variables:

Another blog post on Explain Plan giving wrong plan

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

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

Full presentation available on:

2 views0 comments

Recent Posts

See All


bottom of page