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!Rajarajeswari Kaliyaperuumal Like and leave a comment or suggestion if any!
，爱回扣返现网，云的服务器，计算机大数据，企业软件公司，快云服务器
