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

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

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 HRightOuter] ]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 pCost: 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 HLeftOuter] ]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 HLeftOuter] ]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 HLeftOuter] ]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 HLeftOuter] ]Cost: 15M, Rows: 6M] (PATH ID: 6) Inner (BROADCAST)

| | | | | +-- 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)

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

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

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

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

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

| | | | | | | +-- Outer -> STORAGE ACCESS for VBRK KCost: 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

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

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

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

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

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

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

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

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

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

| | | +-- Inner -> STORAGE ACCESS for tvaut tCost: 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 HLeftOuter] ]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 kCost: 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 TCost: 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

Page 1 / 1

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

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

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

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.

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.

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

Again: no promises...

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

HTH. Good luck.

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN