How to find frequently executed statement in HANA DB per client

Most frequently executed statements in HANA DB per application:

If you want to find out what are the frequently executed statement or query or a view in HANA  per client which connects to HANA DB ,below SQL will help with the same . Here I am displaying top 5 statements ,aggregated per client.(Eg Clients that connects here: -WebIntelligence,ABAP,Mashup Engine etc.

=========

with cte as (select distinct statement_hash, application_name,count(statement_hash) as num, rank() over ( partition by application_name order by count(statement_hash)desc) R from m_expensive_statements group by statement_hash,application_name order by application_name desc)

select * from cte where R in (‘1′,’2′,’3′,’4′,’5’);

======

The above sql outputs the top 5 statement hashes that each of the applications like SLT,Studio,ABAP,WebIntelligence etc along with the number of times it was executed in HANA DB based on the data available in M_EXPENSIVE_STATEMENTS .

If you want to find out the SQL associated with these statement hashes refer my below blog for more details.

https://blogs.sap.com/2020/09/16/how-to-find-sql-statement-from-sql-hash/

WARNING:

-The sub sql involved does not segregate between successful and failed statements. If you want to be very specific about checking on only successful statements alone, add an additional clause as ERROR_CODE=0 .For failed statements go for ERROR_CODE <> ‘0’

eg:

with cte as (select distinct statement_hash, application_name,count(statement_hash) as num, rank() over ( partition by application_name order by count(statement_hash)desc) R from m_expensive_statements where ERROR_CODE =’0′ group by statement_hash,application_name order by application_name desc)

select * from cte where R in (‘1′,’2′,’3′,’4′,’5’);

-If you have no records inside M_EXPENSIVE_STATEMENTS, it means you have not enabled the expensive statement trace .Hence before trying any of the above, ensure that you have enabled the expensive statement trace and with default threshold for memory,cpu and duration such as all the statements that are running in HANA db gets recorded.

Hope it was helpful!!
Click on like if you found this article useful and FOLLOW for more such articleshttp://people.sap.com/rajarajeswari_kaliyaperumal

Please leave a comment or suggestion!