Oracle Scratchpad

June 8, 2011

How to hint – 1

Filed under: dbms_xplan,Execution plans,Hints,Oracle,subqueries,Troubleshooting — Jonathan Lewis @ 3:00 pm BST Jun 8,2011

Here’s a quick tutorial in hinting, promped by a question (that’s no longer available) on the OTN database forum. The OP was trying to change a plan that was using a hash semi-join and Oracle appeared to be ignoring a hint to use a nested loop:


May 11, 2011

dbms_xplan reprise

Filed under: dbms_xplan,lateral view,Oracle — Jonathan Lewis @ 5:28 pm BST 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

Predicate (again)

Filed under: dbms_xplan — Jonathan Lewis @ 6:17 pm BST Apr 15,2010

I often make a fuss about making sure that people include the Predicate Information 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

Plan Notes

Filed under: dbms_xplan — Jonathan Lewis @ 7:07 pm GMT Jan 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,, and in that order: (more…)

January 25, 2010

Old plan_table

Filed under: dbms_xplan — Jonathan Lewis @ 8:27 am GMT Jan 25,2010

When using “explain plan” with “dbms_xplan.display()”, have you ever seen the following note at the end of the output:

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

Dependent Plans

Filed under: dbms_xplan,Execution plans,lateral view — Jonathan Lewis @ 6:09 pm BST 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 of what this allows us to do: (more…)

December 3, 2008

Predicate Problems

Filed under: CBO,dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 8:47 pm GMT Dec 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


Filed under: dbms_xplan,Execution plans — Jonathan Lewis @ 8:01 am GMT Mar 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 format options that can be used with the function.


January 10, 2008

Filter plan error

Filed under: dbms_xplan,Execution plans,Oracle — Jonathan Lewis @ 7:24 pm GMT Jan 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.


June 25, 2007


Filed under: CBO,dbms_xplan,Execution plans,Hints,Oracle — Jonathan Lewis @ 8:15 pm BST Jun 25,2007

When Oracle tries to optimize a complex SQL statement one of the first steps it takes is to transform it into something simpler. The ideal target (from the optimizer’s perspective) is to eliminate any subqueries and in-line views so that your SQL statement ends up as a list of tables in a single from clause. Every in-line view and subquery is a separate query block, and the optimizer really wants to turn the whole thing into a single query block. Unfortunately this isn’t always possible, and sometimes it isn’t even a good idea – which is why 10g introduced ‘cost based query transformation’.


April 26, 2007


Filed under: dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 9:14 pm BST Apr 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.


December 22, 2006

dbms_xplan – again

Filed under: dbms_xplan — Jonathan Lewis @ 12:03 pm GMT Dec 22,2006

I’ve discussed the capabilities of the dbms_xplan package in a couple of posts already; and shown how useful it can be in two examples: understanding a problem with filter subquery selectivity and understanding why some Cartesian merge joins were appearing unexpectedly.

Let me make a crucial point about execution plans (again):  if you have a problem with an execution plan, and need help in understanding what’s going you, you should provide at least the same information that is available from a simple:

explain plan for {statement}
select * from table(dbms_xplan.display);

In particular, you must generate the filter_predicates and access_predicates.

December 12, 2006

Plans in Memory

Filed under: dbms_xplan,Execution plans,Infrastructure,Performance — Jonathan Lewis @ 8:42 pm GMT Dec 12,2006

In an earlier article I described how dbms_xplan.display_cursor() could be used to query memory to find the execution plans (and row source statistics) of recently executed SQL.

In 9i, you have to use your own SQL to get the equivalent results. If you do, you should avoid using the “traditional” hierarchical type of query that you would use against the plan table, as it can be a little brutal on the library cache latches. Instead, you query the v$sql_plan view by hash_value and child_number with a simple “order by id” using the new, internally calculated, depth column instead of the psuedo-column level as the means of indenting the operations.


November 15, 2006

dbms_xplan pt.2

Filed under: dbms_xplan,Execution plans,Infrastructure,Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 8:22 am GMT Nov 15,2006

[More on dbms_xplan.display_cursor()]

A comment on my previous posting about dbms_xplan pointed out that in 10g you also get the function dbms_xplan.display_awr that allows you to report historical execution plans from the AWR (automatic workload repository).

This is true, and there is yet another function dbms_xplan.display_sqlset which allows you to report execution plans from SQL Tuning Sets if you have been using the Automatic SQL Tuning Tools. But to use these functions you do need to purchase the Tuning Pack licence and the Diagnostic Pack licence.


November 9, 2006

dbms_xplan in 10g

Filed under: dbms_xplan,Execution plans,Hints,Tuning — Jonathan Lewis @ 9:17 pm GMT Nov 9,2006

[More on dbms_xplan.display_cursor()]

If you’re using 9i and haven’t learned about the dbms_xplan package, then you should take a good look at it right away. It’s (usually) a much better way of getting execution plans from your system than writing your own queries against the plan_table.

If you’ve been using dbms_xplan, and upgraded from 9i to 10g, make sure that you look at the new features – there are some things which are really useful, and this note is about just one of them.



« Previous Page

Website Powered by