梦想有多远

马克西姆–出埃及记

[youku cf00XMjYyMDM4NA]

Column 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 */

–该锁是否阻塞了另外一个锁
 

(更多…)

Start 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住的对象

–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%’)))