Oracle如何高效抛出ora-00001错误
今天吃饭排队的时候，脑子突然闪出一个念头。设想一下以下场景：
在一张大表，可能有上亿条数据，在表中建立了主键或者唯一索引。当有会话插入数据违反唯一性约束时，Oracle是如何快速判断这个值已存在，而抛出ora-00001错误呢？
从性能上来讲，Oracle不太可能将这些数据存放在shared_pool或者其他内存区域中，假如放在内存区域中，必须先解决两个问题：
1、如果有上千个唯一约束表格，在内存中遍历上千表格的数据是不现实的，那性能问题怎么解决？
2、数据库刚启动时，Oracle绝大部分内存区域是空的，但实践证明，即使在数据启动不久，当业务有违反唯一性约束时，Oracle抛出ora-00001错误，依然迅速。
基于以上两点，我们可以反向推出Oracle肯定有另外的机制查找新更新或者插入的数据是否违反唯一性约束。
带着这些问题，进行如下实验：
1、首先创建测试表格
引用
SQL> create table t11 (id number,name varchar2(100));
Table created.
SQL> insert into t11 select rownum id,dbms_random.string('a',30) name from dual connect by level<=200000;
200000 rows created.
SQL> commit;
Commit complete.
增加唯一索引
引用
SQL> create unique index t11_idx on t11(id);
Index created
测试表格和索引的对象头如下：
引用
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='T11' and owner='ZHOUL';
HEADER_FILE HEADER_BLOCK
----------- ------------
15 41867
SQL> select HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='T11_IDX' and owner='ZHOUL';
HEADER_FILE HEADER_BLOCK
----------- ------------
15 51083
为观察结果，将buffer_cache清空：
引用
SQL> alter system flush buffer_cache;
System altered.
采用10046进行跟踪
引用
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Session altered.
SQL> insert into t11 values(10,'test1');
insert into t11 values(10,'test1')
*
ERROR at line 1:
ORA-00001: unique constraint (ZHOUL.T11_IDX) violated
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
解析跟踪文件可以看到数据库执行的情况
引用
PARSING IN CURSOR #2 len=34 dep=0 uid=60 oct=2 lid=60 tim=1277628749310436 hv=3967311368 ad='277a7894'
insert into t11 values(10,'test1')
END OF STMT
PARSE #2:c=0,e=472,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1277628749310431
BINDS #2:
WAIT #2: nam='db file sequential read' ela= 20 file#=15 block#=41867 blocks=1 obj#=1213118 tim=1277628749310583
WAIT #2: nam='db file sequential read' ela= 9 file#=15 block#=41866 blocks=1 obj#=1213118 tim=1277628749310624
WAIT #2: nam='db file sequential read' ela= 8 file#=15 block#=43402 blocks=1 obj#=1213118 tim=1277628749310656
WAIT #2: nam='db file sequential read' ela= 8 file#=15 block#=43506 blocks=1 obj#=1213118 tim=1277628749310686
WAIT #2: nam='db file sequential read' ela= 10 file#=15 block#=51084 blocks=1 obj#=1213119 tim=1277628749310771
WAIT #2: nam='db file sequential read' ela= 9 file#=15 block#=51085 blocks=1 obj#=1213119 tim=1277628749310804
41867块为T11表格段头，从段头中获取二级位图地址：0x03c0a38a
引用
Start dump data blocks tsn: 8 file#: 15 minblk 41867 maxblk 41867
buffer tsn: 8 rdba: 0x03c0a38b (15/41867)
scn: 0x0a00.10b5061c seq: 0x01 flg: 0x04 tail: 0x061c2301
frmt: 0x02 chkval: 0xd4ae type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
。。。
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x03c0a38a
Last Level 1 BMB: 0x03c0c38c
Last Level II BMB: 0x03c0a38a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 80 obj#: 1213118 flag: 0x10000000
。。。
41866块为T11表格的二级位图，从二级位图获取一级位图的地址：0x03c0a98a
引用
Start dump data blocks tsn: 8 file#: 15 minblk 41866 maxblk 41866
buffer tsn: 8 rdba: 0x03c0a38a (15/41866)
scn: 0x0a00.10b5056c seq: 0x02 flg: 0x04 tail: 0x056c2102
frmt: 0x02 chkval: 0xdf04 type: 0x21=SECOND LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
。。。
Dump of Second Level Bitmap Block
number: 137 nfree: 120 ffree: 0 pdba: 0x03c0a38b
Inc #: 0 Objd: 1213118
opcode:0
xid:
L1 Ranges :
--------------------
0x03c0a389 Free: 5 Inst: 1
0x03c0a399 Free: 5 Inst: 1
0x03c0a3a9 Free: 5 Inst: 1
0x03c0a3b9 Free: 5 Inst: 1
。。。
0x03c0a98a Free: 5 Inst: 1
0x03c0aa09 Free: 5 Inst: 1
0x03c0aa0a Free: 5 Inst: 1
。。。
43402块为一级位图，反映了T11数据块的分布情况，可以看到该三级位图块长度为64，表示管理着64个块
引用
Start dump data blocks tsn: 8 file#: 15 minblk 43402 maxblk 43402
buffer tsn: 8 rdba: 0x03c0a98a (15/43402)
scn: 0x0a00.10b50571 seq: 0x14 flg: 0x04 tail: 0x05712014
frmt: 0x02 chkval: 0x8e2e type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
。。。
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x03c0a38a poffset: 31
unformatted: 0 total: 64 first useful block: 0
owning instance : 1
instance ownership changed at 06/17/2011 14:20:49
Last successful Search 06/17/2011 14:20:49
Freeness Status: nf1 1 nf2 1 nf3 0 nf4 5
Extent Map Block Offset: 4294967295
First free datablock : 6
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 1213118
--------------------
DBA Ranges :
--------------------
0x03c0a9c9 Length: 64 Offset: 0
0:FULL 1:FULL 2:FULL 3:FULL
4:FULL 5:FULL 6:0-25% free 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL 13:FULL 14:FULL 15:FULL
16:FULL 17:FULL 18:FULL 19:FULL
20:FULL 21:FULL 22:FULL 23:FULL
24:FULL 25:FULL 26:FULL 27:FULL
28:FULL 29:FULL 30:FULL 31:FULL
32:FULL 33:FULL 34:FULL 35:FULL
36:FULL 37:FULL 38:FULL 39:FULL
40:FULL 41:25-50% free 42:FULL 43:FULL
44:FULL 45:75-100% free 46:FULL 47:FULL
48:FULL 49:75-100% free 50:FULL 51:FULL
52:FULL 53:75-100% free 54:FULL 55:FULL
56:FULL 57:75-100% free 58:FULL 59:FULL
60:FULL 61:75-100% free 62:FULL 63:FULL
--------------------
End dump data blocks tsn: 8 file#: 15 minblk 43402 maxblk 43402
43506块为T11表格的数据块,在数据文件的位置远离了上面的一级位图块43506-43402=104
引用
SQL> col owner for a10
SQL> col segment_name for a10
SQL> select owner,segment_name from dba_extents where file_id=15 and 43506 between block_id and BLOCK_ID+BLOCKS-1;
OWNER SEGMENT_NA
---------- ----------
ZHOUL T11
以下是dump文件显示，那Oracle从哪里获取该数据块的dba信息呢？这是个问题。
引用
Start dump data blocks tsn: 8 file#: 15 minblk 43506 maxblk 43506
buffer tsn: 8 rdba: 0x03c0a9f2 (15/43506)
scn: 0x0a00.10b5079a seq: 0x02 flg: 0x04 tail: 0x079a0602
frmt: 0x02 chkval: 0xe0de type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
。。。
Block header dump: 0x03c0a9f2
Object id on Block? Y
seg/obj: 0x1282be csc: 0xa00.10b50614 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3c0a98a ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0013.000.00003644 0x028004b5.0922.0f C--- 0 scn 0x0a00.10b50573
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xe1fe864
。。。
51804块为索引的根节点
引用
Start dump data blocks tsn: 8 file#: 15 minblk 51084 maxblk 51084
buffer tsn: 8 rdba: 0x03c0c78c (15/51084)
scn: 0x0a00.10b50656 seq: 0x01 flg: 0x04 tail: 0x06560601
frmt: 0x02 chkval: 0x8b02 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x03c0c78c
Object id on Block? Y
seg/obj: 0x1282bf csc: 0xa00.10b50624 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x3c0c789 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0a00.10b50624
Branch block dump
=================
header address 229374028=0xdabf84c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 416
kdxcofbo 860=0x35c
kdxcofeo 4331=0x10eb
kdxcoavs 3471
kdxbrlmc 62965645=0x3c0c78d
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 10
51085块为索引的叶节点，在上面我们找到了id=10的记录和rowid，和dump信息匹配
引用
Start dump data blocks tsn: 8 file#: 15 minblk 51085 maxblk 51085
buffer tsn: 8 rdba: 0x03c0c78d (15/51085)
scn: 0x0a00.10b50627 seq: 0x01 flg: 0x04 tail: 0x06270601
frmt: 0x02 chkval: 0x8502 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
。。。
col 0; len 2; (2): c1 0b
row#10[7911] flag: ------, lock: 0, len=11, data:(6): 03 c0 a5 8b 00 0a
。。。
SQL> select dump(10,16) from dual;
DUMP(10,16)
-----------------
Typ=2 Len=2: c1,b
SQL> select rowid,dump(rowid,16) from t11 where id=10;
ROWID DUMP(ROWID,16)
------------------------------ --------------------------------------------------
AAEoK+AAPAAAKWLAAJ Typ=69 Len=10: 0,12,82,be,3,c0,a5,8b,0,9
经过以上实验，我们推出Oracle在建立了唯一性约束的表格中，如果业务程序对改表格的唯一键进行update或者insert时，首先会根据update或者insert后的值进行索引检查，如果发现相同值，则抛出ora-00001错误。
另外需要注意的是在开启开启执行计划的SQLPLUS环境中，由于违反约束，执行计划不会显示
引用
SQL> set autot traceonly exp
SQL> insert into t11 values(10,'test1');
insert into t11 values(10,'test1')
*
ERROR at line 1:
ORA-00001: unique constraint (ZHOUL.T11_IDX) violated
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
