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
635
In HANa DB, there can be cases where one user might be re-executing the same query over again in case of performance issues or  just because they feel a time delay with their previous reports . As in HANA, it takes time for running queries to get cancelled , despite user closing the involved  session the statements that they had actually executed continues to run in background.

 

To detect such SQLs is quite hectic and hence below SQL helps with instant results :

======================

WITH CTE AS (

SELECT

C.HOST, C.USER_NAME,

SUBSTRING(S.STATEMENT_STRING,1,2000) SQL_STATEMENT

FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS S ON S.CONNECTION_ID = C.CONNECTION_ID

WHERE C.CONNECTION_STATUS != 'IDLE' and C.USER_NAME not in ('SAP<SID>','ALEREMOTE')),

CTE2 AS (SELECT HOST, USER_NAME,SQL_STATEMENT, COUNT(SQL_STATEMENT) AS CNT FROM CTE

GROUP BY HOST, USER_NAME,SQL_STATEMENT

)

SELECT * FROM CTE2

WHERE CNT > 1

=======================

NOTE:In above, add users like schema user or SLT user who can be excluded from this output . This is only valid for named user queries. In above I have added a clause to ignore SAP ABAP schema , as there can be lot of duplicates which are run by different jobs .

Below is the base query that was modified for this specific scenario .


 

Thanks for reading!

Please leave a comment and suggestion . Click on like if you find this article useful.

 

 
Labels in this area