Styjun

I am using Eclipse (and its Data Tools Plugin) for DB/BI development and like to have some more visual/debugging aid in examining huge explain plans with e.g. up to 500 lines, e.g.:

----------------------------------------------------------------------------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
 ----------------------------------------------------------------------------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | 4077K| 21G| | 7224K (3)| 00:04:43 |
 | 1 | SORT AGGREGATE | | 1 | 15 | | | |
 |* 2 | TABLE ACCESS BY INDEX ROWID BATCHED | TAB1 | 1 | 15 | | 2 (0)| 00:00:01 |
 |* 3 | INDEX RANGE SCAN | IDX_FK_1111111111 | 1 | | | 1 (0)| 00:00:01 |
 | 4 | SORT AGGREGATE | | 1 | 15 | | | |
 |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED | TAB1 | 1 | 15 | | 2 (0)| 00:00:01 |
 |* 6 | INDEX RANGE SCAN | IDX_FK_2222222222 | 1 | | | 1 (0)| 00:00:01 |
 | 7 | WINDOW SORT | | 153 | 5967 | | 32 (7)| 00:00:01 |
 |* 8 | HASH JOIN | | 153 | 5967 | | 31 (4)| 00:00:01 |
 ...

It would be nice to stay in Eclipse so I do not have to setup/change environments.

(I think SQL Developer and/or Toad had some graphic explain plan viewer with hierarchic collapse/expand functionality where there is also some Toad Eclipse Plugin available that I did not like because it was quite intrusive regarding project manipulation.)

But please feel free to add other answers for similar tools and maybe screenshots for users with similar needs but different environments.

What you really want to use is SQL Monitor.

https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/

With the following simple regex find/replace strings applied to the text explain plan one can get a nice result:

(e.g. enabling/pushing the Editbox Eclipse Plugin Button in an explain-plan-containing Editor (View) in Eclipse - any other editor with similar block display/highlight features will also do)

find: ^( |[^|]+d |)(s+)

replace: $2$1

will produce the following output (moving the operation indentions to the beginning of each line):

to get this kind of coloring one has to adjust Editbox (Eclipse -> Window -> Preferences-> Editbox -> Others) this way:

• 
  Highlight selected box:
  
  
  • 
    color <Custom> <Red>, width <2>
    
  
  


• Color levels: 14


• 
  Gradient tool: (e.g.) from color <white> to & <dark green> (and maybe push Generate)

(By the way: Hovering over such a block one can press ALT+Z which will text-select the whole block e.g. for easy copy/paste. (on appropriately indented SQLs also useful for partial code execution))