My experience with the SAP HANA SQL Plan Analyzer
I fear monitoring logs. As a developer, I hope for the best and have an aversion to looking into he logs or preparing test cases. In fact, I hate to write test cases as internally I am fearful of facing the reality of my code.
Recently I had a scenario with HANA SQL queries on existing calculation views. The calculation views were just a replica of the standard tables. So, the logic of their joins and selects was written on the fly in Power BI.
As a good practice, we have an ODBC limit set for the size of the query response. So, any query that returns entries beyond the set limit simply fails within error — Allocation limit exceeded.
Now, I had this query which failed multiple times. I modified aspects of the query but to no avail. I struggled to find the actual piece of query that needs to be improved. I was simply aiming in the dark with trial and error method.
Finally, I decided to use the SQL Statement Monitoring option in the SAP HANA Cloud Cockpit. After some navigation, I found the SQL Statement section.
I found multiple SQL statements in the SQL Plan cache tab. However, I chose to filter them by executed date and user.
On examination of the queries(clicking on the More button), I zeroed in on the query of my interest. On clicking More — we get the option to download the SQL Plan. This .plv file was magical in terms of the decomposition of my SQL statement which was executed on the server.
Get Sharath MG’s stories in your inbox
Join Medium for free to get updates from this writer.
.plv files are supported on Business Application Studio. In my dev space, I maintain a test folder(which is never checked into my git repo). I loaded the .plv file into this test folder. On opening the file the editor prompts you to open the SQL Plan Analyzer. When I opened the file, I found my SQL mapped out.
On opening the SQL Analyzer, I was presented with a Plan Graph. The Graph laid out the SQL breakup and the no. of records which were pulled up at each step. It also provided options to look up the broken down query and the logic applied at each step. In order to get a comparative study, I executed a query which did pass through without any exception. In this query, I maintained a very short timeline with the sole intention to get its graph for purposes of comparison.
Upon analysis of both graphs, I found that the number of records in the Index Scan was consistent with both plans. However, at the Table Scan section, the numbers varied drastically. I found that the blocked query had 24 times the number of records of the successful query.
I didn’t need an SQL expert(for am not one) to tell the point of concern and the point to be fixed.
I found other options to add comments and bookmark like a document to share this graph with teams. This is very useful for reviews when we can check the time of execution of queries and the response size.
I intend to maintain them on Git as reference points for commonly executed queries. They serve as a learning exercise for new team members on the project. Also, they help us to compare the queries when faced with limit issues.
Apart from the plan analyzer, I explored more Workload Analysis, CPU usage, and Memory usage extremely useful to get an insight into the current state of views on HANA and prepare for the future. More on these sections, when I actually use them for a use case.
In conclusion, I found SQL Plan Analyzer a great learning and monitoring tool in the cockpit. Hope you get a chance to explore its features.
Note: The views expressed in this article are purely personal.
