Formatting a Wrapped Execution Plan

If you received or produced an execution plan like the following unreadable one:

——————————————————————————–

——————-

| Id  | Operation                   | Name                    | E-Rows |  OMem |

1Mem | Used-Mem |

——————————————————————————–

——————-

|   0 | SELECT STATEMENT            |                         |        |       |

|          |

|   1 |  SORT AGGREGATE             |                         |      1 |       |

|          |

|*  2 |   HASH JOIN                 |                         |     13 |  1102K|

1102K|  355K (0)|

|*  3 |    HASH JOIN                |                         |     13 |   988K|

988K |  367K (0)|

|*  4 |     HASH JOIN               |                         |     13 |   921K|

921K |  621K (0)|

|*  5 |      HASH JOIN OUTER        |                         |     13 |   836K|

836K | 1224K (0)|

|*  6 |       HASH JOIN             |                         |     13 |   821K|

821K |  501K (0)|

|*  7 |        HASH JOIN            |                         |     13 |  1102K|

1102K|  501K (0)|

|   8 |         MERGE JOIN CARTESIAN|                         |      1 |       |

|          |

|*  9 |          TABLE ACCESS FULL  | PROFILE$                |      1 |       |

|          |

|  10 |          BUFFER SORT        |                         |      1 | 73728 |

73728|          |

|* 11 |           TABLE ACCESS FULL | PROFILE$                |      1 |       |

|          |

|* 12 |         TABLE ACCESS FULL   | USER$                   |     36 |       |

|          |

|  13 |        TABLE ACCESS FULL    | PROFNAME$               |      1 |       |

|          |

|* 14 |       TABLE ACCESS FULL     | RESOURCE_GROUP_MAPPING$ |      1 |       |

|          |

|  15 |      TABLE ACCESS FULL      | TS$                     |      7 |       |

|          |

|  16 |     TABLE ACCESS FULL       | TS$                     |      7 |       |

|          |

|  17 |    TABLE ACCESS FULL        | USER_ASTATUS_MAP        |      9 |       |

|          |

——————————————————————————–

——————-

How we can reformat this plan into a more readable format?

We are going to use the “awk” Unix/Linux utility to format the plan. In Windows , you may install the cygwin64 terminal utility which has also awk utility available.

Procedure:

Copy the un-formatted execution plan to a text file “plan1.txt”

Open cygwin64 terminal and move to the directory containing the plan1.txt file.

Run the following awk program:

cat plan1.txt | awk ‘{ printf “%s”, $0 ; if (NR % 3 == 0) print “” }’

Note that:

$0           represents the entire line

NR          Row number

Note that the above AWK command is simply printing the carriage return (or Enter) at only rows that are multiple of 3 ( rows #: 3, 6, 9,…etc). Note that this may be different from one plan to another.

After that, you may do some minor manual corrections to add some spaces and produce the following final plan:

Another example:

| Id  | Operation                         | Name               | Starts |

E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

———————————————————————————————————————————————

|   0 | SELECT STATEMENT                  |                    |      1 |

|      1 |00:00:10.97 |      36 |       |       |          |

|   1 |  SORT AGGREGATE                   |                    |      1 |

1 |      1 |00:00:10.97 |      36 |       |       |          |

|   2 |   PX COORDINATOR                  |                    |      1 |

|     12 |00:00:10.97 |      36 |       |       |          |

|   3 |    PX SEND QC (RANDOM)            | :TQ10000           |      0 |

1 |      0 |00:00:00.01 |       0 |       |       |          |

|   4 |     SORT AGGREGATE                |                    |      0 |

1 |      0 |00:00:00.01 |       0 |       |       |          |

|   5 |      PX PARTITION RANGE ITERATOR  |                    |      0 |

96M|      0 |00:00:00.01 |       0 |       |       |          |

|   6 |       BITMAP CONVERSION COUNT     |                    |      0 |

96M|      0 |00:00:00.01 |       0 |       |       |          |

|   7 |        BITMAP AND                 |                    |      0 |

|      0 |00:00:00.01 |       0 |       |       |          |

|   8 |         BITMAP OR                 |                    |      0 |

|      0 |00:00:00.01 |       0 |       |       |          |

|*  9 |          BITMAP INDEX SINGLE VALUE| F1_GDS_ID_IDX      |      0 |        |

0 |00:00:00.01 |       0 |       |       |          |

|* 10 |          BITMAP INDEX SINGLE VALUE| F1_GDS_ID_IDX      |      0 |        |

0 |00:00:00.01 |       0 |       |       |          |

|  11 |         BITMAP MERGE              |                    |      0 |

|      0 |00:00:00.01 |       0 |  1024K|   512K|          |

|* 12 |          BITMAP INDEX RANGE SCAN  | F1_MONTH_ID_IDX    |      0 |        |

0 |00:00:00.01 |       0 |       |       |          |

In this case I need only to consider the carriage returns at lines (2, 4, ……etc)

cat plan2.txt | awk ‘{ printf “%s”, $0 ; if (NR % 2 == 0) print “” }’

And with some minor manual modifications to the plan2.txt file, we can reach the following output:

References:

http://blog.tanelpoder.com/2010/01/18/sometimes-things-are-easy-part-1-how-to-fix-wrapped-execution-plan-text/

Thanks
Ahmed