Active statements in HANA DB

ACTIVE STATEMENTS OF HANA DB:

1.Below SQL will help with the list of currently running statements in HANA along with its memory usage, runtime since its cursor was open, is some blocked transactions are involved, which SQL statement, which DB and application level user and from which application , involved connection id etc.

Note: A built in view for the same is already present in HANA Cockpit in tile – Monitor Statements , however the below SQL comes handy which is the backend query used by Monitor statement tile.

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

select      PS.CONNECTION_ID AS “Connection ID”,      SC1.VALUE AS “Application”,       SC4.VALUE AS “Application User”,       C.USER_NAME AS “Database User”,       PS.STATEMENT_STRING AS “Current Statement”,        (SELECT IFNULL(TO_CHAR(MAX(C3.LOGICAL_CONNECTION_ID)), NULL) FROM SYS.M_BLOCKED_TRANSACTIONS BT2           JOIN SYS.M_TRANSACTIONS T2 ON BT2.BLOCKED_UPDATE_TRANSACTION_ID = T2.UPDATE_TRANSACTION_ID          JOIN SYS.M_CONNECTIONS C2 ON T2.HOST = C2.HOST                                AND T2.PORT = C2.PORT                                AND T2.TRANSACTION_ID = C2.TRANSACTION_ID                                AND T2.TRANSACTION_TYPE = ‘USER TRANSACTION’           JOIN SYS.M_TRANSACTIONS T3 ON T3.UPDATE_TRANSACTION_ID = BT2.LOCK_OWNER_UPDATE_TRANSACTION_ID                                AND T3.TRANSACTION_TYPE = ‘USER TRANSACTION’           JOIN SYS.M_CONNECTIONS C3 ON T3.HOST = C3.HOST                                AND T3.PORT = C3.PORT                                AND T3.TRANSACTION_ID = C3.TRANSACTION_ID          WHERE C2.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID ) AS “Blocked by Connection ID”,       ROUND(NANO100_BETWEEN(PS.LAST_ACTION_TIME, CURRENT_TIMESTAMP)/10000) AS “Runtime in Milliseconds Since”,      ROUND(NANO100_BETWEEN(BT.BLOCKED_TIME, CURRENT_TIMESTAMP)/10000) AS “Blocked in Milliseconds Since”,      PS.STATEMENT_STATUS AS “Statement_Status”,      ROUND(NANO100_BETWEEN(PS.LAST_EXECUTED_TIME, CURRENT_TIMESTAMP)/10000) AS “Cursor Duration in Milliseconds Since”,      PS.WORKLOAD_CLASS_NAME, PS.USED_MEMORY_SIZE, PS.ALLOCATED_MEMORY_SIZE, PS.AVG_EXECUTION_MEMORY_SIZE, PS.MAX_EXECUTION_MEMORY_SIZE,      PS.MIN_EXECUTION_MEMORY_SIZE, PS.TOTAL_EXECUTION_MEMORY_SIZE   FROM SYS.M_ACTIVE_STATEMENTS PS       LEFT OUTER JOIN SYS.M_SESSION_CONTEXT SC1 ON SC1.HOST = PS.HOST                                                  AND SC1.PORT = PS.PORT                                                 AND SC1.CONNECTION_ID = PS.CONNECTION_ID                                                  AND SC1.KEY = ‘APPLICATION’             LEFT OUTER JOIN SYS.M_SESSION_CONTEXT SC4 ON SC4.HOST = PS.HOST                                                  AND SC4.PORT = PS.PORT                                                  AND SC4.CONNECTION_ID = PS.CONNECTION_ID                                                  AND SC4.KEY = ‘APPLICATIONUSER’             LEFT OUTER JOIN SYS.M_CONNECTIONS C ON C.HOST = PS.HOST                                                   AND C.PORT = PS.PORT                                                   AND C.CONNECTION_ID = PS.CONNECTION_ID                                                   AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID             LEFT OUTER JOIN SYS.M_BLOCKED_TRANSACTIONS BT ON BT.HOST = PS.HOST                                                 AND BT.PORT = PS.PORT                                                 AND BT.BLOCKED_CONNECTION_ID = PS.CONNECTION_ID   order by “Runtime in Milliseconds Since” desc

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

2.Another SQL statement that helps with the same purpose is below . It helps with the exact memory utilization per statement.

select
c.host, c.user_name, c.connection_status, c.transaction_id, s.last_executed_time,
round(s.allocated_memory_size/1024/1024/1024,2) as “Alloc Mem (GB)”,
round(s.used_memory_size/1024/1024/1024,2) as “Used Mem (GB)”, s.statement_string
from
m_connections c, m_prepared_statements s
where
s.connection_id = c.connection_id and c.connection_status != ‘IDLE’
order by
s.allocated_memory_size desc

Thanks for reading!
Follow for more such posts!https://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!