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.
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 GMT Dec 22,2006 |
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
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 GMT Dec 24,2006 |
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 GMT Oct 4,2007 |
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 GMT Oct 5,2007 |
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:
Comment by Jonathan Lewis — October 5, 2007 @ 9:10 am GMT Oct 5,2007 |
Thanks.
Ganesh
Comment by Ganesh — October 8, 2007 @ 7:29 pm GMT Oct 8,2007 |
[…] 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 GMT Jan 14,2008 |
[…] — 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 GMT Jun 26,2009 |
[…] 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 GMT Feb 11,2010 |
[…] 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 GMT Apr 15,2010 |
[…] 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 GMT May 3,2010 |
[…] 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 GMT May 7,2010 |
[…] 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 GMT Dec 15,2010 |
[…] 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 GMT Jun 24,2011 |