The Plan Visualizer or PlanViz tool in SAP HANA is the primary tool to understand query runtime performance.
Steps to Analyze the planViz:
1) First, goto ST05 and activate the trace.
2) Now from Hana Studio, execute the CDS view on which you want to check the performance.
3) After sucessfully executed the CDS view in Hana Studio, deactivate the Trace that was enabled before.
4) Click on the Display Trace.
5) Now select the row which the statement is reading from CDS view.
6) Now goto Edit -> Display Executive Plan -> For Recorded Statement (F9)
6) now click on Executive Trace button
7) Save the file with extension .plv
8) Now goto HanaStudio to open the .plv file.
9) In the overwiew, you can also check the information in the different tabs Timeline ,Operator List, Table used , Performance trace , Network to find more information about the execution.
The network traffic in PlanViz gives you the inter-node data transfer during query execution.
Things like large joins of tables that are located on different nodes require data transfer between those nodes. And this this takes more time to perform the same operation in main memory on a single node, you will see longer execution times for such queries on multi-node systems.
Mapping the Information Model to PlanViz
To get to an understanding of what is happening in PlanViz, it is usually a good idea to map the single operations of the information model to the PlanViz output.
You can click on the ‘Executed Plan’ to go to the details of the execution. You can drill down in detail to see the execution steps.
You Can see the filter conditions applied on the table.
Every box in the PlanViz output represents a so-called plan operator (POP). The names of the plan operators provide a hint as to which execution engine processes them. POP names starting with CE are executed in the calculation engine, those starting with BW are executed in the OLAP engine, and those starting with JE are executed in the join engine.
In the PlanViz display, data flows from bottom to top. That is, our query result is at the top of the view and the actual data retrieval from database tables is at the bottom. The plan operators in between represent the transformations needed in order to get from the data in the tables to the desired output of the data model. In the modeler interface for calculation views, the data flow direction is also from bottom up.
If there are parallel process running it will also show the process running in parallel in separate boxes. This is very important when you have a complex plan and you want to understand if the processes are running in parallel or not. In my current example I do not have parallel process , but in most scenarios you will see parallel process running in different parallel boxes.
As our example is rather simple, the mapping is not too difficult. In the calculation view, we have the result node (implicitly containing a projection), an aggregation, and a filter on top of a join of data retrieved from two analytic views.
By hovering over any of the boxes, a Details pop-up window appears that contains information on the specific box.
The Exclusive Time in the node refers the time taken to execute a single operation. The Exclusive Time in the Join(JECreateNTuple) node is 0.1 ms . The Inclusive Time is the time taken to execute the complete operation including the time of the children operators and excluding compile time. The information provided on the node helps to understand the performance of the executed operations.
If you have a very big PlanViz you can do a Show critical path only to understand the important execution and focus your analysis to those boxes.
You can also use ‘Show Critical Path Only’ by right click and select.
This graphic is a very powerful tool for studying performance of queries on SAP HANA databases. You can explore the graphic further, for example, you can expand, collapse, or rearrange nodes on the screen. You can also save the graphic as an image or XML file, for example, so you can submit it as part of a support query.
Steps to Analyze the planViz:
1) First, goto ST05 and activate the trace.
2) Now from Hana Studio, execute the CDS view on which you want to check the performance.
3) After sucessfully executed the CDS view in Hana Studio, deactivate the Trace that was enabled before.
4) Click on the Display Trace.
5) Now select the row which the statement is reading from CDS view.
6) Now goto Edit -> Display Executive Plan -> For Recorded Statement (F9)
6) now click on Executive Trace button
7) Save the file with extension .plv
8) Now goto HanaStudio to open the .plv file.
9) In the overwiew, you can also check the information in the different tabs Timeline ,Operator List, Table used , Performance trace , Network to find more information about the execution.
The network traffic in PlanViz gives you the inter-node data transfer during query execution.
Things like large joins of tables that are located on different nodes require data transfer between those nodes. And this this takes more time to perform the same operation in main memory on a single node, you will see longer execution times for such queries on multi-node systems.
Mapping the Information Model to PlanViz
To get to an understanding of what is happening in PlanViz, it is usually a good idea to map the single operations of the information model to the PlanViz output.
You can click on the ‘Executed Plan’ to go to the details of the execution. You can drill down in detail to see the execution steps.
You Can see the filter conditions applied on the table.
Every box in the PlanViz output represents a so-called plan operator (POP). The names of the plan operators provide a hint as to which execution engine processes them. POP names starting with CE are executed in the calculation engine, those starting with BW are executed in the OLAP engine, and those starting with JE are executed in the join engine.
In the PlanViz display, data flows from bottom to top. That is, our query result is at the top of the view and the actual data retrieval from database tables is at the bottom. The plan operators in between represent the transformations needed in order to get from the data in the tables to the desired output of the data model. In the modeler interface for calculation views, the data flow direction is also from bottom up.
If there are parallel process running it will also show the process running in parallel in separate boxes. This is very important when you have a complex plan and you want to understand if the processes are running in parallel or not. In my current example I do not have parallel process , but in most scenarios you will see parallel process running in different parallel boxes.
As our example is rather simple, the mapping is not too difficult. In the calculation view, we have the result node (implicitly containing a projection), an aggregation, and a filter on top of a join of data retrieved from two analytic views.
By hovering over any of the boxes, a Details pop-up window appears that contains information on the specific box.
The Exclusive Time in the node refers the time taken to execute a single operation. The Exclusive Time in the Join(JECreateNTuple) node is 0.1 ms . The Inclusive Time is the time taken to execute the complete operation including the time of the children operators and excluding compile time. The information provided on the node helps to understand the performance of the executed operations.
If you have a very big PlanViz you can do a Show critical path only to understand the important execution and focus your analysis to those boxes.
You can also use ‘Show Critical Path Only’ by right click and select.
This graphic is a very powerful tool for studying performance of queries on SAP HANA databases. You can explore the graphic further, for example, you can expand, collapse, or rearrange nodes on the screen. You can also save the graphic as an image or XML file, for example, so you can submit it as part of a support query.
- SAP Managed Tags:
6 Comments
