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!!
Rajarajeswari Kaliyaperumal
Author of the book 'SAP HANA 2.0 Installation and Administration A Practical Guide'
Bluesky
