Generate HANA PlanViz file from ST04

[nandantadahal]

• ST04 transaction allows generation of plan viz and explain the execution plans for the SQL statements. This is useful especially for BW on HANA scenarios where the expensive statements on the HANA side can be analysed without access to the HANA studio.

 

• ST04 also allows analysis of existing threads on the HANA side and generate plan viz/explain plan for the threads as well.

 

• ST04 has options to generate plan viz/explain plan for the SQL statements that the users wants to analyse. This is useful to analyse the TREX calls from BW to HANA db (call trexviadbsl ('olapSearch' , to_blob(hextobin('*')), ?) )

 

 

To analyse the statement executed in the threads on HANA follow these steps:

 

1. Start ST04 and navigate to threads under Performance as shown here and select the SQL you want to analyse and click EXPLAIN:


2. After clicking on  EXPLAIN button, the EXPLAIN is displayed :


3. From this screen click on Execution Trace button to save the plan viz file (.plv) to your PC:


4. Once saved locally, the plan viz file can be opened in HANA studio for further analysis:


5. Studio will display the Overview and executed plan:

 

 

Similarly, EXPLAIN section in ST04 can be used to generate plan viz/explain plan for the SQL statements.Explain Plan provides details of engines being used for certain operations, sub tree costs, estimated cardinalities, etc.

 

Here the steps are shown for a simple 'SELECT * FROM DUMMY' however complex queries or TREX calls can also be posted in EXPLAIN and analysed.

 



Click on Explain button in the menu after pasting the SQL and hit Execution Trace once the plan is displayed to generate the viz file:



These steps are applicable to BW on HANA for 7.4, 7.5 and BW/4HANA releases.

[prdpks]

Very helpful documentation Nandan. Thank you sharing it.

[former_member230845]

Hi Nandan, thanks for the doc.

you may want to mention the minimum SP required for 740 and 750.

 

[nandantadahal]

Hey Rahul , Sure will add this.  I ran these on 7.4 SP 11.

[colm_boyle]

Thanks for Sharing Nandan 😉

[PhilMad]

Hi, Very interesting thread! But I couldn't make it work for TrexByDBSL call. Is there a specific trick? Kr, Phil

 

[nandantadahal]

Hi

Try the trex call in studio to verify it is correct syntactically  ST04 doesn't always give the correct reason for statement failure and the only alternative is to test it out in studio

 

 

Thanks

Nandan