Today I am going to summarize the point how we can target to achieve best performance for a query and calculation view in HANA 2.0 SPS 03 and afterword’s version with scenario.
There are few ways by which we can analyze performance and tune it to get optimum one from the model –
1. Using EXPLAIN_ PLAN ( Available in HANA Studio, WEB IDE and BAS)(including HANA 1.0 XSC and HANA 2.0 XSA) : In the SQL editor of Database explorer in Web IDE or BAS execute the below query (for Calculation view extract the underlying query) to check the EXPLAIN PLAN.
-- Syntax EXPLAIN PLAN SET STATEMENT_NAME = 'TEST_SQL' FOR <SQL-QUERY> --
EXPLAIN PLAN SET STATEMENT_NAME = 'TEST_SQL'
FOR(
Select C.EMP_ID C.SALES_AMMOUNT from SALES_ITEAM C
LEFT OUTER JOIN ( SELECT D.EMP_ID,D.ADDRESS FROM EMPLOYEE D)
ON C.C.EMP_ID=D.EMP_ID
WHERE C.SALES_AMMOUNT > 1000
GROUP BY SALES_AMMOUNT);
if a query is available in M_SQL_PLAN_CACHE and you want to see the EXPLAIN_PLAN view of then we have to first find the ID of the ID plan using below query –
SELECT
PLAN_ID
FROM "M_SQL_PLAN_CACHE"
WHERE SCHEMA_NAME = 'PLB_TEST'
AND STATEMENT_STRING LIKE '%/*literal*/ Select C.EMP_ID C.SALES_AMMOUNT'
ORDER BY LAST_PREPARATION_TIMESTAMP DESC;Then using the plan id find the build Plan for the query –
EXPLAIN PLAN SET STATEMENT_NAME = 'TEST_SQL'
FOR SQL PLAN CACHE ENTRY '<PLAN_ID>';
Using the output below you can analyze the generated plant and their row and other parameter –
The below diagram provided by SAP using below reference Link –
Now Using hint you can change the subquery Plan execution and execution engine .
SELECT * FROM T1 WITH HINT( USE_OLAP_PLAN );
Note : This all time and result is an estimated value not executed values and Explain plan details generated during build.
2. Using SQL Trace (including HANA 1.0 XSC)/ Analyze SQL Script code (HANA 2.0 XSA) :
SQL Trace is very useful for procedures performance tuning which have multiple in Inline statements. You can easily track which statement is taking much more time and less time and according to that you can break the statement into multiple statements to optimize performance.
A. To enable SQL Trace execute the below query –
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'System' ) SET ('sqltrace', 'query_plan_trace') = 'on' WITH RECONFIGURE;
B. Execute the procedure or Query.
C. Install the SAP HANA SQL Trace Analyzer and configure it in Cockpit and you can check the trace.
This feature is available in HANA 1.0 XSC environment.
HANA XSA 2.0 also have the same kind of tool using SAP HANA Database explorer “Analyze SQL Script code ” we can check it.
3. Health Check mode (HANA XSA 2.0 Onwards) :
When you open a calculation view in editor mode you will have a health check mode which is a built-in procedure that evaluates the calculation model and provide you hint which action may optimize Query like cardinality or other.
In SQL console execute below procedures to check the hints.
CALL CHECK_ANALYTICAL_MODEL(”,’SCHEMA1′,’CV_DEMO_TEST’,?);
CALL CHECK_ANALYTICAL_MODEL('','SCHEMA1','CV_DEMO_TEST',?);
CALL "SYS"."CHECK_CALCULATION_VIEW"(
ACTIONNAME => ''/*<NVARCHAR(64)>*/,
SCHEMA_NAME => ''/*<NVARCHAR(256)>*/,
VIEW_NAME => ''/*<NVARCHAR(256)>*/
)The below is a sample output from SAP reference help –
Work according to the health and fix the issue.
4. Using Performance Analysis tab in Web IDE( AHANA XSA 2.0 SPS02 onwards :
This mode I have used widely to put the performance in Track.
In Web IDE for a calculation view there is Performance Analysis mode which provided the hint that found after analysis the plan and improve performance.
In performance analysis mode, information available on join tables, table partitions and other information, which helps you analyze your calculation view and fine tune it.
.
5. Using Plan Viz (HANA XSC HANA studio) or SQL Analyzer with SAP HANA Cloud in (BAS):
using this tool we can see which node is returning which ammourt of row and which node is causing problem. using HINT or prune inner join below the problem causing node we can get better performance for the Query .
To Put the plan Viz .PLV file into SQL Analyzer with SAP HANA Cloud we need to generate .PLV file from Database explorer and put/import it into the SQL analyzer.
There is a good blog which describes how to import the file in analyzer in details –
Now let’s start if we see in while analyzing the plan via SQL analyzer/Plan Viz that one node is returning large no of rows which causing slow performance. This you can able to figure out after executing the SQL analyzer/Plan Viz open inner plan -> logical.
Step 1 : Find the Dominant Operator and Possible key reducer:
Dominant operator is node which returning the large number of data causing the slow performance and possible key reducer will be the upper node which reduced the rows and return a small number of row and distinguishable.
Lets discuss about the scenario using the plan provided by SAP in the below link –
Here if you see Group by/ Aggregation node is the dominant operator and no 4 might be the possible key reducer as it reduce the return row significantly.
Note : Note 3 do not seems to be a possible key reducer as it is not reducing row significantly.
Step 2 : Push Down the possible key reducer node: In this scenario below the dominant operator node using HINT.
Here if we use JOIN_THRU_AGGR to push down the Join 4 (inner Join) node will push below –
Now the result Plan will look like below . The aggregation or GROUP BY move on the TOP.
Step 3 : Note that step 2 apply hint always not work and not result to accurate. In this scenario we narrow down on the subset by inner join with another table that exist in another branch and joined on the top nodes .
In this case T1 from other side we take and joined with T2 in Column search# 2
Note : Diagram provide by SAP on the below link –
Hope it will make your work easy in any situation where you need to optimize performance.
