This note is a quick summary of a costing oddity that came to light after a twitter conversation with Christian Antognini yesterday. First a little test script to get things going:
October 16, 2013
February 13, 2013
That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.
There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from 10.2.0.3 to 22.214.171.124 – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):
January 17, 2013
Here’s a very long post (which is mainly an example) demonstrating a little bug in the “explain plan” functionality. It’s a variation of a bug which I thought had been fixed in 11g, but it still appears in some cases. Take a look at this execution plan, which comes from explaining “select * from dba_tab_cols” – the bit I want to emphasise is in lines 1 to 10:
January 4, 2012
Here’s a follow-up to a post I did some time ago about estimating the size of an index before you create it. The note describes dbms_stats.create_index_cost() procedure, and how it depends on the results of a call to explain plan. A recent question on the OTN database forum highlighted a bug in explain plan, however, which I can demonstrate very easily. I’ll start with a small amount of data to demonstrate the basic content that is used to calculate the index cost.
August 16, 2011
This little note on how dbms_xplan behaves was prompted by a very simple question on OTN which raised a point that I often manage to forget (temporarily). I’ve chosen to explain it through a little demonstration.
June 8, 2011
Here’s a quick tutorial in hinting, promped by a question on the OTN database forum.
The OP has a hash semi-join and Oracle appears to be ignoring a hint to use a nested loop:
May 11, 2011
One of the questions that pops up on the internet from time to time is the one about finding SQL that’s doing full tablescans.
Since the appearance of the dynamic performance view v$sql_plan in Oracle 9i this has been quite easy – provided you can check memory soon enough. A query like the following will give you the sql_id (hash_value, if you’re still on 9i) and child_number of any query that has a full tablescan (or index fast full scan) in its execution path.
April 15, 2010
I often make a fuss about making sure that people include the predicate section when looking at execution plans. Here’s another example demonstrating why it can be so helpful. We start with an SQL statement that happens to have a large number of child cursors – and every child cursor has exactly the same plan_hash_value (which almost guarantees they all have the same execution plan):
January 27, 2010
Nothing terribly important or exciting in this post, but you’ve probably seen the “Notes” section at the end of an execution plan from time to time. The notes are extracted from the “other_xml” column of (usually) the first line of the execution plan data using calls to the extractvalue() XML function.
It’s interesting to see from a trace of a call to dbms_xplan.display_cursor() how the number of things that can appear as notes is slowly growing over recent versions of Oracle. The statements below are extracted from the trace files for 10.2.0.3, 126.96.36.199, and 188.8.131.52 in that order: (more…)
January 25, 2010
When using “explain plan” with “dbms_xplan.display()”, have you ever seen the following note at the end of the output:
Note ----- - 'PLAN_TABLE' is old version
It’s something I see surprisingly frequently, travelling as I do to many different sites, but it’s usually easy to deal with. (more…)
May 5, 2009
I’ve written several posts about dbms_xplan, and the display_cursor function in 10g. One of the nice feature of this function is that it is a “pipelined” function – which means that you can treat the call to the function as a “virtual table”. Here’s an example (run on 10.2.0.3) of what this allows us to do: (more…)
December 3, 2008
Whenever you look at an execution plan, you must look at the predicate section (introduced in 9i) before you start trying to work out what’s gone wrong. Here’s an example (stripped down to the basic issue from a more complex query) that shows the importance of this check.
March 6, 2008
Some time ago, I wrote a note about using the packaged function dbms_xplan.display_cursor().
I’ve just seen a note on Rob van Wijk’s blog where he investigates many more parameter values that can be used with the function.
January 10, 2008
In 10g, the code to generate execution plans changed dramatically, as did the SQL used by the dbms_xplan package to report execution plans from the plan table. In 9i, the indentation for the lines of a plan was calculated by the reporting query as the level from a ‘connect by’ query; in 10g the explain plan utility itself calculates the level and populates a column called depth in the plan table.
April 26, 2007
Okay, so the title is pretentious; but I thought it was a snappy summary of this item [until someone pointed out that Heisenberg's Uncertainty Principle is not about measurement error].
I’ve blogged before about the improved features in 10g of the dbms_xplan package, in particular the display_cursor() procedure, with its option for displaying rowsource execution statistics.