HANA Cockpit SQL Analyzer / plan graph docu and guidelines

[eddy_declercq]

Hi,

Is there some good documentation / guidelines for the sql analyzer, more specifically the plan graph? Sure, there is note

2565156 and the online docu (https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.02/en-US/38a0454d1a2f4138bd47d99e09f3c094.html?q=plan%20graph)

, but all the latter is saying is "Open the Plan Graph tab to understand and analyze the execution plan of an SQL statement. In the Plan Graph tab you can open the Detail Properties view by clicking one of the operators. This view offers detailed information on the operator such as name, location, ID, summary."

That is not enough for making conclusion. Can someone point me to useful docu which explains the meaning/difference between row search/column search, hashed range join, filter, trex plan operator, inclusice cost and exclusive cost, etc? What is best and does one need to aim for?
Why aren't there any SQL performance recommendations, despite the heavy SQLstatements?

Many thanks in advance,

Eddy

[dvankempen]

You asked a question. Don't forget to mark the answer that helped you most as correct. Thanks!

For the readers, before you leave, don't forget to up/down vote the answers. You can vote on the question too.

Thanks.

[dvankempen]

Hi Eddy,

SQL analysis is documented in the administration guide and database explorer tool guide (how to) with more detailed information in the performance analysis guide from a DBA perspective.

For the developer point of view, there is the dedicated performance guide that covers the code.

You also may find the KBA from SAP Support of interest (one you already mentioned).

• Monitoring, Analyzing, and Improving Performance - SAP HANA Administration Guide for SAP HANA Platform 2.0 SPS 05
• Analyzing Statement Performance - SAP HANA Database Explorer 2.0 SP 12
• Query Plan Analysis - SAP HANA Troubleshooting and Performance Analysis Guide 2.0 SPS 05
• SQL Query Performance - SAP HANA Performance Guide for Developers 2.0 SPS 05

• 2565156 - How to use SQL Analyzer in SAP HANA Cockpit 2.0
• 2000002 - FAQ: SAP HANA SQL Optimization (listing many additional notes and KBAs).

There is no book yet (SAP Press or other) on topic, alas, which would indeed make for a great additional resource.

[lbreddemann]

Hi Eddy,

this part of the question really hits the nail on the head:

My main thing is: I've a plan graph in front of me showing a qry suspected to be not performant or heavy on resources. How do interpret properly what I see (without knowing the ins and outs of HANA as being a non admin)?

My view is, that without knowing how the query processing part of HANA works, the tools will stay black boxes and won't provide the information necessary to understand what parts of the processing make the query slower than expected.

The general approach for using a tool like PlanViz is to look for which parts of the query take the longest and then try to find out why that is the case.

This commonly requires to try and map back to the source query/model and try to decide whether what happens in the long-running plan operators seems reasonable.

Things to look out for are large intermediate result sets, often repeated groups of plan operators, and data type conversions.

Having written that I feel that it's important to also mention that most of the performance tools are not designed to provide the SQL developer with tips for better performance, but to investigate the query processing engines in HANA. The mindset for that is probably the "how can HANA core developers learn about what to improve"-kind.

In my experience, this is not different from most other DBMS in the market. Sure enough, for SQL Server, Oracle, mySQL, and PostgreSQL one can find metric tons of "developer tips" but to actually find and understand the cause of bad query performance expert know-how is required with all of them.

A notably different approach would be to give up on the idea of wanting to understand what makes the query slow and use the various performance/plan-stability/index-advisor tools that are available with most of the DBMS and also with HANA.
These tools commonly "just do their thing" (i.e. trying out a set of possible changes that can be applied to queries, compare the execution time and resource consumption and pick the best combination) and store the improved version of the execution plan in the DB. The developer typically does not learn from that.

Whether and how this approach makes sense is probably a separate discussion.

I understand that's not the answer you were looking for but the 2cts from my end.

Beyond that, it's part of my consulting business to teach internals and how to approach DB performance to developers.

[dvankempen]

Thanks lars.breddemann

[dvankempen]

Hi Eddy,

Performance tuning is often described as both an art and a science. If it was purely rule-based, it would have been automated and there would be no performance issues. There are too many variables and hence require interpretation and yes, this often requires considerable expertise (maybe machine learning will eventually solve this issue for us).

The topic is well documented but to make sense of the graphs and plans you will need some experience as well.

lars.breddemann and c9b9c8ea15574d29bfafe89e88ac94ec regularly write on the topic (highly recommended); maybe they can provide some expert advice on the topic? Danke!

[eddy_declercq]

Hi,

Thanks for the links. I guess I've read already most of them and I've been watching you videos in the academy.

But I'm still missing something, although it could be that it's me not understanding things properly.
My main thing is: I've a plan graph in front of me showing a qry suspected to be not performant or heavy on resources. How do interpret properly what I see (without knowing the ins and outs of HANA as being a non admin)?

Do I need to look at the row search/column search, hashed range join, filter, trex plan operator, inclusive cost and exclusive cost and what is the exact meaning of them in the context of performance/resource consumption? Which one(s) do I need to keep as low or as high as possible?

Why are there never any SQL performance recommendations? Is that due to the use of ABAP CDS or something else?

Many thanks,

Eddy