Archive for the ‘Explain Plan’ Category

Oracle’s Cardinality Hint, Profiles, Outlines and SQL Baselines

November 19th, 2009
This is more of a bookmark for a future blog post on using the Cardinality hint. This post by Greg Rahn give some example and I posted in the comments some more detailed examples:
Here is another excellent post by Kerry Osborne on SQL Baselines:
which made me ask in the comments why one would want to use Outlines vs Profiles vs Baselines, in particular what advantages if any Profiles have.

Interesting question. I had an online dialog with a guy name Randolf Geist a year ago or so where he convinced me to look closer at SQL Profiles. Up until that point I had only seen SQL Profiles that were created by the SQL Tuning Advisor and I was not very impressed. They tended to go sour after a little time because of the OPT_ESTIMATE hint, but I digress. The answer is yes, I think SQL Profiles have a couple of advantages over Outlines at this point.

1. They have the ability to be used on multiple statements (ones that are the same except for literals) using the force matching signature bit.

2. They are newer. I’m starting to worry about Outlines getting buggy due to lack of attention. Documentation says they are deprecated and although they continue to work in 11g, I am a bit leery of continuing to use them at this point. (the whole hint based mechanism is tricky enough as it is).

11gR2 includes a procedure to migrate Outlines to Baselines by the way (DBMS_SPM.MIGRATE_STORED_OUTLINE). At any rate, I am not implementing Outlines at this point.

Explain Plan

Explain Plan vs Real Plan on Oracle

November 17th, 2009

A good part of my presentation Friday at NoCOUG was on explain plan command being an estimate of the actual execution plan. Now on the Oracle-L forum list, I see the same question being asked “Under what conditions, could the plan I get out of ‘explain plan’ differ from what I get from running the sql statement?” and Tanel Poder giving a succinct clear list of reasons:

main reasons:
1) The optimizer statistics the EXPLAIN PLAN ends up using are different from the statistics the other session ended up using
2) Explain plan does not use bind variable peeking thus will not optimize for current bind variable values
3) Explain plan treats all bind variables as VARCHAR2, thus you ma have implicit datatype conversion happening during the plan execution, (meaning to_char,to_number functions are added around variables/columns) and this for example may make optimizer to ignore some indexes if you get unlucky.
with the caveat:
Little correction to point number 3 – of course explain plan doesn’t really execute the plan so the implicit datatype conversion you see is in the explained plan only, but if you actually execute the statement (with correct bind datatypes) then there’s no implicit datatype conversion. And that’s where the difference comes from…
From the presentation Friday at NoCOUG:
more examples on previous blog entry

Explain Plan

Real Time SQL Monitoring

November 13th, 2009

here is a feature that I’m excited about:
Real Time SQL Monitoring
Active Session History has added fields that can track the execution id of a statement as well as the row source (ie line in the explain plan) that the query is executing. For long running queries this can be a way to see where the query is at as well as seeing which row sources are more expensive and for short running queries one can build up a statistical picture of what row sources are expensive.
11g ASH has added the fields



that can track and distinguish different executions of the same sql.

11g ASH has added the fields




That can track the particular line in the execution plan that is executing

ASH, Explain Plan, oem

Oracle DBA Views !

October 22nd, 2009

Ever wonder why some of those DBA_ views take so long to execute?
here is the view expansion of a query from SYS.DBA_ALL_TABLES joined to

Simple huh?!

dboptimizer, embarcadero, Explain Plan, vst

Outlines from DBMS_XPLAN

September 4th, 2009
dbms_xplan.display_cursor will output the hints for a plan.
These hints can then be taken and used in an outline for plan stability:

Explain Plan

dbms_xplan.display_cursor – what are all those arguments?

September 4th, 2009

Nice table on

and some more discussion on

Explain Plan

Diff’ing Explain Plans

September 4th, 2009

Diff’ing explain plans is difficult and tedious.>Here is a new package function from Oracle for diff’ing explain plans
Of course I have my own ideas, some expressed here

but I will be outlining more visual ideas for sql plan analysis over this coming year.I have some exciting designs we are implenting in DB Optimizer (

Explain Plan, graphics

Explain Plans

September 3rd, 2009

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
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:

Explain Plan

Alberto Dell’Era’s XPLAN

September 3rd, 2009

Check out Alberto Dell’Era’s SQL query analyzer – lots of good info like this compact table of indexes availble on the query:

--------------------------------------02.|ColName     |1|2|3|4|5|P|U1|U2|R1|03.--------------------U-U------------04.|X           |1|1|2|1| |1|  |2 |  |05.|PADDING     | | |1|2|1|2|R1|1 |  |06.|RR          | | | | | | |  |  |1 |07.|SYS_NC00004$|2| | | | | |  |  |  |08.|SYS_NC00005$| |2| | | | |  |  |  |

Explain Plan, Optimizer, sql tuning

SQL Joins

June 26th, 2009

Growing up on Ansi 89 and working at Oracle for 10 years, some of the the cleaner SQL join notation seems new to me. Here is a great write up on joins and join notation:

Explain Plan, sql tuning