Technical Articles
How to check the number of duplicate queries in HANA
Duplicate queries in HANA:
1.CURRENTLY ACTIVE DUPLICATE QUERIES:
In HANA, in case of performance issues, there can be cases where user sends multiple duplicate queries when their initial execution did not provide the desired results . Users despite closing the window, HANA in many cases fails to terminate the query run of the previous run.
To detect such runs we can use below query:
======
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 (‘ALEREMOTE’,’SAPERP’,’SAPTM’)),
CTE2 AS (SELECT HOST, USER_NAME,SQL_STATEMENT, COUNT(SQL_STATEMENT) AS CNT FROM CTE
GROUP BY HOST, USER_NAME,SQL_STATEMENT order by SQL_STATEMENT
)
SELECT * FROM CTE2
WHERE CNT > 1
===============
NOTE:Ensure to correct the single quotes while using as copying changes it
In above if we do not want certain users we can add it in USER_NAME not in list .
2.HISTORIC DUPLICATE QUERIES FROM M_EXPENSIVE_STATEMENTS:
=====
select CONNECTION_ID,STATEMENT_HASH,SUBSTRING(STATEMENT_STRING,1,2000) SQL_STATEMENT,DB_USER,left(START_TIME,16) TIME_START ,OPERATION,RECORDS,MEMORY_SIZE/1024/1024/1024 from M_EXPENSIVE_STATEMENTS
where OPERATION=’AGGREGATED_EXECUTION’ and START_TIME between ‘2021-02-02 02:00:00’ and ‘2021-02-02 05:19:00′ and DB_USER=’DANFRTS’ order by STATEMENT_HASH),
CTE2 AS (SELECT SQL_STATEMENT,COUNT(SQL_STATEMENT) AS CNT,TIME_START FROM CTE group by TIME_START,SQL_STATEMENT order by SQL_STATEMENT)
select * from CTE2 where CNT>1;
Thanks for reading!
Follow for more such posts!
Like and leave a comment or suggestion if any!