What you see below is a common problem. Someone sends you (or posts to a forum) a wide execution plan, which is unreadable because of wrapped lines. For example, this one below:
-------------------------------------------------------------------------------- ------------------- | 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 | | | | -------------------------------------------------------------------------------- -------------------
So now you either try to manually edit and fix the execution plan text so you could read it or ask the developer to send the execution plan again. Both approaches take time.
Well, sometimes things are easy – in this particular case I saved the above into a file called /tmp/x and ran the following command:
$ cat /tmp/x | awk '{ printf "%s", $0 ; if (NR % 3 == 0) print } END { print }'
---------------------------------------------------------------------------------------------------
| 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 | | | |
---------------------------------------------------------------------------------------------------
All I did here was that I stripped out line feeds from all lines except every 3rd line (which is the real end of the original line).
Note that if your linesize is very wide (and trimspool/trimout settings are ON) then this script would need some adjustment…
I’m sure this trivial approach doesn’t work in all situations, but with this article I wanted to illustrate that sometimes things which seem hard can be made much easier with a little scripting knowledge. If you are thinking which technology you should learn next – then better check out a Perl, Python or some shell+AWK book :)
By the way, if you want real flexibility displaying your execution plans (directly from library cache views), then check this out:
