本文于 663 天前发布，最后更新于 63 天前
一、简单概述
通俗来讲，会话(Session) 是通信双方从开始通信到通信结束期间的一个上下文（Context）。这个上下文是一段位于服务器端的内存：记录了本次连接的客户端机器、通过哪个应用程序、哪个用户登录等信息。
连接（Connection）：连接是从客户端到ORACLE实例的一条物理路径。连接可以在网络上建立，或者在本机通过IPC机制建立。通常会在客户端进程与一个专用服务器或一个调度器之间建立连接。
会话(Session) 是和连接(Connection)是同时建立的，两者是对同一件事情不同层次的描述。简单讲，连接(Connection)是物理上的客户端同服务器的通信链路，会话(Session)是逻辑上的用户同服务器的通信交互。
二、常用会话相关
2.1 总览会话指标
SELECT 'ACTIVE_SESSION:' AS SESS,COUNT(1) AS COUNT FROM V$SESSIONS WHERE STATE='ACTIVE' UNION ALL
SELECT 'CURRENT_SESSION:',COUNT(1) FROM V$SESSIONS UNION ALL
SELECT 'MAX_SESSION:',PARA_VALUE FROM V$DM_INI WHERE PARA_NAME='MAX_SESSIONS';可以加个时间戳-disql截图
SELECT 'ACTIVE_SESSION:' AS SESS, CAST(COUNT(1) AS VARCHAR) AS COUNT FROM V$SESSIONS WHERE STATE='ACTIVE' UNION ALL
SELECT 'CURRENT_SESSION:' AS SESS, CAST(COUNT(1) AS VARCHAR) AS COUNT FROM V$SESSIONS UNION ALL
SELECT 'MAX_SESSION:' AS SESS, PARA_VALUE AS COUNT FROM V$DM_INI WHERE PARA_NAME='MAX_SESSIONS' UNION ALL
SELECT 'QUERY_TIME:' AS SESS, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS COUNT FROM DUAL;2.2 活动会话
SELECT SESS_ID,
THRD_ID,
TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
STATE,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = S.TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
--APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS S
WHERE STATE = 'ACTIVE'
AND SESS_ID <> SESSID -- 排除当前会话
ORDER BY 4 DESC;2.3 活动会话+未提交事务
-- 达梦数据库：活动会话 + 未提交的事务（空闲但有活跃DML事务）
SELECT SESS_ID,
THRD_ID,
TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
STATE,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = S.TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS S
WHERE STATE = 'ACTIVE'
AND SESS_ID <> SESSID -- 排除当前会话
UNION ALL
SELECT SESS_ID,
THRD_ID,
T1.TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
STATE,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = T1.TRX_ID) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS T1
WHERE SESS_ID <> SESSID -- 排除当前会话
AND STATE = 'IDLE'
AND T1.TRX_ID > 0 -- 存在事务ID
AND EXISTS ( -- 关联活跃的且有DML操作的事务
SELECT 1
FROM V$TRX T2
WHERE T2.ID = T1.TRX_ID
AND T2.STATUS = 'ACTIVE'
AND (T2.INS_CNT > 0
OR T2.UPD_CNT > 0
OR T2.DEL_CNT > 0
OR T2.UPD_INS_CNT > 0) )
ORDER BY 4 DESC; -- 按 EXECTIME(S) 降序2.4 SQL查询会话数
--当前数据库连接的会话总数
SELECT COUNT(*) FROM V$SESSIONS;
--当前数据库活动的会话总数
SELECT COUNT(*) FROM V$SESSIONS WHERE STATE='ACTIVE';
--详细查询
SELECT SQL_TEXT,CLNT_IP,STATE,COUNT(*) FROM V$SESSIONS GROUP BY SQL_TEXT,CLNT_IP,STATE ORDER BY STATE,COUNT(*) DESC;2.5 Linux命令查会话总数
lsof -i:5236|grep dmserver|wc -l
或者
netstat -nat|awk '{print $4}'|grep 5236|wc -l2.6 查看最大会话数配置
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI WHERE PARA_NAME = 'MAX_SESSIONS';
--或者
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'MAX_SESSIONS';2.7 调整MAX_SESSIONS[评估SQL]
SELECT
*,
ROUND(总的 / (SELECT
TRUNC(SUM(TOTAL_SIZE * 1.0) / 1024 / 1024)
FROM
V$MEM_POOL), 2) * 100 || '%' 占总的百分比,
ROUND(总的 / DECODE(水位, 0, 1, 水位), 2) * 100 || '%' 与水位百分比
FROM
(SELECT
REGEXP_REPLACE(NAME, '[0-9]') 类别,
COUNT(*) 池数,
TRUNC(SUM((ORG_SIZE / 1024.0 / 1024))) 初始,
TRUNC(SUM((DATA_SIZE / 1024.0 / 1024))) 在用,
TRUNC(SUM((TOTAL_SIZE / 1024.0 / 1024))) 总的,
TRUNC(SUM((TARGET_SIZE / 1024.0 / 1024))) 水位
FROM
V$MEM_POOL
GROUP BY
REGEXP_REPLACE(NAME, '[0-9]')
UNION ALL
SELECT
'MEM_TOTAL',
1,
TRUNC(SUM((ORG_SIZE / 1024.0 / 1024))) 初始,
TRUNC(SUM((DATA_SIZE / 1024.0 / 1024))) 在用,
TRUNC(SUM(TOTAL_SIZE * 1.0) / 1024 / 1024) 总的,
TRUNC(SUM((TARGET_SIZE / 1024.0 / 1024))) 水位
FROM
V$MEM_POOL) ALL_MEM
WHERE
总的 > 0
ORDER BY
总的 DESC;查看SESSION占总的百分比，评估可以调整的幅度。
实际配置不建议超过推荐值的150%，配置过大的话，业务高峰期间会话数太高，有发生OOM的可能。
【推荐值：参数优化脚本根据服务器配置执行后所得的值】
2.8 清除所有会话
BEGIN
FOR REC IN (SELECT * FROM V$SESSIONS WHERE SESS_ID <> SESSID()) LOOP
SP_CLOSE_SESSION(REC.SESS_ID);
END LOOP;
END;2.9 IO等待、逻辑读、物理读
SELECT
ST.IO_WAIT_TIME,
ST.LOGIC_READ_CNT,
ST.PHY_READ_CNT,
DATEDIFF (MS, S.LAST_RECV_TIME, CURRENT_TIMESTAMP) AS TIME_USED,
DBMS_LOB.SUBSTR (SF_GET_SESSION_SQL (S.SESS_ID)) AS FULLSQL,
S.STATE,
ST.*
FROM
V$SQL_STAT ST
INNER JOIN V$SESSIONS S ON ST.SESSID = S.SESS_ID
WHERE
S.STATE = 'ACTIVE'
ORDER BY
LOGIC_READ_CNT DESC,
IO_WAIT_TIME DESC;2.10 空闲会话清理
SELECT SESS_ID,
THRD_ID,
S.TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS IDLE_SECONDS,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
--APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
CASE
WHEN TRX_ID = 0
THEN '无事务'
ELSE '无活动DML事务（可安全清理）'
END AS RISK_LEVEL,
--'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS S
WHERE S.STATE = 'IDLE'
AND NOT EXISTS ( SELECT 1
FROM V$TRX T
WHERE T.ID = S.TRX_ID
AND T.STATUS = 'ACTIVE'
AND (T.INS_CNT > 0
OR T.UPD_CNT > 0
OR T.DEL_CNT > 0
OR T.UPD_INS_CNT > 0) )
ORDER BY IDLE_SECONDS DESC;2.11 根据ID查询会话的存储过程
--分别通过SESS_ID、THRD_ID、TRX_ID定位会话
--GET_SESSION_BY_ID
CREATE OR REPLACE PROCEDURE SYSDBA.GSBD( P_TYPE IN VARCHAR2, -- 查询类型：'SESS', 'THRD', 'TRX'
P_ID IN BIGINT -- 对应的 ID 值
)
AS
BEGIN
-- 根据类型选择不同的查询
IF P_TYPE = 'SESS' THEN
-- 按 SESS_ID 查询
SELECT SESS_ID,
THRD_ID,
TRX_ID,
STATE,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION (' || SESS_ID || ')' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ')' AS CLOSE_CMD
FROM V$SESSIONS
WHERE SESS_ID = P_ID
AND SESS_ID <> SESSID; -- 排除当前会话
ELSIF P_TYPE = 'THRD' THEN
-- 按 THRD_ID 查询
SELECT SESS_ID,
THRD_ID,
TRX_ID,
STATE,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION (' || SESS_ID || ')' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ')' AS CLOSE_CMD
FROM V$SESSIONS
WHERE THRD_ID = P_ID
AND SESS_ID <> SESSID;
ELSIF P_TYPE = 'TRX' THEN
-- 按 TRX_ID 查询
SELECT SESS_ID,
THRD_ID,
TRX_ID,
STATE,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION (' || SESS_ID || ')' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ')' AS CLOSE_CMD
FROM V$SESSIONS
WHERE TRX_ID = P_ID
AND SESS_ID <> SESSID;
ELSE
-- 非法类型提示
RAISE_APPLICATION_ERROR( - 20001, '参数 P_TYPE 必须是 SESS, THRD 或 TRX');
END IF;
END;
/
--调用方法
CALL SYSDBA.GSBD('SESS', 123);
CALL SYSDBA.GSBD('THRD', 456);
CALL SYSDBA.GSBD('TRX', 7890);2.12 查找全表扫描的高频SQL
WITH EXEC_STATS AS (
SELECT SQL_ID, MAX(N_EXEC) AS N_EXEC
FROM V$SQLTEXT
GROUP BY SQL_ID
)
SELECT
H.SQL_ID,
H.TOP_SQL_TEXT, -- 历史记录中的 SQL 文本（可直接用于查看）
H.SQL_PLAN, -- 执行计划文本（包含操作符和代价）
E.N_EXEC AS 执行次数
FROM
V$PLN_HISTORY H
JOIN EXEC_STATS E ON H.SQL_ID = E.SQL_ID
WHERE
REGEXP_LIKE(H.SQL_PLAN, '#CSCN2: *\[ *([1-9][0-9]{2,}),') -- 代价 ≥ 100 的全表扫描
AND E.N_EXEC > 0 -- 只统计有执行记录的 SQL
AND H.TOP_SQL_TEXT NOT LIKE '%/*+%'
ORDER BY
E.N_EXEC DESC; -- 执行次数高的排在前面三、阻塞相关
3.1 一条SQL查阻塞
因为结果集按WAIT_TIME降序排序，所以最上面的事务大概率就是阻塞源
WITH A AS
(SELECT SESS_ID AS WAITOR_SESSID,
ID AS WAITOR,
SQL_TEXT AS WAITOR_SQL,
WAIT_TIME,
WAIT_FOR_ID -- 所等待的事物ID
FROM SYS."V$SESSIONS" V,
V$TRXWAIT T
WHERE TRX_ID = ID
)
,
B AS
(SELECT SESS_ID AS WAIT_FOR_SESSID,
SQL_TEXT AS WAIT_FOR_SQL,
ID AS WAITOR
FROM SYS."V$SESSIONS" V,
V$TRXWAIT T
WHERE TRX_ID = WAIT_FOR_ID
)
SELECT A.WAITOR_SESSID,
A.WAITOR,
A.WAITOR_SQL,
A.WAIT_TIME / 1000 AS "WAITTIME(S)",
B.WAIT_FOR_SESSID,
A.WAIT_FOR_ID,
B.WAIT_FOR_SQL,
'SP_CANCEL_SESSION_OPERATION(' || WAIT_FOR_SESSID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || WAIT_FOR_SESSID || ');' AS CLOSE_CMD
FROM A,
B
WHERE A.WAITOR = B.WAITOR
ORDER BY 4 DESC;完善-带被阻塞事务的数量
WITH A AS
(SELECT SESS_ID AS WAITOR_SESSID,
SQL_TEXT AS WAITOR_SQL,
ID AS WAITOR,
WAIT_FOR_ID, -- 所等待的事物ID
WAIT_TIME
FROM SYS."V$SESSIONS" V,
V$TRXWAIT T
WHERE TRX_ID = ID
)
,
B AS
(SELECT SESS_ID AS WAIT_FOR_SESSID,
SQL_TEXT AS WAIT_FOR_SQL,
ID AS WAITOR
FROM SYS."V$SESSIONS" V,
V$TRXWAIT T
WHERE TRX_ID = WAIT_FOR_ID
)
SELECT WAITOR_SESSID,
WAITOR,
WAITOR_SQL,
WAIT_TIME / 1000 AS "WAITTIME(S)",
WAIT_FOR_SESSID,
WAIT_FOR_ID,
WAIT_FOR_SQL,
BLOCKED_COUNT,
'SP_CANCEL_SESSION_OPERATION(' || WAIT_FOR_SESSID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || WAIT_FOR_SESSID || ');' AS CLOSE_CMD
FROM (
SELECT A.*,
B.WAIT_FOR_SESSID,
B.WAIT_FOR_SQL,
COUNT(*) OVER (PARTITION BY B.WAIT_FOR_SESSID) AS BLOCKED_COUNT,
ROW_NUMBER() OVER (PARTITION BY B.WAIT_FOR_SESSID ORDER BY A.WAIT_TIME DESC) AS RN
FROM A, B
WHERE A.WAITOR = B.WAITOR
) t
WHERE RN = 1
ORDER BY 4 DESC;BLOCKED_COUNT列对应DEM中的数量，多条阻塞链会重复计算，但实际等待事务没有那么多。
第二条SQL查询的结果集有几条数据，实际就有几条会话在等待，也就是等待的实际数量。
通过第二条SQL可以清晰地看清阻塞链。
3.2 阻塞源头
SELECT SESS_ID,
THRD_ID,
TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
STATE,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = S.TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
--APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS S
WHERE S.TRX_ID IN (SELECT DISTINCT T.WAIT_FOR_ID
FROM V$TRXWAIT T
WHERE T.WAIT_FOR_ID NOT IN (SELECT ID FROM V$TRXWAIT));3.3 查看阻塞会话-简单
SELECT TW.*,SS.CLNT_IP,SESS_ID,SS.SQL_TEXT FROM V$TRXWAIT TW JOIN V$SESSIONS SS ON SS.THRD_ID=TW.THRD_ID ORDER BY WAIT_TIME DESC;
--或者
SELECT * FROM V$SESSIONS WHERE TRX_ID IN (SELECT WAIT_FOR_ID FROM V$TRXWAIT);3.4 查看阻塞会话源头-简单
--使用结果集的SQL清除
SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' FROM V$SESSIONS WHERE TRX_ID IN (SELECT WAIT_FOR_ID FROM V$TRXWAIT);
SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' FROM V$SESSIONS S, V$LOCK L WHERE S.TRX_ID = L.TRX_ID AND L.BLOCKED = 1;
--或者根据SESS_ID清除
SP_CLOSE_SESSION(SESS_ID);
--如果清除不掉，先取消，再清除
SP_CANCEL_SESSION_OPERATION(SESS_ID);
SP_CLOSE_SESSION(SESS_ID);3.5 阻塞源-SQL有问题
SELECT
BLOCKER.SESS_ID AS BLOCKER_SESSID,
BLOCKER.STATE AS BLOCKER_SESS_STATE,
BLOCKER.TRX_ID AS BLOCKER_TRXID,
SQLTEXT.SQL_ID AS BLOCKER_SQLID,
BLOCKED.SESS_ID AS BLOCKED_SESSID,
BLOCKED.TRX_ID AS BLOCKED_TRXID,
SUBSTR(SF_GET_SESSION_SQL(BLOCKER.SESS_ID), 0, 130) AS BLOCKER_FULLSQL,
REPLACE(BLOCKER.CLNT_IP, '::FFFF:') || '-' || BLOCKER.CLNT_HOST AS BLOCKER_CLNT_IP,
DATEDIFF(SS, BLOCKED.LAST_RECV_TIME, SYSDATE) AS "BLOCKED_TIME(S)",
TRXWAIT.WAIT_TIME / 1000 AS "WAIT_TIME(S)"
FROM
V$TRXWAIT TRXWAIT,
V$SESSIONS BLOCKED,
V$SESSIONS BLOCKER,
V$SQLTEXT SQLTEXT
WHERE
TRXWAIT.ID = BLOCKED.TRX_ID
AND TRXWAIT.WAIT_FOR_ID = BLOCKER.TRX_ID
AND TRXWAIT.WAIT_FOR_ID NOT IN (SELECT ID FROM V$TRXWAIT)
AND BLOCKER.SQL_TEXT = SQLTEXT.SQL_TEXT
ORDER BY
BLOCKER_SESSID DESC,
"BLOCKED_TIME(S)" DESC;3.6 被阻塞-SQL有问题
SELECT
BLOCKED.SESS_ID AS BLOCKED_SESSID,
BLOCKED.STATE AS BLOCKED_SESS_STATE,
BLOCKED.TRX_ID AS BLOCKED_TRXID,
BLOCKED.SQL_ID AS BLOCKED_SQLID,
BLOCKER.SESS_ID AS BLOCKER_SESSID,
BLOCKER.TRX_ID AS BLOCKER_TRXID,
SUBSTR(SF_GET_SESSION_SQL(BLOCKED.SESS_ID), 0, 130) AS BLOCKED_FULLSQL,
REPLACE(BLOCKED.CLNT_IP, '::FFFF:') || '-' || BLOCKER.CLNT_HOST AS BLOCKED_CLNT_IP,
DATEDIFF(SS, BLOCKED.LAST_RECV_TIME, SYSDATE) AS "BLOCKED_TIME(S)"
FROM
V$TRXWAIT TRXWAIT,
V$SESSIONS BLOCKED,
V$SESSIONS BLOCKER
WHERE
TRXWAIT.WAIT_FOR_ID = BLOCKED.TRX_ID
AND TRXWAIT.WAIT_FOR_ID = BLOCKER.TRX_ID
ORDER BY
BLOCKER_SESSID DESC,
"BLOCKED_TIME(S)" DESC;3.7 查看当前锁
SELECT * FROM V$LOCK;
--或者
SELECT
LC.LMODE,
LC.TABLE_ID,
LC.BLOCKED,
VTW.ID AS TRX_ID,
VS.SESS_ID,
VS.SQL_TEXT,
VS.APPNAME,
VS.CLNT_IP
FROM
V$LOCK LC
LEFT JOIN V$TRXWAIT VTW ON (LC.TRX_ID = VTW.ID)
LEFT JOIN V$TRX VT ON (VTW.ID = VT.ID)
LEFT JOIN V$SESSIONS VS ON (VT.SESS_ID = VS.SESS_ID)
WHERE
VS.SQL_TEXT IS NOT NULL;四、计划缓存相关
4.1 清除某张表的缓存计划
SELECT 'SP_CLEAR_PLAN_CACHE('||CACHE_ITEM||');' FROM V$CACHEPLN WHERE SQLSTR LIKE '%TABLENAME%';
--或者
SELECT * FROM V$CACHEPLN WHERE SQLSTR LIKE '%TABLENAME%';
SP_CLEAR_PLAN_CACHE(CACHE_ITEM);4.2 批量清除缓存计划
BEGIN
FOR RS IN (SELECT * FROM V$CACHEPLN WHERE SQLSTR LIKE '%SQL语句%') LOOP
EXECUTE IMMEDIATE 'SP_CLEAR_PLAN_CACHE(' || RS.CACHE_ITEM || ');';
END LOOP;
END;五、历史信息
5.1 历史慢SQL
SELECT
SQL_HIS.SEQ_NO,
SQL_HIS.SESS_ID,
SQL_HIS.TRX_ID,
SQL_HIS.TOP_SQL_TEXT,
SQL_HIS.TIME_USED / 1000 TIMEUSED_MS,
SQL_HIS.START_TIME,
UNIX_TIMESTAMP(SQL_HIS.START_TIME), --SQL_HIS.COMMAND_TYPE,
SESS_HIS.USER_NAME,
SESS_HIS.CLNT_IP,
SESS_HIS.APPNAME
FROM
V$SQL_HISTORY SQL_HIS
LEFT JOIN V$SESSION_HISTORY SESS_HIS ON SQL_HIS.SESS_ID = SESS_HIS.SESS_ID AND SQL_HIS.SESS_SEQ = SESS_HIS.SESS_SEQ
WHERE
SF_GET_EP_SEQNO(SQL_HIS.ROWID) = SF_GET_SELF_EP_SEQNO()
AND SQL_ID > 0
AND IS_OVER = 'Y'
AND CAST((TO_DATE(START_TIME, 'YYYY-MM-DD HH24:MI:SS') + CAST(TIME_USED / 1000000 AS INTERVAL SECOND)) AS DATETIME(0)) < SYSDATE
--AND TOP_SQL_TEXT NOT LIKE ? --排除某些SQL
--AND TIME_USED/1000/1000 >= 1 --执行耗时 1S 以上的SQL
--AND START_TIME BETWEEN '2025-07-18 00:00:00' AND '2025-07-18 23:59:59.999' --某一天
ORDER BY
SQL_HIS.TIME_USED DESC;5.2 历史最大连接数
SELECT
TO_CHAR(CREATE_TIME, 'YYYY-MM-DD HH24') HH,
COUNT(1) CNT
FROM
SYS.V$SESSION_HISTORY
GROUP BY
TO_CHAR(CREATE_TIME, 'YYYY-MM-DD HH24')
ORDER BY
1 DESC;六、一些可能常用的存储过程
6.1 会话总数突然增高时
CREATE OR REPLACE PROCEDURE SYSDBA.STC(p_minutes INT DEFAULT 10)
AS
BEGIN
-- 会话数高时查询
SELECT COUNT(*) CNT,
SQL_TEXT
FROM V$SESSIONS
WHERE CREATE_TIME >= SYSDATE - NUMTODSINTERVAL(p_minutes, 'MINUTE') -- 可以修改时间间隔
GROUP BY SQL_TEXT
ORDER BY 1 DESC;
END;调用
STC();
STC(30);查询一定时间范围内创建的会话，以SQL_TEXT计数排序，有可能能够快速定位造成会话数上涨的目标SQL和来源。
6.2 查询某SQL的会话
CREATE OR REPLACE PROCEDURE SYSDBA.QSS(p_sql_pattern VARCHAR2)
AS
BEGIN
-- 根据SQL查询会话
SELECT SESS_ID,
TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
REPLACE(REPLACE(REPLACE(SQL_TEXT, CHR(9), ''), CHR(10), ''), CHR(13), '') AS SQL_TEXT,
STATE, (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = S.TRX_ID
AND TRX_ID > 0) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
--APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
--TIME_ZONE,
--RDONLY,
--AUTO_CMT,
--CONN_TYPE,
--THRD_ID,
--CONNECTED,
--CUR_LINENO,
--CUR_MTDNAME,
--CUR_SQLSTR,
--CLNT_VER,
--SQL_ID,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS S
WHERE SQL_TEXT LIKE '%' || p_sql_pattern || '%'
AND SESS_ID <> SESSID
ORDER BY CREATE_TIME DESC;
END;调用
QSS('SQL部分内容');可结合6.1使用，定位到SQL_TEXT后，再根据QSS查询执行该SQL的所有会话信息。
6.3 管理工具未提交事务-参考未提交事务
CREATE OR REPLACE PROCEDURE SYSDBA.GMS
AS
BEGIN
SELECT SESS_ID,
THRD_ID,
T1.TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS "EXECTIME(S)",
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) AS "SQL", (SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT
FROM V$TRX
WHERE ID = T1.TRX_ID) AS "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
--APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS T1
WHERE APPNAME = 'manager.exe'
AND T1.TRX_ID > 0 -- 存在事务ID
AND SESS_ID <> SESSID -- 排除当前会话
AND EXISTS ( -- 关联活跃的且有DML操作的事务
SELECT 1
FROM V$TRX T2
WHERE T2.ID = T1.TRX_ID
AND T2.STATUS = 'ACTIVE'
AND (T2.INS_CNT > 0
OR T2.UPD_CNT > 0
OR T2.DEL_CNT > 0
OR T2.UPD_INS_CNT > 0) )
ORDER BY 4 DESC; -- 按 EXECTIME(S) 降序
END;6.4 当前正在执行的JOB
CREATE OR REPLACE PROCEDURE SYSDBA.GSJ
AS
BEGIN
SELECT SESS_ID,
THRD_ID,
TRX_ID,
DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) "EXECTIME(S)", --已执行时间S
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 3600 AS IDLE_HOURS,
--DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
TO_CHAR(SF_GET_SESSION_SQL(SESS_ID)) "SQL", --完整SQL
( SELECT INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT IDU
FROM V$TRX
WHERE ID = A.TRX_ID
AND TRX_ID > 0 ) "数据变化_IDU",
--CURR_SCH,
USER_NAME,
CLNT_HOST,
--APPNAME,
REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') AS CLNT_IP,
OSNAME,
CLNT_TYPE,
CREATE_TIME,
--LEFT(LAST_RECV_TIME, 19) AS LAST_RECV_TIME,
LEFT(LAST_SEND_TIME, 19) AS LAST_SEND_TIME,
'SP_CANCEL_SESSION_OPERATION(' || SESS_ID || ');' AS CANCEL_CMD,
'SP_CLOSE_SESSION(' || SESS_ID || ');' AS CLOSE_CMD
FROM V$SESSIONS A
WHERE STATE = 'ACTIVE'
AND SESS_ID <> SESSID
AND A.SQL_TEXT LIKE '%CREATE OR REPLACE TRIGGER%'
ORDER BY 4 DESC;
END;另附
相关的系统动态视图
- 显示会话的具体信息：V$SESSIONS
- 显示所有活动事务的信息：V$TRX
- 显示事务等待信息：V$TRXWAIT
- 显示活动事务视图信息：V$TRX_VIEW
- 显示当前系统中锁的状态：V$LOCK
- 显示死锁的历史信息：V$DEADLOCK_HISTORY
- 当前正在执行的SQL语句的资源开销：V$SQL_STAT
- 需要ENABLE_MONITOR=1
- 历史SQL语句执行的资源开销：V$SQL_STAT_HISTORY
- 需要ENABLE_MONITOR=1
- 显示系统最近1000条执行时间超过预定值的SQL语句：V$LONG_EXEC_SQLS
- 需要ENABLE_MONITOR=1、MONITOR_TIME=1
- 预定值可通过
SP_SET_LONG_TIME(5000);–修改为5S以上的SQL SELECT SF_GET_LONG_TIME();–可查看当前值
- 显示系统自启动以来执行时间最长的20条SQL语句：V$SYSTEM_LONG_EXEC_SQLS
- 需要ENABLE_MONITOR=1、MONITOR_TIME=1
