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:
http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/
Here is another excellent post by Kerry Osborne on SQL Baselines:
http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-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.
Kyle Hailey Explain Plan
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
http://db-optimizer.blogspot.com/2009/09/explain-plans.html
Kyle Hailey Explain Plan
here is a feature that I’m excited about:
Real Time SQL Monitoring
http://askdba.org/weblog/2009/09/11gr2-monitoring-real-time-sql-plan-execution-from-oracle-enterprise-manager/
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
SQL_EXEC_ID
SQL_EXEC_START
that can track and distinguish different executions of the same sql.
11g ASH has added the fields
SQL_PLAN_LINE_ID
SQL_PLAN_OPERATION
SQL_PLAN_OPTIONS
That can track the particular line in the execution plan that is executing
Kyle Hailey ASH, Explain Plan, oem
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
SYS.DBA_OBJECTS
Simple huh?!
Kyle Hailey dboptimizer, embarcadero, Explain Plan, vst
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:
http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
Kyle Hailey Explain Plan
Nice table on
http://www.psoug.org/reference/dbms_xplan.html
and some more discussion on
http://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/
Kyle Hailey Explain Plan
Diff’ing explain plans is difficult and tedious.>Here is a new package function from Oracle for diff’ing explain plans
http://optimizermagic.blogspot.com/2009/09/whats-changed-between-my-new-query-plan.html
Of course I have my own ideas, some expressed here http://sites.google.com/site/youvisualize/sql-topology
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 (http://oraclemonitor.com)
Kyle Hailey Explain Plan, graphics
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
Kyle Hailey Explain Plan
Check out Alberto Dell’Era’s SQL query analyzer – lots of good info like this compact table of indexes availble on the query:
http://www.adellera.it/blog/2009/06/07/optimizing-sql-statements-with-xplan/
--------------------------------------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| | | | | | | |
Kyle Hailey Explain Plan, Optimizer, sql tuning
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:
http://blog.mclaughlinsoftware.com/oracle-sql-programming/basic-sql-join-semantics/
Kyle Hailey Explain Plan, sql tuning
recent comments