- 作者: 三十而立
- 时间:2009年10月04日 0:26:18
- 本文出自 “inthirties(三十而立)”博客,转载请务必注明作者和保留出处http://blog.csdn.net/inthirties/archive/2009/10/04/4632532.aspx
V$lock动态性能视图,使用了查询数据库里有关锁的信息的视图,当我们调用sql语句或者是通过程序调用procedure,或者是Oraclre自身的一些后台进程执行的时候,如果有对数据库锁的动作,都可以通过这个动态视图进行查询的,我们可以通过这个性能视图的查询跟踪锁的状态和信息,从而有针对性的进行我们的分析和管理工作。
虽然这个视图大家很多人都用过,但是里面的几个字段,我是是常用,但是估计对你们的含义还不是很清楚把,这里我们一起来看看里面的几个很重要的字段的含义。
ADDR
Address of lock state object
KADDR
Address of lock
Lock Type
System Type
BL Buffer hash table instance
CF Control file schema global enqueue
CI Cross-instance function invocation instance
CU Cursor bind
DF Data file instance
DL Direct loader parallel index create
DM Mount/startup db primary/secondary instance
DR Distributed recovery process
DX Distributed transaction entry
FS File set
HW Space management operations on a specific segment
IN Instance number
IR Instance recovery serialization global enqueue
IS Instance state
IV Library cache invalidation instance
JQ Job queue
KK Thread kick
LA .. LP Library cache lock instance lock (A..P = namespace)
MM Mount definition global enqueue
MR Media recovery
NA..NZ Library cache pin instance (A..Z = namespace)
PF Password File
PI, PS Parallel operation
PR Process startup
QA..QZ Row cache instance (A..Z = cache)
RT Redo thread global enqueue
SC System change number instance
SM SMON
SN Sequence number instance
SQ Sequence number enqueue
SS Sort segment
ST Space transaction enqueue
SV Sequence number value
TA Generic enqueue
TS Temporary segment enqueue (ID2=0)
TS New block allocation enqueue (ID2=1)
TT Temporary table enqueue
UN User name
US Undo segment DDL
WL Being-written redo log instance
User Type
TX --- Transaction
TM --- Table Manipulate
UL --- PL/SQL USER LOCK
lmode(Lock mode)
Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
request
Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
Block
0 --- Not blocking any other processes
1 --- This lock blocks other processes
2 --- Global
ID1/ID2
在TM中 ID1为对象的id, ID2为0
在TX中 ID1为以十进制数值表示该事务所占用的回滚段号与该事务在该回滚段的事务表(Transaction table)中所占用的槽号(slot number,可理解为记录号)。其组成形式为:0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。ID2为以十进制数值表示环绕(wrap)次数,即该槽(slot)被重用的次数;
exec dbms_output.put_line( dbms_transaction.local_transaction_id(true) );
运行这个命令就可以看到这里id1,id2对于的USN, Slot, 以及slotCount
SQL> exec dbms_output.put_line( dbms_transaction.local_transaction_id(true) );
12.32.812
这里根据这个,我们可以查到我们对应的ID1和ID2的值, ID2为812, ID1为65536*12+32=786464
我们在V$LOCK里可以找到
引用:
SQL> select * from v$lock where id1=786464;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
173BC4F8 173BC604 13 TX 786464 812 6 0 2973 0
我们也可以直接用select trunc(id1/65536),mod(id1,65536), id2 from v$lock来查询,这里的USN号和Slot号对应这v$transaction表里的记录,有undo就有事务叻。
引用:
SQL> select ADDR, XIDUSN,XIDSLOT,XIDSQN,STATUS from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN STATUS
-------- ---------- ---------- ---------- ----------------
173BC4F8 12 32 812 ACTIVE
知道这里自动的含义,我们奉献一个死锁的查询的sql
引用:
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
wk.TYPE lock_type,
hk.lmode mode_held,
wk.request mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
hk.BLOCK blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
- 三十而立 想流泪,流不出;想说苦,欲说还休
- 技术博客 http://blog.csdn.net/inthirties
- 专业论坛 http://www.inthirties.com
- 个人站点 http://blog.inthirties.com