dbms_xplan – again | Oracle Scratchpad

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.

Comments (18)

18 Comments »

Needless to say, I’ve learned my lesson the hard way. Changing all my fmtxplan.sql scripts to:
PROMPT Use dbms_xmplan.display!!!

Comment by Jeff Hunter — December 22, 2006 @ 2:37 pm BST Dec 22,2006 | Reply

Jonathan,

From Oracle10gR2 onwards, oracle uses the dbms_xplan.display with AUTOTTRACE utility.
i.e. when we run AUTOTRACE utility to predict the execution plan, oracle internally uses dbms_xplan.display to diaply the output and it also shows the predicate filtering which was clearly missing until set autot trace exp

  1* select sysdate from dual where sysdate = sysdate
SYSTEM on 24-DEC-06 at rmdb &gt;/

Execution Plan
----------------------------------------------------------
Plan hash value: 731024556

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYSDATE@!=SYSDATE@!)

Predicate filtering was clearly missing from AUTOT TRACE utility until greater than or equal to version 10gR1.

Comment by Jaffar — December 24, 2006 @ 7:41 am BST Dec 24,2006 | Reply

Quick Question on plan display.Not sure you have come across this issue however.

When you use dbmx_xplan.display_cursor or any other subprograms to capture the plan, the screen display is perfect. However, if you store them in a table, for eg.

insert into blah select * from table(dbms_xplan.display_cursor…..), the plan is not stored in the perfect order…

A simple excerpt to illustrate the problem…

