v$session介绍
主要使用v$session查询,在此之前对此时图进行简单介绍:
==============================================================
v$session
==============================================================
SADDR: session address
SID: session identifier,常用于连接其它列。
SERIAL#: sid 会重用,但是同一个sid被重用时,serial#会增加,不会重复。
AUDSID: audit session id。可以通过audsid查询当前session的sid。select sid from v$session where audsid=userenv('sessionid');
PADDR: process address,关联v$process的addr字段,可以通过这个字段查处当前session对应操作系统的那个进程的id。
USER#: session's user id。等于dba_users中的user_id。Oracle内部进程的user#为0。
USERNAME: session's username。等于dba_users中的username。Oracle内部进程的username为空。
COMMAND: session正在执行的SQL Id。1代表create table,3代表select。
TADDR: 当前的transaction address。可以用来关联v$transaction的addr字段。
LOCKWAIT: 可以通过这个字段查询出当前正在等待的锁的相关信息。sid & lockwait与v$lock中的sid & kaddr相对应。
STATUS: 用来判断session状态。Active:正执行SQL语句。Inactive:等待操作。Killed:被标注为删除。
SERVER: server type (dedicated or shared)
SCHEMA#: schema user id。Oracle内部进程的schema#为0。
SCHEMANAME: schema username。Oracle内部进程的schemaname为sys。
OSUSER: 客户端操作系统用户名。
PROCESS: 客户端process id。
MACHINE: 客户端machine name。
TERMINAL: 客户端执行的terminal name。
PROGRAM: 客户端应用程序。比如ORACLE.EXE (PMON)或者sqlplus.exe
TYPE: session type (background or user)
SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER: session正在执行的sql statement,和v$sql中的address, hash_value, sql_id, child_number相对应。
PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: 上一次执行的sql statement。
MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: 应用通过DBMS_APPLICATION_INFO设置的一些信息。
FIXED_TABLE_SEQUENCE: 当session完成一个user call后就会增加的一个数值,也就是说,如果session inactive,它就不会增加。因此可以根据此字段的值变化来监控某个时间点以来的session的性能情况。例如,一个小时以前,某个session的FIXED_TABLE_SEQUENCE是10000,而现在是20000,则表明一个小时内其user call比较频繁,可以重点关注此session的performance statistics。
ROW_WAIT_OBJ#: 被锁定行所在table的object_id。和dba_objects中的object_id关联可以得到被锁定的table name。
ROW_WAIT_FILE#: 被锁定行所在的datafile id。和v$datafile中的file#关联可以得到datafile name。
ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#
ROW_WAIT_ROW#: session当前正在等待的被锁定的行。
LOGON_TIME: session logon time
===========================================================
v$process
============================================================
ADDR: process address。可以和v$session的paddr字段关联。
PID: Oracle进程identifier。
SPID: 操作系统进程identifier。
USERNAME: 操作系统进程的用户名。并非Oracle用户名。
SERIAL#:: process serial number。
TERMINAL: 操作系统terminal identifier(e.g., computer name)。
PROGRAM: 进程正在执行的程序(e.g., ORACLE.EXE (ARC0)),和v$session中的program类似。
BACKGROUND: 1代表oracle background process,null代表normal process。
查看当前阻塞对象:
SELECT
wsession.sid waiting_session,
wsession.serial# serial,
wsession.logon_time,
wsession.blocking_session_status,
wsession.event,
wsession.username waiting_user,
wprocess.pid wait_pid,
nvl(obj.object_name, '-') oname,
nvl(obj.owner, '-') owner,
wsession.row_wait_block# row_lock,
wsession.blocking_session holding_session,
hprocess.pid hold_pid,
hsession.username holding_user
FROM
v$session wsession
JOIN v$session hsession ON
wsession.blocking_session = hsession.sid
JOIN v$process wprocess ON
wprocess.addr = wsession.paddr
JOIN v$process hprocess ON
hprocess.addr = hsession.paddr
LEFT JOIN dba_objects obj ON
obj.object_id = wsession.row_wait_obj#
WHERE
wsession.blocking_session IS NOT NULL
UNION
SELECT
wsession.sid waiting_session,
wsession.serial# serial,
wsession.logon_time,
wsession.blocking_session_status,
wsession.event,
wsession.username waiting_user,
wprocess.pid wait_pid,
nvl(obj.object_name, '-') oname,
nvl(obj.owner, '-') owner,
wsession.row_wait_block# row_lock,
nvl(wsession.blocking_session, 0) holding_session,
nvl(hprocess.pid, 0) hold_pid,
nvl(hsession.username, '-') holding_user
FROM
v$session wsession
LEFT JOIN v$session hsession ON
wsession.blocking_session = hsession.sid
JOIN v$process wprocess ON
wprocess.addr = wsession.paddr
LEFT JOIN v$process hprocess ON
hprocess.addr = hsession.paddr
LEFT JOIN dba_objects obj ON
obj.object_id = wsession.row_wait_obj#
WHERE
wsession.sid IN (
SELECT
blocking_session
FROM
v$session);
查看历史阻塞情况:
select *
from (select t.machine,
t.session_id,
t.session_serial#,
SUBSTR(numtodsinterval(t.WAIT_TIME, 'second'), 12, 8) as wait_time,
t.instance_number,
t.sample_time,
t.blocking_session,
t.blocking_session_serial#,
t.blocking_inst_id,
t.session_state,
t.event,
t.sql_exec_start,
t.sql_plan_operation,
t.sql_id,
t.sql_opcode,
t.user_id,
t.session_type,
connect_by_isleaf as isleaf,
CONNECT_BY_ROOT session_id rootsid,
sys_connect_by_path(session_id, '->') tree
from dba_hist_active_sess_history t
where t.sample_time >
to_date('2024-03-21 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.sample_time <
to_date('2024-03-21 11:20:00', 'yyyy-mm-dd hh24:mi:ss')
and t.session_type <> 'BACKGROUND'
and t.event is not NULL
start with blocking_session is null
connect by nocycle prior t.session_id = t.blocking_session)
where isleaf = 1
-- and rootsid <> 5
;
查看当前DDL锁对象,并kill会话:
select 'ALTER SYSTEM DISCONNECT SESSION ''' || b.SID || ',' || b.SERIAL# ||
''' IMMEDIATE;' kills,
a.type,
a.owner,
c.pid,
b.SID,
b.SERIAL#,
b.OSUSER,
b.MACHINE,
b.PROGRAM,
b.MODULE,
b.ACTION,
b.*,
c.*
From dba_ddl_locks a, v$session b, v$process c
where a.session_id = b.SID
and b.paddr = c.addr
and a.name = upper('pkg_scene_option')
若需kill会话,执行kills字段中的命令即可。
查看当前被锁对象,并kill会话:
select 'ALTER SYSTEM DISCONNECT SESSION ''' || b.SID || ',' || b.SERIAL# ||
''' IMMEDIATE;' kills,
f.lock_type,
c.pid,
b.SID,
b.SERIAL#,
b.OSUSER,
b.MACHINE,
b.PROGRAM,
b.MODULE,
b.ACTION,
e.owner,
e.object_name,
a.*,
b.*,
c.*,
d.*
From v$locked_object a,
v$session b,
v$process c,
v$sql d,
dba_objects e,
dba_locks f
where a.session_id = b.SID
and b.paddr = c.addr
and d.sql_id = b.sql_id
and a.object_id = e.object_id
and a.session_id = f.session_id
and b.sql_address=d.address
oracle rac查看死锁,并kill进程
select 'kill -9 '||pc.SPID,a.*,
row_number() over(partition by blocking_source order by "LEVEL" asc) as rn
from (select *
from (select inst_id,
lpad(' ', (level - 1) * 2, ' ') || sid as sid,
serial#,
sql_id,
paddr,
username,
event,
machine,
program,
SUBSTR(numtodsinterval(seconds_in_wait, 'second'),
12,
8) as wait_time,
wait_class#,
level,
blocking_session,
(prior sid) as blocking_sid,
(prior serial#) as blocking_serial#,
(prior sql_id) as blocking_sql_id,
sys_connect_by_path(sid, '->') as blocking_path,
connect_by_root(sid) as blocking_source
from gv$session
start with blocking_session is null
connect by (prior sid) = blocking_session)
where "LEVEL" > 1
union
select inst_id,
to_char(sid) as sid,
serial#,
sql_id,
paddr,
username,
event,
machine,
program,
SUBSTR(numtodsinterval(seconds_in_wait, 'second'), 12, 8) as wait_time,
wait_class#,
1,
null,
null,
null,
null,
null,
sid
from gv$session
where sid in (select blocking_source
from (select wait_class#,
username,
level,
connect_by_root(sid) as blocking_source
from gv$session
start with blocking_session is null
connect by (prior sid) = blocking_session)
where "LEVEL" > 1)) a,"GV_$PROCESS" pc WHERE pc.ADDR=a.PADDR;
总结
--查看阻塞进程SQL语句:
--Blocking sessions
SELECT /*+ RULE */
LPAD('--->', DECODE(A.request, 0, 0, 5)) || A.SID SID,
B.serial#,
DECODE(TRUNC(SYSDATE - LOGON_TIME),
0,
NULL,
TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),
'HH24:MI:SS') LOGON,
b.status,
b.SCHEMANAME SCHEMA,
DECODE(o.owner || '.' || o.object_name,
'.',
NULL,
o.owner || '.' || o.object_name) OBJECT,
o.object_type,
b.osuser,
b.machine,
b.module,
b.program,
DECODE(BLOCK, 0, NULL, 'BLOKER') || DECODE(request, 0, NULL, '-->WAITER') BLOKER,
DECODE(A.lmode,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
A.lmode) lmode,
DECODE(TRUNC(ctime / 86400),
0,
TO_CHAR(TO_DATE(ctime, 'SSSSS'), 'HH24:MI:SS'),
TRUNC(ctime / 86400) || ' Days + ' ||
TO_CHAR(TO_DATE(ctime - (TRUNC(ctime / 86400)) * 86400, 'SSSSS'),
'HH24:MI:SS')) TIME,
A.TYPE,
'alter system kill session ' || '''' || a.SID || ', ' || b.serial# || '''' ||
' immediate;' kill_session,
DECODE(object_type,
NULL,
NULL,
'Dbms_Rowid.rowid_create(1, ' || row_wait_obj# || ', ' ||
row_wait_file# || ', ' || row_wait_block# || ', ' || row_wait_row# || ')') row_id
FROM v$lock A, v$session b, dba_objects o
WHERE A.SID = b.SID
AND (lmode = 0 OR BLOCK = 1)
AND o.object_id(+) = DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#)
ORDER BY A.id1, A.request;
--Tip: The last column of the query for blocked sessions is ROWID of the record!!!!
--To find the exact locked row execute:
--SELECT * FROM OBJECT WHERE ROWID = DbmsRowid.rowid_create(1, 48804, 31, 114809, 210);
--单实例阻塞会话查询
--cascade blocking
/*set lines 200 pages 100
col tree for a30
col event for a40*/
select *
from (select 'alter system kill session ' || '''' || a.SID || ', ' ||
a.serial# || '''' || ' immediate;' kill_session,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(SID, '<-') tree,
level as tree_level
from v$session a
start with a.blocking_session is not null
connect by nocycle a.sid = prior a.blocking_session)
where isleaf = 1
order by tree_level asc;
--RAC实例阻塞会话查询
--cascade blocking@gv$session
select *
from (select a.inst_id,
'alter system kill session ' || '''' || a.SID || ', ' ||
a.serial# || '''' || ' immediate;' kill_session,
a.sql_id,
a.event,
a.status,
connect_by_isleaf as isleaf,
sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
level as tree_level
from gv$session a
start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior
(a.blocking_session || '@' || a.blocking_instance))
where isleaf = 1
order by tree_level asc;
--alter system kill session '150,8742' immediate;
select p.spid from gv$session s,gv$process p where s.sid=xxx and s.paddr=p.addr;
--检测数据库各实例session 阻塞 tree的sql
检测数据库各实例session 阻塞 tree的sql
--由于使用到了 v$sesion 的 blocking_instance,blocking_session 这两列,所以数据库的最低版本为oracle 10g。
---适合单实例和多实实例数据库,一个SQL搞定。
with/*+ materialized +*/ aa as (
select *
from gv$session s
where 1=1
)
select
'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''' immediate;' as kill_SID,
lpad('+', 2 * level - 1) ||t.inst_sid as leveL_inst_sid,
level level_flag,
sys_connect_by_path(t.inst_sid,'/') as inst_sid_path,
connect_by_root t.inst_sid as root_inst_sid,
connect_by_isleaf as node_isleaf,
'##########' as FLAGFLAG0,
t.status,
t.event#,
t.event,
t.program,
t.module,
t.action,
t.client_identifier,
t.OSUSER os_USER,
t.machine os_machine,
t.logon_time logon_time,
t.last_call_et last_call_et,
'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''' immediate;' as kill_SID,
'##########' as FLAGFLAG/*,
t.* */
from (
select gs.inst_id||'-'||gs.sid as inst_sid,
gs.blocking_instance||'-'||gs.blocking_session par_inst_sid,
gs.*
from aa gs
where 1=1
and (gs.inst_id,gs.sid ) in (
--被阻塞者
select s.inst_id,s.sid
from aa s
where 1=1
and s.BLOCKING_SESSION is not null
union all
--阻塞者
select sw.inst_id,sw.sid
from aa sw
where 1=1
--and lower(sw.MACHINE) not like '%ejb%'
and (sw.INST_ID,sw.SID) in (
select/*+ hash_sj*/
s.BLOCKING_INSTANCE,
s.BLOCKING_SESSION
from aa s
where 1=1
and s.BLOCKING_SESSION is not null
)
)
) t
where 1=1
connect by prior t.inst_sid=t.par_inst_sid
order siblings by t.inst_sid
;
--查看 dba_hist_active_sess_history
with/*+ materialized +*/ aa as (
select instance_number as inst_id,
session_id as sid,
session_serial# as SERIAL#,
blocking_inst_id as blocking_instance,
blocking_session,
blocking_session_serial#,
event,
p1,
p2,
p3,
sql_id
from dba_hist_active_sess_history h
where 1=1
and to_char(sample_time, 'yyyymmddhh24miss') between '20190808143109'
and '20190808143109'
order by sample_time
)
select
lpad('+', 2 * level - 1) ||t.inst_sid as leveL_inst_sid,
level level_flag,
sys_connect_by_path(t.inst_sid,'/') as inst_sid_path,
connect_by_root t.inst_sid as root_inst_sid,
connect_by_isleaf as node_isleaf,
'##########' as FLAGFLAG0,
t.event,
t.p1,
t.p2,
t.p3,
t.sql_id,
'##########' as FLAGFLAG/*,
t.* */
from (
select gs.inst_id||'-'||gs.sid as inst_sid,
gs.blocking_instance||'-'||gs.blocking_session par_inst_sid,
gs.*
from aa gs
where 1=1
and (gs.inst_id,gs.sid ) in (
--被阻塞者
select s.inst_id,s.sid
from aa s
where 1=1
and s.BLOCKING_SESSION is not null
union all
--阻塞者
select sw.inst_id,sw.sid
from aa sw
where 1=1
--and lower(sw.MACHINE) not like '%ejb%'
and (sw.INST_ID,sw.SID) in (
select/*+ hash_sj*/
s.BLOCKING_INSTANCE,
s.BLOCKING_SESSION
from aa s
where 1=1
and s.BLOCKING_SESSION is not null
)
)
) t
where 1=1
connect by prior t.inst_sid=t.par_inst_sid
order siblings by t.inst_sid
;
--
--1.no-rac
--
select a.*,
row_number() over(partition by blocking_source order by "LEVEL" asc) as rn
from (select *
from (select lpad(' ', (level - 1) * 2, ' ') || sid as sid,
serial#,
sql_id,
paddr,
username,
event,
machine,
program,
SUBSTR(numtodsinterval(seconds_in_wait, 'second'),
12,
8) as wait_time,
wait_class#,
level,
blocking_session,
(prior sid) as blocking_sid,
(prior serial#) as blocking_serial#,
(prior sql_id) as blocking_sql_id,
sys_connect_by_path(sid, '->') as blocking_path,
connect_by_root(sid) as blocking_source
from v$session
start with blocking_session is null
connect by (prior sid) = blocking_session)
where "LEVEL" > 1
union
select to_char(sid) as sid,
serial#,
sql_id,
paddr,
username,
event,
machine,
program,
SUBSTR(numtodsinterval(seconds_in_wait, 'second'), 12, 8) as wait_time,
wait_class#,
1,
null,
null,
null,
null,
null,
sid
from v$session
where sid in (select blocking_source
from (select wait_class#,
username,
level,
connect_by_root(sid) as blocking_source
from v$session
start with blocking_session is null
connect by (prior sid) = blocking_session)
where "LEVEL" > 1)) a
--
--2.rac
--
select a.*,
row_number() over(partition by blocking_source order by "LEVEL" asc) as rn
from (select *
from (select inst_id,
lpad(' ', (level - 1) * 2, ' ') || sid as sid,
serial#,
sql_id,
paddr,
username,
event,
machine,
program,
SUBSTR(numtodsinterval(seconds_in_wait, 'second'),
12,
8) as wait_time,
wait_class#,
level,
blocking_session,
(prior sid) as blocking_sid,
(prior serial#) as blocking_serial#,
(prior sql_id) as blocking_sql_id,
sys_connect_by_path(sid, '->') as blocking_path,
connect_by_root(sid) as blocking_source
from gv$session
start with blocking_session is null
connect by (prior sid) = blocking_session)
where "LEVEL" > 1
union
select inst_id,
to_char(sid) as sid,
serial#,
sql_id,
paddr,
username,
event,
machine,
program,
SUBSTR(numtodsinterval(seconds_in_wait, 'second'), 12, 8) as wait_time,
wait_class#,
1,
null,
null,
null,
null,
null,
sid
from gv$session
where sid in (select blocking_source
from (select wait_class#,
username,
level,
connect_by_root(sid) as blocking_source
from gv$session
start with blocking_session is null
connect by (prior sid) = blocking_session)
where "LEVEL" > 1)) a
历史会话情况
select t.machine,
t.session_id,
t.session_serial#,
t.instance_number,
t.sample_time,
t.blocking_session,
t.blocking_session_serial#,
t.blocking_inst_id,
t.session_state,
t.event,
t.sql_exec_start,
t.sql_plan_operation,
t.sql_id,
t.sql_opcode,
t.user_id,
t.session_type
from dba_hist_active_sess_history t
where t.sample_time >
to_date('2023-02-20 08:30:00', 'yyyy-mm-dd hh24:mi:ss')
and t.sample_time <
to_date('2023-02-20 10:30:00', 'yyyy-mm-dd hh24:mi:ss')
and t.session_type <> 'BACKGROUND'
and t.event is not NULL
and t.blocking_session is not null
and t.sql_opcode <> '0'
and t.user_id <> '0'
order by t.sample_time desc;