Oracle 查看session级别信息
1. 查看活动会话信息
[sql] view plain copy
SELECT *
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS = 'ACTIVE'
ORDER BY LOGON_TIME, SID;
注：status用来判断会话状态
Active：正执行sql语句(waiting for/using a resource)
Inactive：等待操作(即等待需要执行的sql语句)
killed：被标记为删除
2. 查看当前会话执行的sql语句及进程信息
[sql] view plain copy
SELECT P.SPID,
S.SID,
S.OSUSER,
S.MACHINE,
S.PROGRAM,
S.LOGON_TIME,
S.SERIAL#,
Q.MODULE,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT
FROM V$SESSION S, V$PROCESS P, V$SQLAREA Q
WHERE S.PADDR = P.ADDR(+)
AND S.SQL_ID = Q.SQL_ID(+)
AND S.AUDSID = USERENV('SESSIONID');
else:获取当前会话的SID
[sql] view plain copy
SELECT * FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
SELECT * FROM V$MYSTAT A WHERE ROWNUM=1;
SELECT USERENV('SID') FROM DUAL;
3. 查询会话等待与其对应的sql
[sql] view plain copy
SELECT P.PID,
S.SID,
S.SERIAL#,
S.USERNAME,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT,
W.EVENT,
W.WAIT_TIME,
W.STATE,
CASE
WHEN W.STATE = 'WAITING' THEN
W.SECONDS_IN_WAIT
WHEN W.STATE = 'WAITING KNOWN TIME' THEN
W.WAIT_TIME
END AS SEC_IN_WAIT
FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = W.SID
AND S.SQL_ID = Q.SQL_ID
AND P.ADDR = S.PADDR
AND W.EVENT NOT LIKE 'SQL*Net%'
AND S.USERNAME IS NOT NULL
AND W.WAIT_TIME >= 0
ORDER BY W.SECONDS_IN_WAIT DESC;
注： 如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
4. 查询会话等待事件及对应的sql
[sql] view plain copy
SELECT P.PID,
S.SID,
S.SERIAL#,
S.USERNAME,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT,
E.EVENT,
E.TOTAL_WAITS,
E.TIME_WAITED,
E.AVERAGE_WAIT
FROM V$SESSION S, V$SESSION_EVENT E, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = E.SID
AND S.SQL_ID = Q.SQL_ID
AND P.ADDR = S.PADDR
AND E.EVENT NOT LIKE 'SQL*Net%'
AND S.STATUS = 'ACTIVE'
AND S.USERNAME IS NOT NULL;
5. 查询长时间运行的会话与对应的sql
[sql] view plain copy
SELECT S.SID,
OPNAME,
TRUNC(L.SOFAR / L.TOTALWORK * 100, 2) || '%' AS PCT_WORK,
L.ELAPSED_SECONDS ELAPSED,
ROUND(L.ELAPSED_SECONDS * (L.TOTALWORK - L.SOFAR) / L.SOFAR) REMAIN_TIME,
Q.SQL_TEXT
FROM V$SESSION_LONGOPS L, V$SQLAREA Q, V$SESSION S
WHERE L.SQL_HASH_VALUE = Q.HASH_VALUE
AND L.SID = S.SID
--AND L.SOFAR != L.TOTALWORK
AND L.ELAPSED_SECONDS>6
ORDER BY L.START_TIME
6. 查询会话及锁与对应的sql
[sql] view plain copy
SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
B.ORACLE_USERNAME 锁表用户名,
DECODE(D.TYPE,
'XR',
'NULL',
'RS',
'SS(Row-S)',
'CF',
'SS(Row-S)',
'TM',
'TABLE LOCK',
'PW',
'TABLE LOCK',
'TO',
'TABLE LOCK',
'TS',
'TABLE LOCK',
'RT',
'ROW LOCK',
'TX',
'ROW LOCK',
'MR',
'S(Share)',
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程ID,
DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
C.SERVER,
C.SID,
E.SQL_TEXT,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C,
V$LOCK D,
V$SQLTEXT E
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
AND C.SID = D.SID
AND B.LOCKED_MODE = D.LMODE
AND C.SQL_ID = E.SQL_ID(+)
ORDER BY 1, 2;
7. 查询被阻塞会话与被阻塞会话的对应sql
[sql] view plain copy
SELECT S1.USERNAME "WAITING USER",
S1.OSUSER "OS User"
,
S1.LOGON_TIME "logon time",
W.SESSION_ID "Sid",
P1.SPID "PID",
Q1.SQL_TEXT "SQLTEXT",
S2.USERNAME "HOLDING User",
S2.OSUSER "OS User"
,
S2.LOGON_TIME "logon time",
H.SESSION_ID "Sid",
P2.SPID "PID",
Q2.SQL_TEXT "SQLTEXT"
FROM SYS.V_$PROCESS P1,
SYS.V_$PROCESS P2,
SYS.V_$SESSION S1,
SYS.V_$SESSION S2,
DBA_LOCKS W,
DBA_LOCKS H,
V$SQL Q1,
V$SQL Q2
WHERE H.MODE_HELD != 'None'
AND H.MODE_HELD != 'Null'
AND W.MODE_REQUESTED != 'None'
AND W.LOCK_TYPE(+) = H.LOCK_TYPE
AND W.LOCK_ID1(+) = H.LOCK_ID1
AND W.LOCK_ID2(+) = H.LOCK_ID2
AND W.SESSION_ID = S1.SID(+)
AND H.SESSION_ID = S2.SID(+)
AND S1.PADDR = P1.ADDR(+)
AND S2.PADDR = P2.ADDR(+)
AND S1.SQL_ID = Q1.SQL_ID(+)
AND S2.SQL_ID = Q2.SQL_ID(+)
ORDER BY H.SESSION_ID;
1.通过如下sql你可以查询你的每个应用程序到底在等待什么，从而针对这些信息对数据库的性能进行调整。
Select s.username,s.program,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait
from v$session s, v$session_event se
Where s.sid=se.sid And se.event not like 'SQl*Net%' And s.status ='ACTIVE' And s.username is not null
2.oracle中查询被锁的表并释放session
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME, B.OS_USER_NAME,B.PROCESS, B.LOCKED_MODE, C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM
FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) ORDER BY 1,2
释放session Sql:
alter system kill session 'sid, serial#'
alter system kill session '379, 21132'
alter system kill session '374, 6938'
3.查看占用系统io较大的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC
4.找出耗cpu较多的session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc
5.查询session被锁的sql可以用一下语句
select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,
decode(v$lock.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,
decode(lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode(request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id;
[sql] view plain copy
SELECT *
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS = 'ACTIVE'
ORDER BY LOGON_TIME, SID;
注：status用来判断会话状态
Active：正执行sql语句(waiting for/using a resource)
Inactive：等待操作(即等待需要执行的sql语句)
killed：被标记为删除
2. 查看当前会话执行的sql语句及进程信息
[sql] view plain copy
SELECT P.SPID,
S.SID,
S.OSUSER,
S.MACHINE,
S.PROGRAM,
S.LOGON_TIME,
S.SERIAL#,
Q.MODULE,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT
FROM V$SESSION S, V$PROCESS P, V$SQLAREA Q
WHERE S.PADDR = P.ADDR(+)
AND S.SQL_ID = Q.SQL_ID(+)
AND S.AUDSID = USERENV('SESSIONID');
else:获取当前会话的SID
[sql] view plain copy
SELECT * FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
SELECT * FROM V$MYSTAT A WHERE ROWNUM=1;
SELECT USERENV('SID') FROM DUAL;
3. 查询会话等待与其对应的sql
[sql] view plain copy
SELECT P.PID,
S.SID,
S.SERIAL#,
S.USERNAME,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT,
W.EVENT,
W.WAIT_TIME,
W.STATE,
CASE
WHEN W.STATE = 'WAITING' THEN
W.SECONDS_IN_WAIT
WHEN W.STATE = 'WAITING KNOWN TIME' THEN
W.WAIT_TIME
END AS SEC_IN_WAIT
FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = W.SID
AND S.SQL_ID = Q.SQL_ID
AND P.ADDR = S.PADDR
AND W.EVENT NOT LIKE 'SQL*Net%'
AND S.USERNAME IS NOT NULL
AND W.WAIT_TIME >= 0
ORDER BY W.SECONDS_IN_WAIT DESC;
注： 如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。
4. 查询会话等待事件及对应的sql
[sql] view plain copy
SELECT P.PID,
S.SID,
S.SERIAL#,
S.USERNAME,
Q.SQL_ID,
Q.SQL_TEXT,
Q.SQL_FULLTEXT,
E.EVENT,
E.TOTAL_WAITS,
E.TIME_WAITED,
E.AVERAGE_WAIT
FROM V$SESSION S, V$SESSION_EVENT E, V$SQLAREA Q, V$PROCESS P
WHERE S.SID = E.SID
AND S.SQL_ID = Q.SQL_ID
AND P.ADDR = S.PADDR
AND E.EVENT NOT LIKE 'SQL*Net%'
AND S.STATUS = 'ACTIVE'
AND S.USERNAME IS NOT NULL;
5. 查询长时间运行的会话与对应的sql
[sql] view plain copy
SELECT S.SID,
OPNAME,
TRUNC(L.SOFAR / L.TOTALWORK * 100, 2) || '%' AS PCT_WORK,
L.ELAPSED_SECONDS ELAPSED,
ROUND(L.ELAPSED_SECONDS * (L.TOTALWORK - L.SOFAR) / L.SOFAR) REMAIN_TIME,
Q.SQL_TEXT
FROM V$SESSION_LONGOPS L, V$SQLAREA Q, V$SESSION S
WHERE L.SQL_HASH_VALUE = Q.HASH_VALUE
AND L.SID = S.SID
--AND L.SOFAR != L.TOTALWORK
AND L.ELAPSED_SECONDS>6
ORDER BY L.START_TIME
6. 查询会话及锁与对应的sql
[sql] view plain copy
SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
B.ORACLE_USERNAME 锁表用户名,
DECODE(D.TYPE,
'XR',
'NULL',
'RS',
'SS(Row-S)',
'CF',
'SS(Row-S)',
'TM',
'TABLE LOCK',
'PW',
'TABLE LOCK',
'TO',
'TABLE LOCK',
'TS',
'TABLE LOCK',
'RT',
'ROW LOCK',
'TX',
'ROW LOCK',
'MR',
'S(Share)',
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程ID,
DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
C.SERVER,
C.SID,
E.SQL_TEXT,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME
FROM ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C,
V$LOCK D,
V$SQLTEXT E
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
AND C.SID = D.SID
AND B.LOCKED_MODE = D.LMODE
AND C.SQL_ID = E.SQL_ID(+)
ORDER BY 1, 2;
7. 查询被阻塞会话与被阻塞会话的对应sql
[sql] view plain copy
SELECT S1.USERNAME "WAITING USER",
S1.OSUSER "OS User"
,
S1.LOGON_TIME "logon time",
W.SESSION_ID "Sid",
P1.SPID "PID",
Q1.SQL_TEXT "SQLTEXT",
S2.USERNAME "HOLDING User",
S2.OSUSER "OS User"
,
S2.LOGON_TIME "logon time",
H.SESSION_ID "Sid",
P2.SPID "PID",
Q2.SQL_TEXT "SQLTEXT"
FROM SYS.V_$PROCESS P1,
SYS.V_$PROCESS P2,
SYS.V_$SESSION S1,
SYS.V_$SESSION S2,
DBA_LOCKS W,
DBA_LOCKS H,
V$SQL Q1,
V$SQL Q2
WHERE H.MODE_HELD != 'None'
AND H.MODE_HELD != 'Null'
AND W.MODE_REQUESTED != 'None'
AND W.LOCK_TYPE(+) = H.LOCK_TYPE
AND W.LOCK_ID1(+) = H.LOCK_ID1
AND W.LOCK_ID2(+) = H.LOCK_ID2
AND W.SESSION_ID = S1.SID(+)
AND H.SESSION_ID = S2.SID(+)
AND S1.PADDR = P1.ADDR(+)
AND S2.PADDR = P2.ADDR(+)
AND S1.SQL_ID = Q1.SQL_ID(+)
AND S2.SQL_ID = Q2.SQL_ID(+)
ORDER BY H.SESSION_ID;
1.通过如下sql你可以查询你的每个应用程序到底在等待什么，从而针对这些信息对数据库的性能进行调整。
Select s.username,s.program,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait
from v$session s, v$session_event se
Where s.sid=se.sid And se.event not like 'SQl*Net%' And s.status ='ACTIVE' And s.username is not null
2.oracle中查询被锁的表并释放session
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,B.ORACLE_USERNAME, B.OS_USER_NAME,B.PROCESS, B.LOCKED_MODE, C.MACHINE,C.STATUS,C.SERVER,C.SID,C.SERIAL#,C.PROGRAM
FROM ALL_OBJECTS A,V$LOCKED_OBJECT B,SYS.GV_$SESSION C
WHERE ( A.OBJECT_ID = B.OBJECT_ID ) AND (B.PROCESS = C.PROCESS ) ORDER BY 1,2
释放session Sql:
alter system kill session 'sid, serial#'
alter system kill session '379, 21132'
alter system kill session '374, 6938'
3.查看占用系统io较大的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC
4.找出耗cpu较多的session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc
5.查询session被锁的sql可以用一下语句
select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid,
sys.v_$session.serial#,
decode(v$lock.type,
'MR', 'Media Recovery',
'RT','Redo Thread',
'UN','User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Unknown') LockType,
rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,
decode(lmode, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') LockMode,
decode(request, 0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive', 'Unknown') RequestMode,
ctime, block b
from v$lock, all_objects, sys.v_$session
where v$Lock.sid > 6
and sys.v_$session.sid = v$lock.sid
and v$lock.id1 = all_objects.object_id;
