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.

14 Comments »

  1. 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

  2. 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 >/
    
    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

  3. 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

  4. 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

  5. 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

  6. Thanks.

    Ganesh

    Comment by Ganesh — October 8, 2007 @ 7:29 pm BST Oct 8,2007 | Reply

  7. [...] 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

  8. [...] — 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

  9. [...] 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

  10. [...] 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

  11. [...] 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

  12. [...] 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

  13. [...] 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

  14. [...] 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,528 other followers