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.
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.
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!