A-A+

唯一性组合索引引起的插入的sql hang住的分析

2013年12月19日 TroubleShooting 暂无评论 阅读 1,483 次

唯一性组合索引引起的插入的sql hang住的分析
问题分析:拥有相同组合索引列键值的sql,在批量操作时可能会导致undo争用,致使会话挂起。

=============以下为分析过程=============
表TBLCERTREQ有以下有关的唯一性索引
第一个:主键产生的唯一性索引

SQL> select * from dba_ind_columns where table_owner='CAUSER' and table_name='TBLCERTREQ' and index_name='SYS_C001605';

INDEX_OWNE INDEX_NAME                     TABLE_OWNE TABLE_NAME COLUMN_NAME     COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
---------- ------------------------------ ---------- ---------- --------------- --------------- ------------- ----------- ----
CAUSER     SYS_C001605                    CAUSER     TBLCERTREQ REF                           1            32          32 ASC

第二个:组合键值产生的唯一性索引

SQL> select * from dba_ind_columns where table_owner='CAUSER' and table_name='TBLCERTREQ' and index_name='TBLCERTREQ_INDEX02';

INDEX_OWNER          INDEX_NAME                     TABLE_OWNE TABLE_NAME COLUMN_NAME          COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
-------------------- ------------------------------ ---------- ---------- -------------------- --------------- ------------- ----------- ----
CAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ DN                                 1           512         512 ASC
CAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ CERTTMLID                          2            22           0 ASC
CAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ USERSTATUS                         3            22           0 ASC
CAUSER               TBLCERTREQ_INDEX02             CAUSER     TBLCERTREQ STATUSEX                           4            32          32 ASC

创建时间

SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where OBJECT_NAME='SYS_C001605';

OWNER                          OBJECT_NAME          CREATED
------------------------------ -------------------- -------------------
CAUSER                         SYS_C001605          2009/08/01 01:50:45

SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where OBJECT_NAME='TBLCERTREQ_INDEX02';

OWNER                          OBJECT_NAME          CREATED
------------------------------ -------------------- -------------------
CAUSER                         TBLCERTREQ_INDEX02   2009/08/01 01:49:30

第一个会话(session id:29)
插入一条记录但迟迟不提交

SQL> select sid from v$mystat where rownum<2;

       SID
----------
        29

SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
  2  values('08f03e7a3793acd295e1366c41dc5caf','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
  3  'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
  4  113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');

1 row created.

SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
  2  values('08f03e7a3793acd295e1366c41dc5ccf','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
  3  'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
  4  113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
*
ERROR at line 1:
ORA-00001: unique constraint (YALLONKING.TBLCERTREQ_INDEX02) violated		<-同一会话中继续插入组合索引值相同时报违反唯一性索引的错误

第二个会话(session id:33)
正常操作

SQL> select sid from v$mystat where rownum<2;

       SID
----------
        33
		
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
  2  values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
  3  'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
  4  113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
^Cinsert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation			<-此处发现插入hang住了,人为取消

注:这次插入的记录中,(DN,CERTTMLID,USERSTATUS,STATUSEX)组和值和第一个会话插入的一致,也就是在违反唯一性组合索引的情况下插入。
由于第一个会话没有提交,根据事务ACID特性中的隔离性使得会话二无法看到会话一的数据,所以数据库也就不会报违反唯一性的相关错误以至于会话二一直等待会话一提交。如果会话一提交后,则会话二会报违反唯一性的相关错误。
如下:

SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
  2  values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
  3  'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
  4  113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
*
ERROR at line 1:
ORA-00001: unique constraint (YALLONKING.TBLCERTREQ_INDEX02) violated

继续查看将要插入的条目的主键ref是否和已存在的记录重复了

SQL> select * from tblcertreq where ref='08f03e7a3793acd295e1366c41dc5cag';

no rows selected			<-发现主键不存在重复

SQL> select * from tblcertreq where dn='CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN';

no rows selected			<-发现该DN也不存在

但是发现插入USERSTATUS=5却是可以的

SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
  2  values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
  3  'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
  4  113,0,5,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');

1 row created.

SQL> rollback;

回滚掉5后继续插入

SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
  2  values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
  3  'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
  4  113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
//此处继续hang住

第三个会话(session id:36)
在该回话中查看锁相关信息

SQL> select sid from v$mystat where rownum<2;

       SID
----------
        36
SQL> sho user
USER is "SYS"
SQL> SELECT inst_id,DECODE(request, 0, 'Holder :', 'Waiter :') || sid sess,
  2         id1,
  3         id2,
  4         lmode,
  5         2 request,
  6         type
  7    FROM gV$LOCK
  8   WHERE (id1, id2, type) IN
  9         (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
 10   ORDER BY inst_id,id1, request;

   INST_ID SESS                                                    ID1        ID2      LMODE    REQUEST TY
---------- ------------------------------------------------ ---------- ---------- ---------- ---------- --
         1 Waiter :33                                           196614        706          0          2 TX
         1 Holder :29                                           196614        706          6          2 TX

此处发现确实是会话33在等待会话29释放锁
注: TX - Transaction enqueue
LMODE 6 - exclusive (X)
REQUEST 2 - row-S (SS)
ID1:以十进制数值表示该事务所占用的回滚段号与该事务在该回滚段的事务表(Transaction table)中所占用的槽号(slot number,可理解为记录号)。其组成形式为: 0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。
ID2:以十进制数值表示环绕(wrap)次数,即该槽(slot)被重用的次数;

从上述查询结果可知,2个会话争用的资源是同一回滚段资源

将上边的ID1换算如下

SQL> select trunc(196614/65536),mod(196614,65536) from dual;

TRUNC(196614/65536) MOD(196614,65536)
------------------- -----------------
                  3                 6	

说明:争用的是3号回滚段的6号事务槽

查询事务表获得更详细信息

SQL> select ADDR,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;

ADDR                 XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC STATUS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
0000000095A18098          6         32        889          3        535        181         31 ACTIVE
0000000095A753F8          3          6        706          3        322        155         22 ACTIVE

SQL> select FILE_ID, FILE_NAME,TABLESPACE_NAME from dba_data_files where FILE_ID=3;

   FILE_ID FILE_NAME                                          TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
         3 /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf     UNDOTBS1

说明:从上边这个查询可以看到具体争用的是位于3号数据文件(也就是UNDO数据文件,322号数据块上)的3号回滚段的6号事务槽的第706次记录

转储3号回滚段的UNDO段头

SQL> select * from V$ROLLNAME where USN=3;

       USN NAME
---------- ------------------------------
         3 _SYSSMU3_307413221$
		 
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump undo header '_SYSSMU3_307413221$';

System altered.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2310.trc
SQL> oradebug close_trace
Statement processed.

trace中关于事务表信息如下:

  TRN TBL::
 
  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x02c2  0x0013  0x0000.000eab9c  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x01    9    0x00  0x02c2  0x0019  0x0000.000eacba  0x00c00140  0x0000.000.00000000  0x00000001   0x00000000  1387368974
   0x02    9    0x00  0x02c3  0x000c  0x0000.000eae5a  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1387370017
   0x03    9    0x00  0x02c1  0x0016  0x0000.000eae97  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127
   0x04    9    0x00  0x02c2  0x0002  0x0000.000eae0a  0x00c00141  0x0000.000.00000000  0x00000001   0x00000000  1387369828
   0x05    9    0x00  0x02c3  0x0001  0x0000.000eac7d  0x00c00140  0x0000.000.00000000  0x00000003   0x00000000  1387368958
   0x06   10    0x80  0x02c2  0x0005  0x0000.000eafa7  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  0
   0x07    9    0x00  0x02c4  0x000e  0x0000.000eaeaa  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127
   0x08    9    0x00  0x02c3  0x0012  0x0000.000eac26  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x09    9    0x00  0x02c3  0x0015  0x0000.000eab46  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x0a    9    0x00  0x02c1  0x000b  0x0000.000eab23  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x0b    9    0x00  0x02c3  0x0018  0x0000.000eab2d  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x0c    9    0x00  0x02c2  0x0003  0x0000.000eae8e  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127
   0x0d    9    0x00  0x02c1  0x001e  0x0000.000eab78  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x0e    9    0x00  0x02c3  0x0017  0x0000.000eaeb3  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127
   0x0f    9    0x00  0x02c1  0x001d  0x0000.000eab0f  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x10    9    0x00  0x02c4  0x0008  0x0000.000eac19  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x11    9    0x00  0x02c4  0x001a  0x0000.000eabbd  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x12    9    0x00  0x02c3  0x0005  0x0000.000eac33  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368928
   0x13    9    0x00  0x02c3  0x0011  0x0000.000eabae  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x14    9    0x00  0x02c0  0x0021  0x0000.000eabd9  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x15    9    0x00  0x02c1  0x001f  0x0000.000eab57  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x16    9    0x00  0x02c2  0x0007  0x0000.000eaea0  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127
   0x17    9    0x00  0x02c4  0x0020  0x0000.000eaebc  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370127
   0x18    9    0x00  0x02c1  0x0009  0x0000.000eab38  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x19    9    0x00  0x02c2  0x0004  0x0000.000ead49  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1387369353
   0x1a    9    0x00  0x02c2  0x0014  0x0000.000eabcb  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x1b    9    0x00  0x02c2  0x0010  0x0000.000eac07  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x1c    9    0x00  0x02c3  0x001b  0x0000.000eabf9  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x1d    9    0x00  0x02c1  0x000a  0x0000.000eab19  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x1e    9    0x00  0x02c2  0x0000  0x0000.000eab89  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x1f    9    0x00  0x02c1  0x000d  0x0000.000eab67  0x00c0013e  0x0000.000.00000000  0x00000001   0x00000000  1387368927
   0x20    9    0x00  0x02c3  0xffff  0x0000.000eaf30  0x00c00142  0x0000.000.00000000  0x00000001   0x00000000  1387370428
   0x21    9    0x00  0x02c2  0x001c  0x0000.000eabe8  0x00c0013a  0x0000.000.00000000  0x00000001   0x00000000  1387368927

注: 11g中该表只能存储34(0x21=33)条记录,然后就进行覆盖重用
state:9-incative,10-active
说明:发现只有第七个记录(0x06)是活动的,其wrap#=0x02c2(706)

把这个块dump出来继续分析

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 3 block 322;

System altered.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2310.trc

部分trace文件如下:

UNDO BLK:  
xid: 0x0003.006.000002c2  seq: 0x9b  cnt: 0x16  irb: 0x16  icl: 0x0   flg: 0x0000
 
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f38     0x02 0x1e88     0x03 0x1e1c     0x04 0x1d6c     0x05 0x1cc0     
0x06 0x1c38     0x07 0x1bd4     0x08 0x1b90     0x09 0x1b3c     0x0a 0x1af8     
0x0b 0x1aa4     0x0c 0x1a1c     0x0d 0x19c0     0x0e 0x1930     0x0f 0x18f4     
0x10 0x1894     0x11 0x1834     0x12 0x17e0     0x13 0x1770     0x14 0x170c     
0x15 0x1664     0x16 0x15f0     
		... ...
*-----------------------------
* Rec #0x13  slt: 0x06  objn: 71495(0x00011747)  objd: 71495  tblspc: 5(0x00000005)
*       Layer:  11 (Row)   opc: 1   rci 0x00   
Undo type:  Regular undo    Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c00142.009b.0e ctl max scn: 0x0000.000eaafb prv tx scn: 0x0000.000eab05
txn start scn: scn: 0x0000.000eaede logon user: 80
 prev brb: 12583230 prev bcl: 0
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0140000f  hdba: 0x0140000a
itli: 2  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5)
 
*-----------------------------
* Rec #0x14  slt: 0x06  objn: 71496(0x00011748)  objd: 71496  tblspc: 5(0x00000005)
*       Layer:  10 (Index)   opc: 22   rci 0x13   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x1400012 block=0x01400013
(kdxlpu): purge leaf row
key :(33): 
 20 30 38 66 30 33 65 37 61 33 37 39 33 61 63 64 32 39 35 65 31 33 36 36 63
 34 31 64 63 35 63 61 66
 
*-----------------------------
* Rec #0x15  slt: 0x06  objn: 71497(0x00011749)  objd: 71497  tblspc: 5(0x00000005)
*       Layer:  10 (Index)   opc: 22   rci 0x14   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000a.008.000002cf uba: 0x00c00118.00c1.39
                      flg: C---    lkc:  0     scn: 0x0000.000eacc3
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140001a block=0x0140001b
(kdxlpu): purge leaf row
key :(77): 
 43 43 4e 3d 39 35 35 38 30 31 30 30 34 32 31 35 38 38 38 35 2c 4f 55 3d 43
 75 73 74 6f 6d 65 72 73 2c 4f 55 3d 59 55 5a 48 49 2c 4f 3d 43 46 43 41 20
 4f 70 65 72 61 74 69 6f 6e 20 43 41 32 2c 43 3d 43 4e 03 c2 02 0e 02 c1 04
 01 30
 
*-----------------------------
* Rec #0x16  slt: 0x06  objn: 71498(0x0001174a)  objd: 71498  tblspc: 5(0x00000005)
*       Layer:  10 (Index)   opc: 22   rci 0x15   
Undo type:  Regular undo   Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x000a.008.000002cf uba: 0x00c00118.00c1.3a
                      flg: C---    lkc:  0     scn: 0x0000.000eacc3
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1400022 block=0x01400023
(kdxlpu): purge leaf row
key :(27): 
 13 32 30 31 33 2e 31 32 2e 32 30 2d 31 34 3a 35 33 3a 32 39 06 01 40 00 0f
 00 05
 
End dump data blocks tsn: 2 file#: 3 minblk 322 maxblk 322

查看争用的资源

SQL> select utl_raw.cast_to_varchar2('13323031332e31322e32302d31343a35333a3239060140000f0005') from dual;

UTL_RAW.CAST_TO_VARCHAR2('13323031332E31322E32302D
--------------------------------------------------
2013.12.20-14:53:29@  		<-由于测试数据日期相同,无法分辨
		
		
SQL> select utl_raw.cast_to_varchar2('43434e3d393535383031303034323135383838352c4f553d437573746f6d6572732c4f553d59555a48492c4f3d43464341204f7065726174696f6e204341322c433d434e03c2020e02c1040130') from dual;

UTL_RAW.CAST_TO_VARCHAR2('43434E3D3935353830313030
--------------------------------------------------
CCN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA
Operation CA2,C=CN??0		<-由于测试数据DN相同,无法分辨


SQL> select utl_raw.cast_to_varchar2('203038663033653761333739336163643239356531333636633431646335636166') from dual;

UTL_RAW.CAST_TO_VARCHAR2('203038663033653761333739336163643239356531333636633431
--------------------------------------------------------------------------------
 08f03e7a3793acd295e1366c41dc5caf		<-此处为具有可识别性的第一个会话插入的ref(主键)值

至此,发现该回滚段的事物槽存放的第一个一直迟迟没有提交的会话的记录信息

下边继续用bbed查看在物理数据块中的具体值,进一步确认所争用的资源信息

BBED> set dba 3,322
        DBA             0x00c00142 (12583234 3,322)

BBED> find /c 08f03e7a3793acd295e1366c41dc5cag
BBED-00212: search string not found


BBED> find /c 08f03e7a3793acd295e1366c41dc5caf
 File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
 Block: 322              Offsets: 5985 to 6496           Dba:0x00c00142
------------------------------------------------------------------------
 30386630 33653761 33373933 61636432 39356531 33363663 34316463 35636166 
 00000008 004c0008 00140047 17010047 17010005 00000000 0000000b 01060008 
 0c010000 00000042 01c0009b 000e00fb aa0e0000 00290005 ab0e0000 0000009f 
 aa0e00de ae0e0000 0001003e 01c00000 00000050 00000003 0d000008 0c01000f 
 0040010a 004001fa 12030102 00ffff05 0000000c 00180010 00140002 0008000d 
 0100000d 01000000 00000000 0000000a 16201100 00c30202 0d140018 00200042 
 01c0009b 000e0012 02059780 07400081 07400000 00000000 0015ad01 80000000 
 80ee4f00 00440008 00180020 0020000e 0100000c 01000001 00000000 0000000b 
 01201000 00c30204 0d0700c4 02000008 001000ca 020000fa 00c000ce 00040000 
 800000c0 440c0085 b6800092 018000fa 120a0101 00000000 80ec5e00 000080ee 
 48000001 00000008 00180020 0020000e 0100000c 01000001 00000000 0000000b 
 01200f00 00c30204 0d0700c4 02000008 001000ca 020000fa 00c000ce 00020000 
 800000c0 440c005e ec800092 018000fa 120a0101 000000ac 0101ad85 b60080b6 
 85000002 00000008 00180008 0014000e 0100000c 01000001 00000000 0000000b 
 01200e00 00c30203 0d0000a5 ae0e0048 ee800092 018000fa 12030101 00000000 
 0000ad0a 004c0020 00140002 0000000d 0100000d 01000000 00000000 0000000a 

 <32 bytes per line>

BBED> dump /v dba 3,322 offset 5985 count 32
 File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
 Block: 322     Offsets: 5985 to 6016  Dba:0x00c00142
-------------------------------------------------------
 30386630 33653761 33373933 61636432 l 08f03e7a3793acd2
 39356531 33363663 34316463 35636166 l 95e1366c41dc5caf	

 <16 bytes per line>
 

以上均确认了争用的资源是会话一持有的3号数据文件的3号回滚段的6号事务槽的第706次记录位。

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: