HANA Query Plan Execution Analysis

[ashok_oleti]

Hi,

Do we have any SAP published document or any other blog post which explains how to read and analyze the query plan execution on HANA.

I have created execution plan on 1 complex query and have few doubts

1) The Time section of execution plan has Execution time 4986.2s which is around 1.5 hour but the query execution doesn't take that long when we execute on the SQL console. What does the Execution Time represents?

2) Analytical Search is the top dominant Operator

When I further get into details of "Analytical Search", I got below details but not sure how to interpret them

3) In the "Executed Plan" tab I see all the boxes in different color borders (Pink, Yellow, Green, blue, red) and Arrows (grey, orange). Are those colors have any importance in the analysis?

4) Each box in the "Executed Plan" tab has "Inclusive time" and "Exclusive time". What are they?

5) What is SUBTREE_COST in the query Explain plan?

it will be really helpful if any of you can help in above queries or anyone provide any document that explains my questions

Cheers,

Ashok

[former_member208449]

Hello Ashok

Also check this video and the documentation for explanation on Explain Plans -

SAP HANA Academy - Using Explain Plans in SAP HANA Studio - YouTube

SAP HANA Reference: EXPLAIN PLAN

[lbreddemann]

I recommend to follow Chandan's advice and make yourself familiar with the available documentation on the topic.

Here are some quick answers to your questions, though:

1) The Time section of execution plan has Execution time 4986.2s which is around 1.5 hour but the query execution doesn't take that long when we execute on the SQL console. What does the Execution Time represents?

The SQL console returns a limited number of records, whereas the PlanViz internally fetches all records by default (this can be changed via Preferences -> SAP HANA -> PlanViz -> Graph -> [  ] FetchAall and Fetch Size). This is one part of the longer runtime.

The other is that the PlanViz itself adds overhead to the total runtime, though comparing absolute runtimes is not valid.

BTW: be aware that PlanViz -> execute  in fact executes and traces the statement. It's not a simple display of the query plan.

2) Analytical Search is the top dominant Operator
When I further get into details of "Analytical Search", I got below details but not sure how to interpret them

Like most other complex operations the Analytical Search is made up of the processing of many more granualar Plan Operators (POPs). The next step is not to dig into the details of the Analytical Search box but into what happens "under" this box.

3) In the "Executed Plan" tab I see all the boxes in different color borders (Pink, Yellow, Green, blue, red) and Arrows (grey, orange). Are those colors have any importance in the analysis?

Yes, they do have a significance:

4) Each box in the "Executed Plan" tab has "Inclusive time" and "Exclusive time". What are they?

5) What is SUBTREE_COST in the query Explain plan?

Execution plans can be understood as directed graphs of data flows. Most operations have preceeding operations that feed data that has been processed so far into them.

To understand what part of the cost/the time associated with any operation belongs to the operation itself and which part belongs to the preceeding operations, you find the Inclusive time and the SUBTREE_COST.

Exclusive Time on the other hand gives you only the current operator's runtime.

[chandan_praharaj]

There is none. Please check the doc, from Lars, which surely would help.

Once I have responded here in the thread all the possible planViz help.