常用SQL
select TO_TIMESTAMP('2015-10-21 23:30:45','yyyy-mm-dd hh24:mi:ss') from dummy;
1、正在执行的sql（推测）
SELECT C.CONNECTION_ID, PS.STATEMENT_STRING FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS ON C.CONNECTION_ID = PS.CONNECTION_ID AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID WHERE C.CONNECTION_STATUS = 'RUNNING' AND C.CONNECTION_TYPE = 'Remote'
2、杀会话
ALTER SYSTEM DISCONNECT SESSION ‘<CONNECTION_ID >‘
ALTER SYSTEM DISCONNECT SESSION '300736'
--ALTER SYSTEM CANCEL SESSION '300735'
--ALTER SYSTEM CANCEL WORK IN SESSION '300736'
3、hana studio sessions里的语句
SELECT TOP 1001 "Server Host",
"Server Port",
"Logical Connection ID",
"Created At",
"Seconds Since Last Statement Start",
"Connection status",
"Transaction status",
"Auto commit",
"Blocked by Connection ID",
"Blocks No. of Transactions",
"Application",
"Application Source",
"Application Version",
"Application User",
"Database User",
"Client Host",
"Client IP",
"Client Process ID",
"Current Statement Hash",
"Current Statement",
"Current Operator",
"Last Executed Statement",
"No. of Queries",
"Average No. of Records Fetched",
"Average Query Time in [ms]",
"No. of DML Statements",
"Average Time in [ms] per DML Statement",
"Average Wait Time [ms] per DML Statement",
"No. of DDL && Command Statements",
"Average DDL && Command Time in [ms]",
"Average DDL && Command Wait in [ms]",
"Average No. of Records Affected by DML && DDL Statements",
"Network Traffic Server to Client in [MB]",
"Network Traffic Client to Server in [MB]"
FROM (SELECT C.HOST AS "Server Host",
C.PORT AS "Server Port",
C.LOGICAL_CONNECTION_ID AS "Logical Connection ID",
C.START_TIME AS "Created At",
SECONDS_BETWEEN(STAT.LAST_EXECUTED_TIME, CURRENT_TIMESTAMP) AS "Seconds Since Last Statement Start",
IFNULL(CR.CONNECTION_STATUS, 'IDLE') AS "Connection status",
IFNULL(TX_STATUS.TRANSACTION_STATUS, 'INACTIVE') AS "Transaction status",
C.AUTO_COMMIT AS "Auto commit",
(SELECT IFNULL(TO_CHAR(MAX(C3.LOGICAL_CONNECTION_ID)),
'<not blocked>')
FROM M_BLOCKED_TRANSACTIONS BT2
JOIN M_TRANSACTIONS T2
ON BT2.BLOCKED_UPDATE_TRANSACTION_ID =
T2.UPDATE_TRANSACTION_ID
JOIN 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 M_TRANSACTIONS T3
ON T3.UPDATE_TRANSACTION_ID =
BT2.LOCK_OWNER_UPDATE_TRANSACTION_ID
AND T3.TRANSACTION_TYPE = 'USER TRANSACTION'
JOIN 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",
(SELECT COUNT(DISTINCT BT2.BLOCKED_UPDATE_TRANSACTION_ID)
FROM M_BLOCKED_TRANSACTIONS BT2
JOIN M_TRANSACTIONS T2
ON BT2.LOCK_OWNER_UPDATE_TRANSACTION_ID =
T2.UPDATE_TRANSACTION_ID
JOIN 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'
WHERE C2.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID) AS "Blocks No. of Transactions",
SC1.VALUE AS "Application",
SC2.VALUE AS "Application Source",
SC3.VALUE AS "Application Version",
SC4.VALUE AS "Application User",
C.USER_NAME AS "Database User",
C.CLIENT_HOST AS "Client Host",
C.CLIENT_IP AS "Client IP",
C.CLIENT_PID AS "Client Process ID",
SPC.STATEMENT_HASH AS "Current Statement Hash",
PS.STATEMENT_STRING AS "Current Statement",
CR.CURRENT_OPERATOR_NAME AS "Current Operator",
'' "Last Executed Statement",
STAT.SELECT_EXECUTION_COUNT AS "No. of Queries",
STAT.AVG_FETCHED_RECORDS AS "Average No. of Records Fetched",
STAT.SELECT_AVG_EXECUTION_TIME AS "Average Query Time in [ms]",
STAT.UPDATE_EXECUTION_COUNT AS "No. of DML Statements",
STAT.UPDATE_AVG_EXECUTION_TIME AS "Average Time in [ms] per DML Statement",
STAT.UPDATE_AVG_LOCK_WAIT_TIME AS "Average Wait Time [ms] per DML Statement",
STAT.OTHERS_EXECUTION_COUNT AS "No. of DDL && Command Statements",
STAT.OTHERS_AVG_EXECUTION_TIME AS "Average DDL && Command Time in [ms]",
STAT.OTHERS_AVG_LOCK_WAIT_TIME AS "Average DDL && Command Wait in [ms]",
STAT.AVG_AFFECTED_RECORDS AS "Average No. of Records Affected by DML && DDL Statements",
ROUND(STAT.SENT_MESSAGE_SIZE / 1024 / 1024, 2) AS "Network Traffic Server to Client in [MB]",
ROUND(STAT.RECEIVED_MESSAGE_SIZE / 1024 / 1024, 2) AS "Network Traffic Client to Server in [MB]"
FROM M_CONNECTIONS C
LEFT OUTER JOIN M_SESSION_CONTEXT SC1
ON SC1.HOST = C.HOST
AND SC1.PORT = C.PORT
AND SC1.CONNECTION_ID = C.CONNECTION_ID
AND SC1.KEY = 'APPLICATION'
LEFT OUTER JOIN M_SESSION_CONTEXT SC2
ON SC2.HOST = C.HOST
AND SC2.PORT = C.PORT
AND SC2.CONNECTION_ID = C.CONNECTION_ID
AND SC2.KEY = 'APPLICATIONSOURCE'
LEFT OUTER JOIN M_SESSION_CONTEXT SC3
ON SC3.HOST = C.HOST
AND SC3.PORT = C.PORT
AND SC3.CONNECTION_ID = C.CONNECTION_ID
AND SC3.KEY = 'APPLICATIONVERSION'
LEFT OUTER JOIN M_SESSION_CONTEXT SC4
ON SC4.HOST = C.HOST
AND SC4.PORT = C.PORT
AND SC4.CONNECTION_ID = C.CONNECTION_ID
AND SC4.KEY = 'APPLICATIONUSER'
LEFT OUTER JOIN M_CONNECTIONS CR
ON CR.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID
AND CR.CONNECTION_STATUS != 'IDLE'
LEFT OUTER JOIN M_ACTIVE_STATEMENTS PS
ON CR.HOST = PS.HOST
AND CR.PORT = PS.PORT
AND CR.CONNECTION_ID = PS.CONNECTION_ID
AND CR.CURRENT_STATEMENT_ID = PS.STATEMENT_ID
LEFT OUTER JOIN M_SQL_PLAN_CACHE SPC
ON SPC.HOST = PS.HOST
AND SPC.PORT = PS.PORT
AND SPC.PLAN_ID = PS.PLAN_ID
JOIN (SELECT C1.LOGICAL_CONNECTION_ID,
MAX(ST.LAST_EXECUTED_TIME) AS LAST_EXECUTED_TIME,
SUM(C1.SENT_MESSAGE_SIZE) AS SENT_MESSAGE_SIZE,
SUM(C1.RECEIVED_MESSAGE_SIZE) AS RECEIVED_MESSAGE_SIZE,
SUM(ST.SELECT_EXECUTION_COUNT) AS SELECT_EXECUTION_COUNT,
CASE SUM(ST.SELECT_EXECUTION_COUNT)
WHEN 0 THEN
0
ELSE
ROUND(SUM(C1.FETCHED_RECORD_COUNT) /
SUM(ST.SELECT_EXECUTION_COUNT),
2)
END AS AVG_FETCHED_RECORDS,
ROUND(TO_DECIMAL(AVG(ST.SELECT_AVG_EXECUTION_TIME) / 1000),
2) AS SELECT_AVG_EXECUTION_TIME,
SUM(ST.UPDATE_COUNT) AS UPDATE_EXECUTION_COUNT,
ROUND(TO_DECIMAL(AVG(ST.UPDATE_AVG_EXECUTION_TIME) / 1000),
2) AS UPDATE_AVG_EXECUTION_TIME,
ROUND(TO_DECIMAL(AVG(ST.UPDATE_AVG_LOCK_WAIT_TIME) / 1000),
2) AS UPDATE_AVG_LOCK_WAIT_TIME,
SUM(ST.OTHERS_COUNT) AS OTHERS_EXECUTION_COUNT,
ROUND(TO_DECIMAL(AVG(ST.OTHERS_AVG_EXECUTION_TIME) / 1000),
2) AS OTHERS_AVG_EXECUTION_TIME,
ROUND(TO_DECIMAL(AVG(ST.OTHERS_AVG_LOCK_WAIT_TIME) / 1000),
2) AS OTHERS_AVG_LOCK_WAIT_TIME,
CASE SUM(ST.OTHERS_COUNT + ST.UPDATE_COUNT)
WHEN 0 THEN
0
ELSE
ROUND(SUM(C1.AFFECTED_RECORD_COUNT) /
SUM(ST.OTHERS_COUNT + ST.UPDATE_COUNT),
2)
END AS AVG_AFFECTED_RECORDS
FROM M_CONNECTIONS C1, M_CONNECTION_STATISTICS ST
WHERE C1.HOST = ST.HOST
AND C1.PORT = ST.PORT
AND C1.CONNECTION_ID = ST.CONNECTION_ID
GROUP BY C1.LOGICAL_CONNECTION_ID) STAT
ON STAT.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID
LEFT OUTER JOIN (SELECT CT.LOGICAL_CONNECTION_ID,
MAX(TR.TRANSACTION_STATUS) AS TRANSACTION_STATUS
FROM M_TRANSACTIONS TR, M_CONNECTIONS CT
WHERE TR.HOST = CT.HOST
AND TR.PORT = CT.PORT
AND TR.CONNECTION_ID = CT.CONNECTION_ID
AND TR.TRANSACTION_STATUS != 'INACTIVE'
GROUP BY CT.LOGICAL_CONNECTION_ID) TX_STATUS
ON TX_STATUS.LOGICAL_CONNECTION_ID = C.LOGICAL_CONNECTION_ID
WHERE C.CONNECTION_TYPE = 'Remote'
AND C.LOGICAL_CONNECTION_ID = C.CONNECTION_ID
ORDER BY /* Connection status */ 6 DESC, /* Logical Connection ID */
3)
ORDER BY "Logical Connection ID" ASC
浙公网安备 33010602011771号