How to find out duplicate SQL reports run by the same user by mistake during performance issue

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.