In this blog I will discuss about the query processing steps inside SAP HANA i.e. how the SAP HANA process a query in a optimize way and what are SAP HANA component associate with the processing.
The main objective is to discuss about the techniques we will use to optimize query performance in a better way when business critical HANA query or HANA information view got stuck or taking to much time to execute. But that I will discuss one by one on the next blogs… to do that we need to know basic query processing architecture of HANA and steps in details which I will discuss in this blog.
There are two types query 1. Normal or regular query and 2. Parameterized Query. In this section we will discuss about the Normal or regular query processing .
The HANA components inside HANA server activity below –
1. SQL Plan cache : Used to store compiled Query with Execution Plan.
2. SQL Front End : which will check the syntactical error or semantically ok or not.
3. SQL Optimizer : This component has two sub component :
◉ Query Optimize Tree(Q) : It will convert the query in optimize tree as per HANA execution order and prepare the query for the next steps.
◉ Query Execution Plan(QE) : Compile the query in optimize tree and create execution Plan.
4. Execution Engine: There are two types of engine i.e.
◉ Row engine: Which process query for row store tables .SQL engine is a type of row engine.
◉ Column Engine: Which will process Query for column store data. Calculation engine, OLAP engine and Join Engine are the three types of Column type engine.
Engine process the execution plan prepared by the SQL Optimizer.
Now let discuss about the processing sequence when a query reached to HANA servers –
A. When No SQL Plan cache Found: The diagram describe when an Query is executed for the first time and no Plan cache found.
1. Query after entering inside HANA query processor first check the SQL plan cache if any compiled execution plan for the Query already exists or not. As the query is execution for the first time there will be no compiled execution plan store.
2. If no SQL Plan Cache found then the query routed to the SQL Front End. This component check the query for syntactical error or semantically ok or not. If everything is OK then query routed to the next step i.e. SQL Optimizer.
3. Inside the SQL optimizer, the first sub component QO(Query Optimizer Tree) convert the Query into Optimize Tree as per the HANA operator execution order(which I will discuss in my next blog).Then the tree is routed to the next sub component QE(Query Execution Plan) where the tree is converted i.e. and compiled into Execution Plan.
4. In the next step the one copy of the complied execution plan routed to the exaction engines and another copy routed back and store into SQL PLAN Cache.
The store complied execution plan can be view by the view M_SQL_PLAN_CACHE like the below query –
The result of the query will look like it –
5. Now the compiled plan is in the execution stage. If the execution plan deals with row store tables then the plan is executed by the SQL engine.
If the execution plan deals with column store tables then the plan is executed by the Calculation engine,OLAP engine or JOIN Engine.
B. When SQL Plan cache Found: The diagram describe when an Query is executed and compiled execution plan found in SQL Plan cache execution.
When the complied execution plan find the SQL Plan cache then the compiled execution plan for the query direct go to Execution engine and executed by the respective engines. i.e. If the execution plan deals with row store tables then the plan is executed by the SQL engine. If the execution plan deals with column store tables then the plan is executed by the Calculation engine,OLAP engine or JOIN Engine.
All the other component steps like SQL Front end, SQL Optimizer has been skipped in this process.
The above below process are for normal or regular query which do not have any parameter but for for parameterized query execution process, we will discuss in my next blog.
We can also manipulate the query execution flow manually by using hint – IGNORE_PLAN_CACHE. The query processing will ignore the component SQL plan cache and go through –
SQL Front End ==> SQL Optimizers==> Execution engines. No compiled execution plan will store in SQL plan cache. You can not see any data from M_SQL_PLAN_CACHE after execution of the query.
The diagram should look like below –
No comments:
Post a Comment