一、简单概述

通俗来讲，会话(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 查看SQL的来源

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

2.6 如果想要调整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.7 查看阻塞会话

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.8 清除阻塞会话的源头

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

2.9 清除所有会话

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

2.10 清除某张表的缓存计划

SELECT 'SP_CLEAR_PLAN_CACHE('||CACHE_ITEM||');' FROM V$CACHEPLN WHERE SQLSTR LIKE '%TABLENAME%';

2.11 批量清除缓存

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.12 查询当前锁的状态

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.13 查询历史执行语句记录-有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 >= 1         --执行耗时 1S 以上的SQL
ORDER BY
    SQL_HIS.TIME_USED DESC;

2.14 历史最大连接数

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;

三、补充-阻塞会话相关

（1）阻塞源

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;

（2）被阻塞

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）查询等待的会话和SQL

WITH A AS
    (SELECT
         SESS_ID AS WAITOR_SESSID,
         SQL_TEXT AS WAITOR_SQL,
         ID AS WAITOR,
         WAIT_FOR_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
FROM
    A,B
WHERE
    A.WAITOR = B.WAITOR;

另附

相关的系统动态视图

• 显示会话的具体信息：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】
• 显示系统自启动以来执行时间最长的20条SQL语句：V$SYSTEM_LONG_EXEC_SQLS【需要ENABLE_MONITOR=1、MONITOR_TIME=1】