一、简单概述

通俗来讲，会话(Session) 是通信双方从开始通信到通信结束期间的一个上下文（Context）。这个上下文是一段位于服务器端的内存：记录了本次连接的客户端机器、通过哪个应用程序、哪个用户登录等信息。

连接（Connection）：连接是从客户端到ORACLE实例的一条物理路径。连接可以在网络上建立，或者在本机通过IPC机制建立。通常会在客户端进程与一个专用服务器或一个调度器之间建立连接。

会话(Session) 是和连接(Connection)是同时建立的，两者是对同一件事情不同层次的描述。简单讲，连接(Connection)是物理上的客户端同服务器的通信链路，会话(Session)是逻辑上的用户同服务器的通信交互。

二、常用SQL

2.1 操作系统命令查询会话数

lsof -i:5236|grep dmserver|wc -l
或者
netstat -nat|awk '{print $4}'|grep 5236|wc -l

2.2 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.3 查看数据库设置的最大会话数

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.4 总览活动会话数、会话总数、最大会话数

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';

2.5 查看会话主要信息

SELECT SQL_TEXT, 
       STATE, 
       USER_NAME, 
       CURR_SCH, 
       CLNT_TYPE, 
       CLNT_HOST, 
       APPNAME, 
       CLNT_IP, 
       CLNT_VER 
FROM V$SESSIONS;

2.6 查看SQL的来源

SELECT
    REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+'),
    COUNT(*),
    STATE,
    CLNT_VER
FROM
    V$SESSIONS
GROUP BY
    REGEXP_SUBSTR(CLNT_IP, '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+'),
    STATE,
    CLNT_VER
ORDER BY
    2 DESC;

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 查看阻塞会话

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);

2.9 清除阻塞会话的源头

--使用结果集的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);

2.10 清除所有会话

BEGIN
    FOR REC IN (SELECT * FROM V$SESSIONS WHERE SESS_ID <> SESSID()) LOOP
        SP_CLOSE_SESSION(REC.SESS_ID);
    END LOOP;
END;

2.11 清除某张表的缓存计划

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);

2.12 批量清除缓存

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;

2.13 查询当前锁的状态

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;

2.14 查询历史执行语句记录-有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;

2.15 历史最大连接数

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;

2.16 当前慢SQL

