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.