Analyzing SQL Execution with the Plan Visualizer (PlanViz)
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|
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
|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’