DBMS_XPLAN display issues

DBMS_XPLAN is a great little tool for getting formatted SQL execution plans and associated information. Good enough to make my revised top 3 yesterday.

Whether following an explain plan:

EXPLAIN PLAN FOR 
SELECT ... FORM ...;

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY);

Or pulling a plan from memory using:

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(<sql_id>,<child_cursor>,<format>));

Typically used with the gather_plan_statistics hint like this:

SELECT /*+ gather_plan_statistics */ .... 
FROM ... 
WHERE...;

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

Or pulling a plan still in memory by specifying the sql_id and, optionally, the child cursor.

For further information on the options for the third format parameter, here is a nice post on Rob van Wijk’s blog.

Jonathan Lewis also has some articles on dbms_xplan here and here.

Another handy option which I’ve been using recently is the ability to pull plans which are no longer in memory from the AWR repository using:

SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(<sql_id>,<child_cursor>,<db_id>,<format>));

Whilst troubleshooting a recent performance problem where a good (all things are relative) plan had gone bad, the bad plan was no longer in memory. However, I was able to pull the bad plan from the AWR repository and also a number of variations on the good plan.

However, this was a big plan – some 3000 lines – and illustrates some display issues which you can get.

See this excerpt:


| 316 | E JOIN CARTESIAN                                        MERG |                                |    790 |                                                                                                                                                                                             
| 317 | IST ITERATOR                                             INL |                                |        |                                                                                                                                                                                             
| 318 | BLE ACCESS BY INDEX ROWID                                 TA | CLASSIFICATIONS                |      5 |                                                                                                                                                                                             
| 319 | NDEX RANGE SCAN                                            I | CLASSIFICATIONS_IX1            |      5 |                                                                                                                                                                                             
| 320 | FER SORT                                                 BUF |                                |    158 |                                                                                                                                                                                             
| 321 | RT UNIQUE                                                 SO |                                |    158 |                                                                                                                                                                                             
| 322 | NDEX FAST FULL SCAN                                        I | PK_ELEMENT_AGGREGATES          |    158 |                                                                                                                                                                                             
| 323 | E ACCESS FULL                                           TABL | PERFORMANCE_ELEMENTS           |   1863 |                                                                                                                                                                                             
| 324 |  UNIQUE SCAN                                           INDEX | PK_CLASSIFICATION_ELEMENTS     |      1 |                                                                                                                                                                                             
| 325 |  SCAN                                            INDEX RANGE | VEHICLE_METRICS_PK             |      1 |                                                                                                                                                                                             
| 326 | S BY INDEX ROWID                                 TABLE ACCES | POSITIONS                      |      1 |                                                                                                                                                                                             
| 327 | E SCAN                                            INDEX RANG | POSITIONS_UK                   |      1 |                                                                                                                                                                                             
| 328 |  INDEX ROWID                                 TABLE ACCESS BY | PRR_MAPPINGS                   |      1 |                                                                                                                                                                                             
| 329 | AN                                            INDEX RANGE SC | PRR_MAPPINGS_UX1               |      1 | 

The OUTPUT column is wrapped and does not make such investigations and interpretations any easier.
Note that this is NOT a SQL*Plus display issue as far as I can work out. It’s not the whole line which is wrapping.

As far as I know, there is no solution to this.
I have an Oracle SR open for confirmation but after 4 days I’ve yet to even receive confirmation of my ticket…

If there’s a solution, let me know.

About these ads

