逛论坛看到一篇关于blocking session的帖子 归纳一下几种查询
①
with vw_lock AS (SELECT * FROM v$lock)
select
a.sid,
'is blocking',
(select 'sid:'||s.sid||' object:'||do.object_name||' rowid:'||
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
||' sql_id:'||s.sql_id
from v$session s, dba_objects do
where s.sid=b.sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
) blockee,
b.sid,b.id1,b.id2
from vw_lock a, vw_lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
②SELECT /*+ ORDERED USE_HASH(H,R) */
H.SID HOLD_SID,
R.SID WAIT_SID,
decode(H.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 Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'TC', 'Thread Checkpoint',
'SS', 'Sort Segment',
'JQ', 'Job Queue',
'PI', 'Parallel operation',
'PS', 'Parallel operation',
'DL', 'Direct Index Creation',
H.type) type,
decode(H.lmode,
0, 'None', 1, 'Null',
2, 'Row-S (SS)', 3, 'Row-X (SX)',
4, 'Share', 5, 'S/Row-X (SSX)',
6, 'Exclusive', to_char(H.lmode)) hold,
decode(r.request, 0, 'None',
1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share',
5, 'S/Row-X (SSX)',6, 'Exclusive',
to_char(R.request)) request,
R.ID1,R.ID2,R.CTIME
FROM V$LOCK H,V$LOCK R
WHERE H.BLOCK = 1 AND R.REQUEST > 0 AND H.SID <> R.SID
and H.TYPE <> 'MR' AND R.TYPE <> 'MR'
AND H.ID1 = R.ID1 AND H.ID2 = R.ID2 AND H.TYPE=R.TYPE
AND H.LMODE > 0 AND R.REQUEST > 0 ORDER BY 1,2;
③
④
①
with vw_lock AS (SELECT * FROM v$lock)
select
a.sid,
'is blocking',
(select 'sid:'||s.sid||' object:'||do.object_name||' rowid:'||
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
||' sql_id:'||s.sql_id
from v$session s, dba_objects do
where s.sid=b.sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
) blockee,
b.sid,b.id1,b.id2
from vw_lock a, vw_lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
②SELECT /*+ ORDERED USE_HASH(H,R) */
H.SID HOLD_SID,
R.SID WAIT_SID,
decode(H.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 Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'TC', 'Thread Checkpoint',
'SS', 'Sort Segment',
'JQ', 'Job Queue',
'PI', 'Parallel operation',
'PS', 'Parallel operation',
'DL', 'Direct Index Creation',
H.type) type,
decode(H.lmode,
0, 'None', 1, 'Null',
2, 'Row-S (SS)', 3, 'Row-X (SX)',
4, 'Share', 5, 'S/Row-X (SSX)',
6, 'Exclusive', to_char(H.lmode)) hold,
decode(r.request, 0, 'None',
1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share',
5, 'S/Row-X (SSX)',6, 'Exclusive',
to_char(R.request)) request,
R.ID1,R.ID2,R.CTIME
FROM V$LOCK H,V$LOCK R
WHERE H.BLOCK = 1 AND R.REQUEST > 0 AND H.SID <> R.SID
and H.TYPE <> 'MR' AND R.TYPE <> 'MR'
AND H.ID1 = R.ID1 AND H.ID2 = R.ID2 AND H.TYPE=R.TYPE
AND H.LMODE > 0 AND R.REQUEST > 0 ORDER BY 1,2;
③
select /*+ rule */ l.block blk_status, l.inst_id, l.sid, /* s.serial#, */ p.spid, s.status, s.sql_hash_value hash_value, s.program, s.username, s.last_call_et, /* s.sql_id,s.sql_child_number c_n,*/ l.lmode, l.type, l.request, l.ctime from (select inst_id, sid, type, id1, id2, lmode, request, ctime, decode(block, 0, 'blocked', 1, 'blocking', 2, 'global') block from gv$lock where (id1, id2, type) in (select id1, id2, type from gv$lock where request > 0)) l, gv$session s, gv$process p where l.sid = s.sid and l.inst_id = s.inst_id and s.inst_id = p.inst_id and s.paddr = p.addr and s.username is not null order by 1, inst_id, status; |
④
| |
11g upgrade
Infinity I/O SAN Bootcamp O'Reilly.Mastering.Perl,Intermediate.Perl mysql...... oracle on linux!! |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26526320/viewspace-2121361/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26526320/viewspace-2121361/