Oracle Scratchpad

March 6, 2008


Filed under: dbms_xplan,Execution plans — Jonathan Lewis @ 8:01 am BST 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 parameter values 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 BST 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.


April 26, 2007


Filed under: dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 9:14 pm BST Apr 26,2007

[See also: gather plan statistics]

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 BST 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 BST 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,Statspack,Troubleshooting — Jonathan Lewis @ 8:22 am BST 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 BST 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

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 6,320 other followers