Skip to Content

The Plan Visualizer or PlanViz tool in SAP HANA is the primary tool to understand query runtime performance.

I am taking a very simple scenario, where I have VBAK table to pick Sales Order and VBAP table to pick Sales Item. I have put a filter on field AUART(SalesType) = ‘AG’

Sales Order Header Table: VBAK

Sales Order Item Table: VBAP

Figure 1: Simple Calculation view

Figure 2: Join condition used in the calculation view

Using this simple example, we can see how you we use the different features of the PlanViz tool to retrieve and interpret detailed information on the actual query execution.

To understand what SAP HANA does in order to process the query, we choose Visualize Plan from the context menu in the SQL Editor. A dialog box about switching the perspective to the PlanViz perspective may be displayed, and confirming it ensures that all relevant UI displays are visible during the analysis.

Figure 3: Execute visualize plan from SQL editor

 

Figure 4: Overview page

 

 

Figure 5: Overview page with Table Used details

 

Execution     Total duration of the query excluding  compilation time
Dominant Operators

    Operators sorted by their execution time (Top 3). This helps

You understand which operation is taking most time.

No. of Nodes     Number of servers involved, if you have multinode system
Number of network transfer

    Number of network transfer that has happened (this is in

case of multinode system, this is important parameter to

Understand Performance)

Memory Allocated    Total Memory used for executing the statement
Number of table used    Total number of tables touched upon during execution
Result Record Count    Total number of rows output

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.

 

Figure 6: Executed Plan (showing filters details)

You can click on the SQL button on the top right corner to see the exact SQL that was run , this is helpful if you have multiple session of visualize plan open and you want to see which SQL was run.

You can also save the executed plan for future reference , by clicking on the save button.

Figure 7:  Executed Plan (showing join execution)

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.

Figure 8 : Use of ‘Show Critical Path Only’

 

 

 

 

Further References

https://help.sap.com/viewer/bed8c14f9f024763b0777aa72b5436f6/2.0.00/en-US/c1f281fbbb571014aaf38a264c0e12c4.html

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply