51工具盒子一、简单概述
通俗来讲，会话(Session) 是通信双方从开始通信到通信结束期间的一个上下文（Context）。这个上下文是一段位于服务器端的内存：记录了本次连接的客户端机器、通过哪个应用程序、哪个用户登录等信息。
连接（Connection）：连接是从客户端到ORACLE实例的一条物理路径。连接可以在网络上建立，或者在本机通过IPC机制建立。通常会在客户端进程与一个专用服务器或一个调度器之间建立连接。
会话(Session) 是和连接(Connection)是同时建立的，两者是对同一件事情不同层次的描述。简单讲，连接(Connection)是物理上的客户端同服务器的通信链路，会话(Session)是逻辑上的用户同服务器的通信交互。
二、常用SQL
（1）操作系统命令查询会话数
lsof -i:5236|grep dmserver|wc -l
或者
netstat -nat|awk '{print $4}'|grep 5236|wc -l
（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;
（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';
（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';
（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;
（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占总的百分比，评估可以调整的幅度。
（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);
（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);
（9）清除所有会话
BEGIN
FOR REC IN
(
SELECT * FROM V$SESSIONS WHERE SESS_ID <> SESSID()
)
LOOP
SP_CLOSE_SESSION(REC.SESS_ID);
END LOOP;
END;
（10）清除某张表的缓存计划
SELECT 'SP_CLEAR_PLAN_CACHE('||CACHE_ITEM||');' FROM V$CACHEPLN WHERE SQLSTR LIKE '%TABLENAME%';
（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;
（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;
三、补充-阻塞会话相关
（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】
