SAP HANA Query Analysis and Optimization – using SQL Trace

I will discuss about the inbound SAP HANA Query Analysis and Optimization using SQL Trace.

There are three main analysis process by which we used to analysis HANA query and optimize it.

1. SQL Trace

2. Explain Plan

3. Visualized plan(PlanViz).

Today I will discuss about the analysis and Optimization of SAP HANA Query using SQL Trace. Before we use SQL trace the we need to use which scenario we will use SQL trace .For the below situation we will use SQL trace –

1. When there are many SQL statements coming from an application or report and we do bot know which query is causing a performance issue.

2. When we want to identify statements running internally as SQL script-based statements. Normal way we can not able to trace internal statements. we have to set the configuration which I will discuss latter in this blog.

3. When we want to identify the string that is causing problems.

We use SQL trace mostly for optimize performance for SQL script based statements like procedures, table function and Scripted calculation view. SQL trace capture all statement passes through SAN HANA server in user session. We will use the below procedure Optimization using SQL trace.

Steps : 

1. Configuration :

When the statement of an internally called procedure body (Here PROCEDURE_INTERNAL) is executed internally, the internal statement inside the internal procedure do not captured by the default SQL trace setting. These internal statements will be traced and logged when you enable the “internal statement” configuration setting. The enablement can be done from the administrative console or visa SQL. You must have to  I always prefer to do it via SQL by the below Query

2. Query chewing in the trace file :

Here We are assuming that the below internal query inside the internal procedure causing the problem.

First we will use configuration “all” enablement to identify the internal problematic query duration.

Then only using “query_plane_trace” we an retrieve the execution plan. Before that  We will use the following setting  to get the execution plane of the internally executed statement .

The similar below execution plan being generated in the trace file –

3. Extraction of explain plan for Internal statement to Excel and draw the Logical Plan:

Copy the above Explain Plan of the internal statement and put it into Excel which will look like below. Then draw the Logical Plan.

The converted Logical Plan will look like it –

Now we have got the logical Plan of the problematic query

4. Now Here the aggregation causing the issue with larger amount of intermediate result (heck the column OUTPUT SIZE in the excel – Here It is  3000000).

To solve this issue we will use SQL Hint JOIN_THRU_AGGR to push down the TOP inner join (as  it reduce the query) before the aggregation Group by.

if the the pushdown do not change the output result then the push down will took place, Otherwise redesign the logical Plan like below and then convert it into Query.

Using the above way you can check the problematic internal SQL statement which causing slow performance and redesign the Query.