马克西姆–出埃及记
星期五, 一月 25th, 2008马克西姆–出埃及记
[youku cf00XMjYyMDM4NA]
Archive for 一月, 2008
v$lock
星期五, 一月 25th, 2008Column Datatype Description
ADDR RAW(4 | 8) Address of lock state object
KADDR RAW(4 | 8) Address of lock
SID NUMBER Identifier for session holding or acquiring the lock
TYPE VARCHAR2(2) Type of user or system lock
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
–我们主要关注TX和TM两种类型的锁
–UL锁用户自己定义的，一般很少会定义，基本不用关注
–其它均为系统锁，会很快自动释放，不用关注
ID1 NUMBER Lock identifier #1 (depends on type)
ID2 NUMBER Lock identifier #2 (depends on type)
—当lock type 为TM时，id1为DML-locked object的object_id
—当lock type 为TX时，id1为usn+slot，而id2为seq。
–当lock type为其它时，不用关注
LMODE NUMBER 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)
–大于0时表示当前会话以某种模式占有该锁，等于0时表示当前会话正在等待该锁资源，即表示该会话被阻塞。
–往往在发生TX锁时，伴随着TM锁，比如一个sid=9会话拥有一个TM锁，一般会拥有一个或几个TX锁，但他们的id1和id2是不同的，请注意
REQUEST NUMBER 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)
–大于0时，表示当前会话被阻塞，其它会话占有改锁的模式
CTIME NUMBER Time since current mode was granted
BLOCK NUMBER The lock is blocking another lock
0, ‘Not Blocking’, /* Not blocking any other processes */
1, ‘Blocking’, /* This lock blocks other processes */
2, ‘Global’, /* This lock is global, so we can’t tell */
–该锁是否阻塞了另外一个锁
data block
星期五, 一月 25th, 2008file block header
星期四, 一月 24th, 2008Start dump data blocks tsn: 4 file#: 4 minblk 3 maxblk 3
buffer tsn: 4 数据文件对应的 tablespace 的 number ,这只是dump文件中记录的数据而已,block无此信息
rdba: 0×01000003 (4/3) datafile 中 block 的地址，标示的第几个 block
scn: 0×0000.00093761 system change number
seq: 0×01 A sequence number incremented for each change to a block at the same SCN
— A new SCN is allocated if the sequence number wraps.
— 同一个SCN影响这个block中的行数大于 254 行就会为这个事务分配一个新的SCN
— 如下面的操作就可能引起同一个SCN但影响的同一个block 中的行超过254行
— “delete from table_name”
— 影响的行数(最大254) 是用从 0×01 到 0xfe 表示的
— 当这个byte 的数据为 0xff 的时候标志这个 block 坏调了—> ora-01578
查找当前在shared pool中被pin住的对象 (eygle)
星期四, 一月 24th, 2008查找当前在shared pool中被pin住的对象
–1.获得Library Cache Pin等待的对象的信息
–Note: X$KGLOB–[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
col KGLNAOWN for a10
col KGLNAOBJ for a20
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
FROM x$kglob
WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE ‘library%’)
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
——– ——– ——– ———- ——————– ———- ——–
973AA6AC 727BECF0 727BECF0 TAOBAO CHECK_TAB_COL 902274209 727BEAD8
–2.获得持有等待对象的session信息
–Note:X$KGLPN–[K]ernel [G]eneric [L]ibrary Cache Manager object [P]i[N]s
SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE ‘library%’)
–3.获得持有对象用户执行的代码
SELECT sql_text
FROM v$sqlarea
WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
SELECT sql_address, sql_hash_value
FROM v$session
WHERE SID IN (
SELECT SID
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE ‘library%’)))