11 Responses to DBMS_XPLAN display issues

  1. Pingback: The way things are « OraStory

  2. Hi,

    I hate this issue – it happens quite often. As a workaround, you may issue a direct sql call to the dba_hist_sql_plan with proper column formatting (similar to Tanel Poder’s scripts).

  3. dombrooks says:

    Timur – thanks.

    And a good point point about getting around this by looking at dba_hist_sql_plan (take Tanel’s script and repoint to dba_hist_sql_plan via plan_hash_value rather than sql_id and child number – the latter which is not in the hist table)

    On which note, it’s a shame that dba_hist_sql_plan has columns for captured filter_predicates and access_predicates but which are left unpopulated up to and including 11.1.0.6 at least.

  4. I see that’s a plan more than 700 lines – so maybe it really does need that level of indentation; but it’s posisble that your also hitting the bug where Oracle gets the “depth” value wrong when there are multiple filter subqueries – which is an even better reason for using one of the old fashioned “connect by” queries against the underlying objects (possibly with subquery factoring or a no_merge view to pull the driving data out of the main tables first).

  5. dombrooks says:

    Yep – it’s 2800 lines when pulled from dba_hist_sql_plan. I’ll be putting the plans up in a bit on another article.

    What’s the details of that bug? Is it a bug on v$sql_plan? Wondering whether that potentially means that the information in dba_hist_sql_plan could be wrong as that’s populated by a select on v$sql_plan?

  6. Dom,

    The bug is an error in the way Oracle evaluates the “depth” column in the plan – so it appears in explain plan, and in v$sql_plan, and gets copied into the AWR.

    I did a blog about it here: http://jonathanlewis.wordpress.com/2008/01/10/filter-plan-error/

    There have been some changes in how often it goes wrong – but I never raised it as a bug with Oracle, and haven’t been checking it through the versions – it’s just something I remember when I see lots of subqueries and a heavily indented execution plan.

  7. dombrooks says:

    Thanks Jonathan. For some reason, the url to your article was wrong. I think I’ve corrected it to the right one.

  8. Pingback: Plans gone AWRy – an invASHtigation « OraStory

  9. Pingback: Blogroll Report – 18/12/2009-25/12/2009 « Coskan’s Approach to Oracle

  10. dombrooks says:

    Oracle Support asked me to a dump of tables & statistics and lists of patches, etc in order to investigate the wrapping.

    I thought this was a bit of a fob off as this was likely to be a generic issue.

    Fortunately, I was able to reproduce a generic problem with a generic query:

    explain plan for
    with subq_a as
    (select * from dba_objects)
    select *
    from subq_a a1
    , subq_a a2
    , subq_a a3
    , subq_a a4
    , subq_a a5
    , subq_a a6
    , subq_a a7
    , subq_a a8
    , subq_a a9
    , subq_a a10
    , subq_a a11
    , subq_a a12
    , subq_a a13
    , subq_a a14
    , subq_a a15
    , subq_a a16
    , subq_a a17
    , subq_a a18
    , subq_a a19
    , subq_a a20
    , subq_a a21
    , subq_a a22
    , subq_a a23
    , subq_a a24
    , subq_a a25
    , subq_a a26
    , subq_a a27
    , subq_a a28
    , subq_a a29
    , subq_a a30
    , subq_a a31
    , subq_a a32
    , subq_a a33
    , subq_a a34
    , subq_a a35
    , subq_a a36
    , subq_a a37
    , subq_a a38
    , subq_a a39
    , subq_a a40
    , subq_a a41
    , subq_a a42
    , subq_a a43
    , subq_a a44
    , subq_a a45
    , subq_a a46
    , subq_a a47
    , subq_a a48
    , subq_a a49
    , subq_a a50
    where a2.object_id = a1.object_id
    and a3.object_id = a2.object_id
    and a4.object_id = a3.object_id
    and a5.object_id = a4.object_id
    and a6.object_id = a5.object_id
    and a7.object_id = a6.object_id
    and a8.object_id = a7.object_id
    and a9.object_id = a8.object_id
    and a10.object_id = a9.object_id
    and a11.object_id = a10.object_id
    and a12.object_id = a11.object_id
    and a13.object_id = a12.object_id
    and a14.object_id = a13.object_id
    and a15.object_id = a14.object_id
    and a16.object_id = a15.object_id
    and a17.object_id = a16.object_id
    and a18.object_id = a17.object_id
    and a19.object_id = a18.object_id
    and a20.object_id = a19.object_id
    and a21.object_id = a20.object_id
    and a22.object_id = a21.object_id
    and a23.object_id = a22.object_id
    and a24.object_id = a23.object_id
    and a25.object_id = a24.object_id
    and a26.object_id = a25.object_id
    and a27.object_id = a26.object_id
    and a28.object_id = a27.object_id
    and a29.object_id = a28.object_id
    and a30.object_id = a29.object_id
    and a31.object_id = a30.object_id
    and a32.object_id = a31.object_id
    and a33.object_id = a32.object_id
    and a34.object_id = a33.object_id
    and a35.object_id = a34.object_id
    and a36.object_id = a35.object_id
    and a37.object_id = a36.object_id
    and a38.object_id = a37.object_id
    and a39.object_id = a38.object_id
    and a40.object_id = a39.object_id
    and a41.object_id = a40.object_id
    and a42.object_id = a41.object_id
    and a43.object_id = a42.object_id
    and a44.object_id = a43.object_id
    and a45.object_id = a44.object_id
    and a46.object_id = a45.object_id
    and a47.object_id = a46.object_id
    and a48.object_id = a47.object_id
    and a49.object_id = a48.object_id
    and a50.object_id = a49.object_id;

    select * from table(dbms_xplan.display);

  11. Pingback: Problem plans « OraStory

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

Follow

Get every new post delivered to your Inbox.

Join 75 other followers

%d bloggers like this: