Skip to main content
Question

Is there a way to visualize the EXPLAIN query plan? like how we can visualize in graphviz

  • January 4, 2023
  • 7 replies
  • 0 views
A
  • Anonymous

trying to see how to improve this query and what are the high run time and what to change to improve the run time.

 

QUERY PLAN

 

Access Path:

+-JOIN HASH [RightOuter] [Cost: 52M, Rows: 6M] (PATH ID: 1) Outer (RESEGMENT)

| Join Cond: (vbap.VBELN = VBRP.AUBEL) AND (vbap.POSNR = VBRP.AUPOS)

| Join Filter: (VBRP.AUBEL <> '') AND (VBRP.AUPOS <> '000000') AND (VBRK.VBTYP = 'M')

| Execute on: All Nodes

| +-- Outer -> STORAGE ACCESS for vbap [Cost: 708K, Rows: 380M] (PATH ID: 2)

| | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.VBAP_super

| | Materialize: vbap.VBELN, vbap.POSNR, vbap.YYBOFLAG

| | Execute on: All Nodes

| +-- Inner -> JOIN HASH [LeftOuter] [Cost: 34M, Rows: 6M] (PATH ID: 3) Outer (RESEGMENT)(LOCAL ROUND ROBIN) Inner (RESEGMENT)

| | Join Cond: (vbak.VBELN = VBRP.AUBEL)

| | Join Filter: (VBRP.AUBEL <> '') AND (VBRK.VBTYP = 'M')

| | Materialize at Input: VBRP.MANDT, VBRP.VBELN, VBRP.POSNR, VBRP.UEPOS, VBRP.FKIMG, VBRP.VRKME, VBRP.UMVKZ, VBRP.UMVKN, VBRP.MEINS, VBRP.FKLMG, VBRP.LMENG, VBRP.GSBER, VBRP.PRSDT, VBRP.FBUDA, VBRP.NETWR, VBRP.VGTYP, VBRP.AUBEL, VBRP.AUPOS, VBRP.AUREF, VBRP.MATNR, VBRP.ARKTX, VBRP.MATKL, VBRP.PSTYV, VBRP.SPART, VBRP.WERKS, VBRP.WKREG, VBRP.PRSFD, VBRP.SKTOF, VBRP.SKFBP, VBRP.KONDM, VBRP.KTGRM, VBRP.SHKZG, VBRP.ERNAM, VBRP.ERDAT, VBRP.ERZET, VBRP.LGORT, VBRP.WAVWR, VBRP.KZWI1, VBRP.KZWI2, VBRP.KZWI3, VBRP.KZWI4, VBRP.KZWI5, VBRP.KZWI6, VBRP.UVPRS, VBRP.UVALL, VBRP.EAN11, VBRP.PRCTR, VBRP.KVGR1, VBRP.KVGR5, VBRP.BONBA, VBRP.CMPRE, VBRP.CMPNT, VBRP.AUTYP, VBRP.MWSBP, VBRP.AUGRU_AUFT, VBRP.CMPRE_FLT, VBRP.MSR_RET_REASON, VBRP.IRM_PCNUM, VBRP.IRM_PCBGP, VBRP.IRM_PCNUM_EXT, VBRP.YYSUPNR, VBRP.YYXBLNR, VBRP.YYSPART, vbak.MANDT, vbak.VBELN, vbak.BSARK

| | Execute on: All Nodes

| | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 16M, Rows: 6M] (PATH ID: 4) Inner (BROADCAST)

| | | Join Cond: (tvaut.AUGRU = VBRP.AUGRU_AUFT)

| | | Materialize at Input: VBRP.AUGRU_AUFT

| | | Execute on: All Nodes

| | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 16M, Rows: 6M] (PATH ID: 5) Inner (BROADCAST)

| | | | Join Cond: (LFA1.LIFNR = VBRP.YYSUPNR)

| | | | Materialize at Input: VBRP.YYSUPNR

| | | | Execute on: All Nodes

| | | | +-- Outer -> JOIN HASH [LeftOuter] [Cost: 15M, Rows: 6M] (PATH ID: 6) Inner (BROADCAST)

| | | | | Join Cond: (TVV5T.KVGR5 = VBRP.KVGR5)

| | | | | Materialize at Input: VBRP.KVGR5

| | | | | Execute on: All Nodes

| | | | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 15M, Rows: 6M] (PATH ID: 7) Inner (BROADCAST)

| | | | | | Join Cond: (VBRP.MANDT = VBRK.MANDT) AND (VBRP.VBELN = VBRK.VBELN)

| | | | | | Execute on: All Nodes

| | | | | | +-- Outer -> STORAGE ACCESS for VBRP [Cost: 592K, Rows: 388M] (PATH ID: 8)

| | | | | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.VBRP_super

| | | | | | | Materialize: VBRP.VBELN, VBRP.MANDT

| | | | | | | Filter: (VBRP.MANDT IS NOT NULL)

| | | | | | | Filter: (VBRP.VBELN IS NOT NULL)

| | | | | | | Execute on: All Nodes

| | | | | | | Runtime Filters: (SIP1(MergeJoin): VBRP.MANDT), (SIP2(MergeJoin): VBRP.VBELN), (SIP3(MergeJoin): VBRP.MANDT, VBRP.VBELN)

| | | | | | +-- Inner -> JOIN HASH [LeftOuter] [Cost: 2M, Rows: 51M] (PATH ID: 9) Outer (RESEGMENT)(LOCAL ROUND ROBIN) Inner (RESEGMENT)

| | | | | | | Join Cond: (KNA1.KUNNR = VBRK.KUNAG)

| | | | | | | Materialize at Input: KNA1.KUNNR, KNA1.NAME1, KNA1.KUKLA

| | | | | | | Execute on: All Nodes

| | | | | | | +-- Outer -> STORAGE ACCESS for VBRK [Cost: 170K, Rows: 51M] (PATH ID: 10)

| | | | | | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.INV_VBRK_super

| | | | | | | | Materialize: VBRK.VBELN, VBRK.MANDT, VBRK.VBTYP, VBRK.KUNAG

| | | | | | | | Filter: (VBRK.MANDT IS NOT NULL)

| | | | | | | | Filter: (VBRK.VBELN IS NOT NULL)

| | | | | | | | Execute on: All Nodes

| | | | | | | +-- Inner -> JOIN HASH [LeftOuter] [Cost: 2K, Rows: 1M] (PATH ID: 11) Inner (BROADCAST)

| | | | | | | | Join Cond: (TKUKT.KUKLA = KNA1.KUKLA)

| | | | | | | | Execute on: All Nodes

| | | | | | | | +-- Outer -> STORAGE ACCESS for KNA1 [Cost: 1K, Rows: 1M] (PATH ID: 12)

| | | | | | | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.KNA1_super

| | | | | | | | | Materialize: KNA1.KUKLA

| | | | | | | | | Filter: (KNA1.KUNNR IS NOT NULL)

| | | | | | | | | Execute on: All Nodes

| | | | | | | | +-- Inner -> STORAGE ACCESS for TKUKT [Cost: 179, Rows: 27] (PATH ID: 13)

| | | | | | | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.TKUKT_super

| | | | | | | | | Materialize: TKUKT.KUKLA, TKUKT.VTEXT

| | | | | | | | | Filter: (TKUKT.SPRAS = 'E')

| | | | | | | | | Filter: (TKUKT.KUKLA IS NOT NULL)

| | | | | | | | | Execute on: All Nodes

| | | | | +-- Inner -> STORAGE ACCESS for TVV5T [Cost: 163, Rows: 26] (PATH ID: 14)

| | | | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.TVV5T_super

| | | | | | Materialize: TVV5T.KVGR5, TVV5T.BEZEI

| | | | | | Filter: (TVV5T.SPRAS = 'E')

| | | | | | Filter: (TVV5T.KVGR5 IS NOT NULL)

| | | | | | Execute on: All Nodes

