Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
rajarajeswari_kaliyaperum
Active Participant

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!rajarajeswari_kaliyaperumal
Please leave a comment or suggestion!

 

 

 
Labels in this area