DBMS_XPLAN display issues
December 21, 2009 11 Comments
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.
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.