How to find top most non-DDL statements which consumes highest Memory ,CPU,Runtime in HANA

M_EXPENSIVE_STATEMENT Vs MEMORY_SIZE,CPU_TIME,DURATION_MICROSEC

If you are a HANA DBA and you want to find out SQL statements that are consuming a very huge memory or CPU or or duration, the first point to examine is table M_EXPENSIVE_STATEMENTS.

COLUMN NAME:MEMORY_SIZE,CPU_TIME,DURATION_MICROSEC,ERROR_CODE

Sample SQL to manipulate this data:

A.Top 10 statements which consumer maximum memory and is successful in execution:

select  top 10 * from M_EXPENSIVE_STATEMENTS where ERROR_CODE = ‘0’  order by MEMORY_SIZE desc ;.

B.Top 10 statements which runs for longest duration and is successful in execution:

select  top 10 * from M_EXPENSIVE_STATEMENTS where ERROR_CODE = ‘0’ order by DURATION_MICROSEC desc ;

C.Top 10 statements which consumes maximum CPU and is successful in execution:

select  top 10 * from M_EXPENSIVE_STATEMENTS where ERROR_CODE = ‘0’ order by CPU_TIME desc ;

NOTE:In the same manner if you specify with ERROR_CODE <> ‘0’ , then we will be able to find the same details for failed statements in HANA DB.

So how does HANA detects a statement , if it is expensive or not ?

It simply relies upon the parameters below.

Global.in

[expensive_statement]

=>threshold_duration (us)

=> threshold_cpu_time(us)

=>threshold_memory(bytes)

The lesser you configure these values, the more entries you can find in the expensive statement trace. If we specify these parameter value as -1 , then all the statements will be recorded despite they do not match with the exact meaning of expensive in english.

Can we find all types of statement getting recorded in M_EXPENSIVE_STATEMENTS?

No.The statements that performs DDL operation gets recorded in a different table called M_EXECUTED_STATEMENTS and we can not find such statements in expensive statement trace.Rather we can find it in executed statement trace

Refer OSS 2180165 – FAQ: SAP HANA Expensive Statements Trace and 2366291 – FAQ: SAP HANA Executed Statements Trace for more details

Hope it was helpful!!
Click on like if you found this article useful and FOLLOW for more such articles!https://people.sap.com/rajarajeswari_kaliyaperumal
Please leave a comment or suggestion!