Summary about HANA Query execution and optimization -Good to know about SAP HANA

[pallab_haldar]

Today I am going to discuss how when a query executed in SAP HANA, How it is processed and  Optimized and generated a plan and push to a engine. This concept is will help you to optimize HANA Query in latter stage.

The summary architectural diagram is given below which is provided by SAP in the below URL -

Architectural diagram by SAP

Process occur Step by Step : 

1. When query executed from any processor it first push to HANA Query processor and check inside the SQL_PLAN_CACHE if there is already a build plan is available or not.

• A. For the first time no execution plan will be available and the query push to the SQL optimizer through SQL front end.


• In second time when the query will be executed the build plan or generated plan  will be in Sql plan cache and will take the result from it . The generated plan directly push to the Engine of row store and column store as per scenario.


• Note : If the query executed with HINT (IGNORE_PLAN_CACHE) then all step will be executed one by one.

2. SQL Optimizer has two parts : 

A. Query Optimizer : When a query come it convert into optimized tree. How it is doing it will go it one by one .

Query may be build in different sequence to execute operator (like select, where act.) . But Hana follow execution of the operator in below priority of execution sequence or the operator to optimize the query .

There two types of optimization operation performed inside Query Optimizer -

• Rule based Optimization : In this optimization the query first converted into a tree. Then apply rules and sequence which I described above and generate optimized tree. The generated tree then pass to the next step Cost based optimization.

 

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;

       

 

 

• Cost based Optimization: In this optimization using different Enumerator like AGGR_THRU_JOIN or JOIN_THRU_AGGR generate multiple copy of the plan and check which plan is cost effective. Lest cost plan then sent to the next stage to generate the build execution plan.

B. Query Execution Module : In this module build and generate the provided execution plan from previous step and push to the corresponding execution engine which is suitable for the model and query i,e. Calculation, OLAP or Join engine.

Note : Using Hint you can forcefully change the execution engine.

 

SAP provides a good query to check the M_SQL_PLAN_CACHE which is given below -

 

--CHECK M_SQL_PLAN_CACHE--



SELECT

STATEMENT_STRING,

STATEMENT_HASH,

EXECUTION_COUNT,

PREPARATION_COUNT,

PARAMETER_COUNT,

LAST_EXECUTION_TIMESTAMP,

LAST_PREPARATION_TIMESTAMP

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;

 

Hope this will help..