--1--
SELECT
    'SP_CANCEL_SESSION_OPERATION (''' || SESS_ID || ''');' AS                 "取消会话",
    'SP_CLOSE_SESSION(''' || SESS_ID || ''');' AS                             "清除会话",
    DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS                                  SQL_USED_TIME,
    DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)) AS                           SQL_TXT,
    CURR_SCH,
    USER_NAME,
    SESS_ID,
    REPLACE(SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, ':', -1) -1), '::FFFF:', '') AS CLNT_IP,
    (SELECT
         INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT IDU
     FROM
         V$TRX
     WHERE
         ID = A.TRX_ID
         AND TRX_ID > 0) AS                                                   "INS | DEL | UPD - COUNT",
    PARSE_TIME,
    HARD_PARSE_TIME,
    LOGIC_READ_CNT,
    PHY_READ_CNT,
    IO_WAIT_TIME,
    MAX_MEM_USED
FROM
    V$SESSIONS A
    LEFT JOIN V$SQL_STAT B ON A.SESS_ID = B.SESSID
WHERE
    1 = 1
    AND A.STATE = 'ACTIVE'
ORDER BY
    3 DESC;
--2--
SELECT
    'SP_CANCEL_SESSION_OPERATION (''' || SESS_ID || ''');' AS                                      "取消会话",
    'SP_CLOSE_SESSION(''' || SESS_ID || ''');' AS                                                  "清除会话",
    SQL_USED_TIME,
    SQL_TXT,
    CURR_SCH,
    USER_NAME,
    SESS_ID,
    CLNT_IP,
    "INS | DEL | UPD - COUNT",
    PARSE_TIME,
    HARD_PARSE_TIME,
    LOGIC_READ_CNT,
    PHY_READ_CNT,
    ROUND(PHY_READ_CNT * 1.0 * PAGE / 1024 / 1024 / DECODE(SQL_USED_TIME, 0, 1, SQL_USED_TIME)) AS PHY_READ_MB,
    IO_WAIT_TIME,
    MAX_MEM_USED
FROM
    (SELECT
         DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS                                  SQL_USED_TIME,
         DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)) AS                           SQL_TXT,
         CURR_SCH,
         USER_NAME,
         SESS_ID,
         REPLACE(SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, ':', -1) -1), '::FFFF:', '') AS CLNT_IP,
         (SELECT
              INS_CNT || ' ' || DEL_CNT || ' ' || UPD_CNT IDU
          FROM
              V$TRX
          WHERE
              ID = A.TRX_ID
              AND TRX_ID > 0) AS                                                   "INS | DEL | UPD - COUNT",
         PARSE_TIME,
         HARD_PARSE_TIME,
         LOGIC_READ_CNT,
         PHY_READ_CNT,
         IO_WAIT_TIME,
         MAX_MEM_USED
     FROM
         V$SESSIONS A
         LEFT JOIN V$SQL_STAT B ON A.SESS_ID = B.SESSID
     WHERE
         1 = 1
         AND A.STATE = 'ACTIVE')
ORDER BY
    3 DESC;

2.17 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.18 清除运行过久的SQL

BEGIN
    FOR REC IN (SELECT
                    SESS_ID
                FROM
                    (SELECT DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) SS, SF_GET_SESSION_SQL(SESS_ID), * FROM V$SESSIONS WHERE STATE = 'ACTIVE')
                WHERE
                    SS > 30) LOOP
        SP_CLOSE_SESSION(REC.SESS_ID);
    END LOOP;
END;

2.19 空闲会话清理

SELECT 
    SESS_ID,
    USER_NAME,
    CLNT_IP,
    STATE,
    TRX_ID,
    DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS IDLE_SECONDS,
    DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) / 86400 AS IDLE_DAYS,
    CREATE_TIME,
    LAST_RECV_TIME,
    SQL_TEXT,
    CASE 
        WHEN TRX_ID = 0 THEN '低风险'
        ELSE '中风险（有事务但无DML）'
    END AS RISK_LEVEL,
    'SP_CLOSE_SESSION(' || SESS_ID || ');' AS KILL_SESS_SQL
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.20 自定义查询会话的存储过程

--分别通过SESS_ID、THRD_ID、TRX_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 'SP_CANCEL_SESSION_OPERATION (' || SESS_ID || ')' AS CANCEL_CMD,
               'SP_CLOSE_SESSION(' || SESS_ID || ')'            AS CLOSE_CMD,
               THRD_ID,
               SESS_ID,
               TRX_ID,
               DATEDIFF(SS, LAST_RECV_TIME, SYSDATE)           AS "EXECTIME(S)",
               STATE,
               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,
               CLNT_IP,
               CLNT_TYPE,
               OSNAME,
               LEFT(LAST_SEND_TIME, 19)
        FROM V$SESSIONS
        WHERE SESS_ID = P_ID
          AND SESS_ID <> SESSID;   -- 排除当前会话

    ELSIF P_TYPE = 'THRD' THEN
        -- 按 THRD_ID 查询
        SELECT 'SP_CANCEL_SESSION_OPERATION (' || SESS_ID || ')' AS CANCEL_CMD,
               'SP_CLOSE_SESSION(' || SESS_ID || ')'            AS CLOSE_CMD,
               THRD_ID,
               SESS_ID,
               TRX_ID,
               DATEDIFF(SS, LAST_RECV_TIME, SYSDATE)           AS "EXECTIME(S)",
               STATE,
               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,
               CLNT_IP,
               CLNT_TYPE,
               OSNAME,
               LEFT(LAST_SEND_TIME, 19)
        FROM V$SESSIONS
        WHERE THRD_ID = P_ID
          AND SESS_ID <> SESSID;

    ELSIF P_TYPE = 'TRX' THEN
        -- 按 TRX_ID 查询
        SELECT 'SP_CANCEL_SESSION_OPERATION (' || SESS_ID || ')' AS CANCEL_CMD,
               'SP_CLOSE_SESSION(' || SESS_ID || ')'            AS CLOSE_CMD,
               THRD_ID,
               SESS_ID,
               TRX_ID,
               DATEDIFF(SS, LAST_RECV_TIME, SYSDATE)           AS "EXECTIME(S)",
               STATE,
               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,
               CLNT_IP,
               CLNT_TYPE,
               OSNAME,
               LEFT(LAST_SEND_TIME, 19)
        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', 12345);
CALL SYSDBA.GSBD('THRD', 67890);
CALL SYSDBA.GSBD('TRX', 987654321);

2.21 查找全表扫描的高频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有问题用第三个

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.2 被阻塞-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.3 一条SQL查阻塞

因为结果集按WAIT_TIME降序排序，所以最上面的事务大概率就是阻塞源

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 A.*,
         B.WAIT_FOR_SESSID,
         B.WAIT_FOR_SQL,
         'SP_CANCEL_SESSION_OPERATION(' || WAIT_FOR_SESSID || ');' AS CANCEL_SESS_SQL,
         'SP_CLOSE_SESSION(' || WAIT_FOR_SESSID || ');'            AS KILL_SESS_SQL
    FROM A, B
   WHERE A.WAITOR = B.WAITOR
ORDER BY WAIT_TIME 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,
       WAIT_FOR_SESSID,
       WAIT_FOR_ID,
       WAIT_FOR_SQL,
       BLOCKED_COUNT,
       'SP_CANCEL_SESSION_OPERATION(' || WAIT_FOR_SESSID || ');' AS CANCEL_SESS_SQL,
       'SP_CLOSE_SESSION(' || WAIT_FOR_SESSID || ');'            AS KILL_SESS_SQL
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 WAIT_TIME DESC;

3.4 阻塞源头

SELECT
    'SP_CANCEL_SESSION_OPERATION(' || S.SESS_ID || ');' AS 取消会话,
    'SP_CLOSE_SESSION(' || S.SESS_ID || ');' AS            清除会话,
    S.SESS_ID AS                                           会话ID,
    S.TRX_ID AS                                            事务ID,
    S.USER_NAME AS                                         用户名,
    S.SQL_TEXT AS                                          当前SQL,
    S.STATE AS                                             会话状态,
    S.CLNT_IP AS                                           客户端IP,
    S.APPNAME AS                                           应用名,
    S.CREATE_TIME AS                                       会话创建时间
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));

另附

相关的系统动态视图

• 显示会话的具体信息：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