Skip to Content
Author's profile photo Nandan Tadahal

Generate HANA PlanViz file from ST04

  • 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.

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Pradeep Kodihalli Satish
      Pradeep Kodihalli Satish

      Very helpful documentation Nandan. Thank you sharing it.

      Author's profile photo Rahul Sancheti
      Rahul Sancheti

      Hi Nandan, thanks for the doc.

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

       

      Author's profile photo Nandan Tadahal
      Nandan Tadahal
      Blog Post Author

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

      Author's profile photo Colm Boyle
      Colm Boyle

      Thanks for Sharing Nandan 😉

      Author's profile photo Philipp Nell
      Philipp Nell

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

       

      Author's profile photo Nandan Tadahal
      Nandan Tadahal
      Blog Post Author

      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