| | | | +-- Inner -> STORAGE ACCESS for LFA1 [Cost: 1K, Rows: 155K] (PATH ID: 15)

| | | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.LFA1_super

| | | | | Materialize: LFA1.LIFNR, LFA1.NAME1

| | | | | Filter: (LFA1.LIFNR IS NOT NULL)

| | | | | Execute on: All Nodes

| | | +-- Inner -> STORAGE ACCESS for tvaut [Cost: 200, Rows: 383] (PATH ID: 16)

| | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.TVAUT_super

| | | | Materialize: tvaut.AUGRU, tvaut.BEZEI

| | | | Filter: (tvaut.SPRAS = 'E')

| | | | Filter: (tvaut.AUGRU IS NOT NULL)

| | | | Execute on: All Nodes

| | +-- Inner -> JOIN HASH [LeftOuter] [Cost: 67K, Rows: 37M] (PATH ID: 17) Inner (BROADCAST)

| | | Join Cond: (vbak.MANDT = T176T.MANDT) AND (vbak.BSARK = T176T.BSARK)

| | | Execute on: All Nodes

| | | +-- Outer -> STORAGE ACCESS for vbak [Cost: 24K, Rows: 37M] (PATH ID: 18)

| | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.VBAK_super

| | | | Materialize: vbak.MANDT, vbak.BSARK

| | | | Execute on: All Nodes

| | | +-- Inner -> STORAGE ACCESS for T176T [Cost: 262, Rows: 76] (PATH ID: 19)

| | | | Projection: 8e9add16-f209-4968-8e45-d1442c21cccb_cb2d42c8-e4fe-422e-8ac2-cf82c740f17c.T176T_super

| | | | Materialize: T176T.BSARK, T176T.MANDT, T176T.VTEXT

| | | | Filter: (T176T.SPRAS = 'E')

| | | | Filter: (T176T.MANDT IS NOT NULL)

| | | | Filter: (T176T.BSARK IS NOT NULL)

| | | | Execute on: All Nodes

7 replies

A
  • Anonymous
  • January 9, 2023

According to Vertica documentation

 

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/QueryManagement/QueryPlans/ExplainOutputOptions/EXPLAINOutputOptions.htm

 

By default, EXPLAIN output represents the query plan as a hierarchy, where each level, or path, represents a single database operation that the optimizer uses to execute a query. 

EXPLAIN output also appends DOT language source so you can display this output graphically with open source Graphviz tools.

 

Another question is if Celonis ported this into Data Ingestion.

 

HTH

A
  • Anonymous
  • January 9, 2023

Hi Guillermo,

 

When i run this explain Statement and i get the information, but it is not all the results, it is truncated may be after 150 lines.

 

So have to check with celonis if there is a way to import into data pool.

 

Thanks

Muthappan

A
  • Anonymous
  • January 9, 2023

Hmmm... I made a EXPLAIN of a query with 6-7 joins and it doesn't truncate the output

 

image

A
  • Anonymous
  • January 9, 2023

i dont get digraph for every explain statement, thats why i say mine is getting truncated. not sure if there is any setting to show every results of the explain statement.

A
  • Anonymous
  • January 9, 2023

if i get digraph then i can use graphviz online tools to visualize the query plan. since i missing digraph i am feeling it is truncating.

A
  • Anonymous
  • January 9, 2023

Hmmm.... no promises, I will try to use the graphviz with my explains to see what I get.

 

Again: no promises... :P

A
  • Anonymous
  • January 10, 2023

Hi Muthappan

 

Just in case

 

I played with graphviz (installed in my laptop, downloaded from https://graphviz.org/download/ - Windows 64b version)

 

I found that cut-and-paste from the output of the explain in Celonis throw me an error. I needed to finish the document with a "}"

 

After that It worked and, as far as I can see, didn't cut me any info. Of course your explain could be much longer

 

dot -Tjpeg -O EXPLAIN.txt

 

imageHTH. Good luck.

Terms & ConditionsAccessibility statement