Predicate Information (identified by operation id):
—————————————————
9 – filter((”X”.”PO_VENDOR”=:SYS_B_147 AND INTERNAL_FUNCTION(”DSG_PO_TYPE_CODE”)))
10 – access((”PO_STATUS_CODE”=:SYS_B_148 OR “PO_STATUS_CODE”=:SYS_B_149))
12 – access(INTERNAL_FUNCTION(”X”.”EXPECTED_RECEIPT_DATE”)>=INTERNAL_FUNCTION(”from$_subquery$_004
filter(INTERNAL_FUNCTION(”X”.”EXPECTED_RECEIPT_DATE”)>=INTERNAL_FUNCTION(”from$_subquery$_004
13 – filter(”FISCAL_YEAR_MONTH”=)
45 – access(”X”.”EXPECTED_RECEIPT_DATE”=”ERD”.”DATE_ID”)
48 – filter((”X”.”PO_VENDOR”=:SYS_B_292 AND INTERNAL_FUNCTION(”DSG_PO_TYPE_CODE”)))
50 – filter(”X”.”EXPECTED_RECEIPT_DATE”=INTERNAL_FUNCTION(”from$_subquery$_011
52 – filter(”FISCAL_YEAR_MONTH”=)
80 – access(”DATE_ID”=TO_NUMBER(TO_CHAR(SYSDATE@!,:SYS_B_289)))

| 22 | WINDOW BUFFER | | 5845 | 313K| | 126 (43)| 00:00:01
| 27 | WINDOW BUFFER | | 5845 | 313K| | 126 (43)| 00:00:01
| 32 | WINDOW BUFFER | | 5845 | 313K| | 126 (43)| 00:00:01

EX_PLAN
—————————————————————————————————-
| 37 | VIEW | | 5845 | 313K| | 78 (7)| 00:00:01
| 42 | SORT AGGREGATE | | 1 | 683 | | 48486 (2
A portion of the plan shows up after predicate information….

Any ideas how to enforce this?

Thanks
Ganesh

Comment by Ganesh — October 4, 2007 @ 11:38 am BST Oct 4,2007 | Reply
Hi Ganesh,

Oracle tables do not have any implicit ordering – unless you sort the query the results may be in any order.

To solve this, you could add a numeric column to your “blah” table (e.g. called “rn”), then try this:

insert into blah select rownum as rn, plan_table_output from table(dbms_xplan.display_cursor);

Then you can query the table as follows:

select plan_table_output from blah order by rn;

Comment by Jeff Kemp — October 5, 2007 @ 4:21 am BST Oct 5,2007 | Reply
Ganesh, I see Jeff Kemp has supplied the answer. The position of rows in a table is not predicatable, and any time you want an ordered output, you must be able to supply an “order by” clause.

I’d go one step further than Jeff, possibly:
```
create table jpl_plan as
select 	'Some Label Text'	tag, 	rownum			rn, 	plan_table_output 	plan_line 
from 	table(dbms_xplan.display_cursor('9tz4qu4rj9rdp'))
where	rownum = 0
;
```
This happens to use a particular cursor that I know is in memory to give me the table structure.
Then I can use Jeff’s “insert as select” with a piece of text to label every plan as I create it:
```
insert into jpl_plan select 'LABEL1', rn, plan_table_output from ...

commit;

select	plan_table_output
from	jpl_plan
where	tag = 'LABEL1'
order by	rn
;
```
Comment by Jonathan Lewis — October 5, 2007 @ 9:10 am BST Oct 5,2007 | Reply
Thanks.

Ganesh

Comment by Ganesh — October 8, 2007 @ 7:29 pm BST Oct 8,2007 | Reply
[…] so we are a little deprived of clues. The execution plan should always be a proper report from dbms_xplan, showing the access_predicates and filter_predicates. In this case, we may find that (for reasons […]

Pingback by Trouble-shooting « Oracle Scratchpad — January 14, 2008 @ 1:32 pm BST Jan 14,2008 | Reply
[…] — Jonathan Lewis @ 8:47 pm UTC 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 […]

Pingback by Predicate Problems « Oracle Scratchpad — June 26, 2009 @ 1:33 pm BST Jun 26,2009 | Reply
[…] Have you spotted the critical clue ? It’s in the predicate sections. […]

Pingback by Ignoring Hints – 2 « Oracle Scratchpad — February 11, 2010 @ 7:29 pm BST Feb 11,2010 | Reply
[…] Lewis @ 6:17 pm UTC Apr 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 […]

Pingback by Predicate (again) « Oracle Scratchpad — April 15, 2010 @ 6:18 pm BST Apr 15,2010 | Reply
[…] first response, of course, should be to tell me off for not showing the complete execution plan. Always genreate and look at the predicate sections which, in this case, also shows some […]

Pingback by Cursor Sharing 3 « Oracle Scratchpad — May 3, 2010 @ 8:58 pm BST May 3,2010 | Reply
[…] The owner of the query is going to be using dbms_xplan, checking the estimated cardinalities and predicates, to check why Oracle came up with a Cartesian […]

Pingback by Buffer Sorts – 2 « Oracle Scratchpad — May 7, 2010 @ 8:50 am BST May 7,2010 | Reply
[…] of the plan is much lower as a consequence.. You’ll also notice that the predicate sections (always check the predicate section) are a little different – the order of evaluation has been […]

Pingback by Join Surprise « Oracle Scratchpad — December 15, 2010 @ 8:56 pm BST Dec 15,2010 | Reply
[…] it doesn’t – and there’s a clue about why not in the “Predicate Information”. To create this plan the optimizer would have to duplicate an existing predicate (c1 = 5) so that […]

Pingback by Mything 2 « Oracle Scratchpad — June 24, 2011 @ 5:53 pm BST Jun 24,2011 | Reply
[…] yourself a pat on the head if you’ve been thinking “Where’s the predicate section for these plans ?” (9 years old […]

Pingback by Predicates | Oracle Scratchpad — December 22, 2015 @ 12:58 pm BST Dec 22,2015 | Reply
[…] I did emphasise the need to examine ALL of the execution plan – and the important feature appears not in the body of the plan but in the predicate section. […]

Pingback by Aliases | Oracle Scratchpad — May 2, 2017 @ 6:29 pm BST May 2,2017 | Reply
[…] to index range scans of the access predicates and filter predicates that Oracle reports in the predicate section of an execution […]

Pingback by opt_estimate | Oracle Scratchpad — May 8, 2017 @ 8:04 am BST May 8,2017 | Reply
[…] nested loop joins in both branches – until we look a little more closely and examine the Predicate section of the plan. What, exactly, has been pushed […]

Pingback by pushing predicates | Oracle Scratchpad — July 13, 2018 @ 1:05 pm BST Jul 13,2018 | Reply