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
2,737

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:

 

=====

WITH CTE AS(
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;

=========



SAMPLE OUTPUT:




 

Thanks for reading!
Follow for more such posts!
Like and leave a comment or suggestion if any!
Labels in this area