Technical Articles
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.