[20211108]索引分裂块清除日志增加(唯一索引)2.txt

来源：这里教程网时间：2026-03-03 17:09:05 作者：

[20211108]索引分裂块清除日志增加(唯一索引)2.txt --//链接http://blog.itpub.net/267265/viewspace-2840853/ 测试了索引分裂时遇到的奇怪现象。 --//看看唯一索引发生分裂时发生的情况,上个星期的测试唯一索引时插入最大值，出现10－90分裂，没有设计好，应该选择50－50分裂 --//的情况。 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.首先确定索引分裂发生的位置： SCOTT@book> create table t1 (id number,vc varchar2(100)); Table created. SCOTT@book> create unique index i_t1_id on t1(id); Index created. SCOTT@book> insert into t1 select rownum,rpad(rownum,100,'x') from dual connect by level<=1e3; 1000 rows created. SCOTT@book> commit ; Commit complete. --//分析略。注意不要遗漏这步，避免查询取样问题的影响。 $ cat treedump.sql column object_id new_value m_index_id select object_id from user_objects where object_name = upper('&&1') and object_type = 'INDEX'; alter session set events 'immediate trace name treedump level &m_index_id'; SCOTT@book> @ treedump.sql i_t1_id OBJECT_ID ---------- 329453 Session altered. --//查看转储文件： branch: 0x10002b3 16777907 (0: nrow: 2, level: 1) leaf: 0x10002b6 16777910 (-1: nrow: 578 rrow: 578) leaf: 0x10002b7 16777911 (0: nrow: 422 rrow: 422) ----- end tree dump --//可以发现唯一索引每块插入的记录更多，这是因为唯一索引rowid部分(不包括data_object_id信息)6字节在键值前面，没有长度指示 --//器,这样每条记录节约1个字节，能容纳更多键值。可以看出插入id=579时出现分裂。 3.开始测试： --//truncate table t1; SCOTT@book> insert into t1 select rownum,rpad(rownum,100,'x') from dual connect by level<=577; 577 rows created. SCOTT@book> commit; Commit complete. SCOTT@book> insert into t1 select 579,rpad(579,100,'y') from dual ; 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24971_0001.trc SCOTT@book> @ treedump.sql i_t1_id OBJECT_ID ---------- 329453 Session altered. --//查看转储文件： ----- begin tree dump leaf: 0x10002b3 16777907 (0: nrow: 578 rrow: 578) ----- end tree dump --//注意不要提交,注意插入不是最大值，不会出现10-90分裂。 SCOTT@book> insert into t1 select 578,rpad(578,100,'z') from dual ; 1 row created. --//注意不要提交。 SCOTT@book> select rowid,id from t1 where id in (1,578,579); ROWID ID ------------------ ---------- AABQdBAAEAAAAIkAAA 1 AABQdBAAEAAAAK8AAy 578 AABQdBAAEAAAAK8AAx 579 --//可以看出id =1与后面插入的id=579,578记录在不同一块中,id=578,589在同一块中。 SCOTT@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24971_0002.trc SCOTT@book> @ treedump.sql i_t1_id OBJECT_ID ---------- 329453 Session altered. --//查看转储文件： ----- begin tree dump branch: 0x10002b3 16777907 (0: nrow: 2, level: 1) leaf: 0x10002b7 16777911 (-1: nrow: 298 rrow: 298) leaf: 0x10002b4 16777908 (0: nrow: 281 rrow: 281) ----- end tree dump --//可以发现发生了索引块分裂50-50，一块占298条(键值id=1-298)，另外一块281条，也就是id=578插入发生在dba=0x10002b4块中。 --//打开新的会话session 1： SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- -------------------------------------------------- 58 5409 24915 DEDICATED 24916 28 174 alter system kill session '58,5409' immediate; --//记下sid=58. $ cat viewsessx.sql column name format a70 SELECT b.NAME, a.statistic#, a.VALUE,a.sid FROM v$sesstat a, v$statname b WHERE lower(b.NAME) like lower('%&1%') AND a.statistic# = b.statistic# and a.sid='&&2' and a.value>0; SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 724 58 SCOTT@book> select * from t1 where id=579; ID VC ---------- ---------------------------------------------------------------------------------------------------- 579 579yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 724 58 --//日志没有增加。唯一索引的好处。 --//测试全表扫描呢？ SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 724 58 SCOTT@book> select /*+ full(t1) */ * from t1 where id=579; ID VC ---------- ---------------------------------------------------------------------------------------------------- 579 579yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 832 58 SCOTT@book> select /*+ full(t1) */ * from t1 where id=579; ID VC ---------- ---------------------------------------------------------------------------------------------------- 579 579yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 940 58 --//可以发现全表扫描会出现日志增加的情况。 SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 940 58 SCOTT@book> select * from t1 where id=578; no rows selected --//看不见正常，因为没有提交。 SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 1048 58 --//日志增加，876-768 = 108. SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 1048 58 SCOTT@book> select rowid from t1 where id=578; no rows selected SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 1156 58 --//日志增加。 SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 1156 58 SCOTT@book> select /*+ full(t1) */ * from t1 where id=578; no rows selected SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 1264 58 --//日志增加。 SCOTT@book> select /*+ full(t1) */ * from t1 where id=678; no rows selected SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ---------- ---------- ---------- redo size 194 1372 58 --//日志增加。 --//测试这里基本情况与前面唯一索引看到的情况一致。 --//唯一索引带来一点点好处就是查询select * from t1 where id=:N; N=1-577,579 不会产生日志. --//而全表扫描select /*+ full(t1) */ * from t1 where id=N1 ;会产生日志 --// 当执行select * from t1 where id=578时 (该记录未提交)，一定会产生日志，在唯一索引我给出解析。 --//仔细想一下当执行select * from t1 where id=578时，首先定位索引块，通过undo重构索引块没有查询到id=578的记录，不用回表。 --//而全表扫描涉及全部数据块，会触摸为提交的脏块，会产生日志。 --//而唯一索引的情况非常特殊select * from t1 where id=:N; N=1-577,579 不会产生日志。 --//问题的源头还是在于，oracle在扫描数据块或者索引段如何知道索引块发生了分裂,为什么一些特殊情况下touch 对应数据块以及索 --//引块时要发生一次Block cleanout record，这样设计的道理何在，那位给出合理的解析。 4.看看日志转储内容。 --//这步不做了，情况与前面类似，就是产生Block cleanout record日志。 5.想起以前的测试： --//链接 http://blog.itpub.net/267265/viewspace-2775396/=>[20210604]索引分裂与 itl ktbitflg.txt --//转抄英文版PDF文档内容 P41: Table 3-2. Columns in the Interested Transaction List ----------------------------------------------------------------------------------------------------- Column Description ----------------------------------------------------------------------------------------------------- ... Flag Bit flag identifying the apparent state of this transaction: ----: active (or "never existed" if every field in the Xid is zero). --U-: Upper bound commit (also set during "fast commit"). C---: Committed and cleaned out (all associated lock bytes have been reset to zero). -B--: May be relevant to the recursive transactions for index block splits. I have seen comments that this flag means the UBA will point to a record holding the previous content of the ITL entry, but I have not managed to confirm this. ---T: I have seen comments that this means the transaction was active during block cleanout, but I have not managed to confirm this. ----------------------------------------------------------------------------------------------------- --//里面提到-B--标识与索引分裂有关.里面提到了recursive transactions,既然是递规事务表示不会回滚的,应该查看索引分裂时可以 --//看到这个标识.转储对应数据块看看。 --//0x10002b7 = set dba 4,695 = alter system dump datafile 4 block 695 = 16777911 --//0x10002b4 = set dba 4,692 = alter system dump datafile 4 block 692 = 16777908 --//0x10002b3 = set dba 4,691 = alter system dump datafile 4 block 691 = 16777907 SCOTT@book> select rowid,id from t1 where id in (1,578,579); ROWID ID ------------------ ---------- AABQdBAAEAAAAIkAAA 1 AABQdBAAEAAAAK8AAy 578 AABQdBAAEAAAAK8AAx 579 SCOTT@book> alter system checkpoint ; System altered. / / SCOTT@book> @ rowid AABQdBAAEAAAAK8AAx OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT ---------- ---------- ---------- ---------- -------------------- -------------------- ---------------------------------------- 329537 4 700 49 0x10002BC 4,700 alter system dump datafile 4 block 700 ; --//alter system dump datafile 4 block 691;索引的root节点。 Block header dump: 0x010002b3 Object id on Block? Y seg/obj: 0x50740 csc: 0x03.1dac5765 itc: 1 flg: E typ: 2 - INDEX ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.014.00037e64 0x00c00d30.1297.01 -BU- 1 fsc 0x0000.1dac5767 Branch block dump ================= header address 140085000411724=0x7f6814b01a4c kdxcolev 1 KDXCOLEV Flags = - - - kdxcolok 1 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 1 kdxconro 1 kdxcofbo 30=0x1e kdxcofeo 8048=0x1f70 kdxcoavs 8018 kdxbrlmc 16777911=0x10002b7 kdxbrsno 0 kdxbrbksz 8056 kdxbr2urrc 0 row#0[8048] dba: 16777908=0x10002b4 col 0; len 3; (3): c2 03 64 --// id=299 ----- end of branch block dump ----- --//注意FLAG=-BU-. --//alter system dump datafile 4 block 692; Block header dump: 0x010002b4 Object id on Block? Y seg/obj: 0x50740 csc: 0x03.1dac5957 itc: 2 flg: E typ: 2 - INDEX ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.014.00037e64 0x00c00d31.1297.02 CB-- 0 scn 0x0003.1dac5767 0x02 0x0002.01d.00002f84 0x00c006bc.032a.1e ---- 1 fsc 0x0000.00000000 Leaf block dump =============== header address 140085000411748=0x7f6814b01a64 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 1 kdxconro 281 kdxcofbo 598=0x256 kdxcofeo 4663=0x1237 kdxcoavs 4065 kdxlespl 0 kdxlende 0 kdxlenxt 0=0x0 kdxleprv 16777911=0x10002b7 kdxledsz 6 kdxlebksz 8032 row#0[4663] flag: ------, lock: 0, len=12, data:(6): 01 00 02 23 00 22 col 0; len 3; (3): c2 03 64 ... row#279[8008] flag: ------, lock: 2, len=12, data:(6): 01 00 02 bc 00 32 col 0; len 3; (3): c2 06 4f --//id=578键值 row#280[8020] flag: ------, lock: 0, len=12, data:(6): 01 00 02 bc 00 31 col 0; len 3; (3): c2 06 50 --//id=579键值 ----- end of leaf block dump ----- --//注意看ITL=0x01,flag=CB--. C表示已经提交，B表示递归事务索引分裂。也就是索引分裂不会回滚的。 --//0x0003.1dac5767 = scn(10): 13382735719 = scn(16): 0x31dac5767 SCOTT@book> select current_scn from v$database; CURRENT_SCN ------------ 13382739012 SCOTT@book> select rowid from t1 where id=578; no rows selected SCOTT@book> select current_scn from v$database; CURRENT_SCN ------------ 13382739020 SCOTT@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24916_0004.trc SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo02.log' scn min 13382739012 scn max 13382739020; System altered. SCOTT@book> alter system checkpoint ; System altered. SCOTT@book> alter system checkpoint ; System altered. SCOTT@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24916_0005.trc SCOTT@book> alter system dump datafile 4 block 692; System altered. --//日志转储： REDO RECORD - Thread:1 RBA: 0x0004b6.00002a4e.0010 LEN: 0x006c VLD: 0x05 SCN: 0x0003.1dac6449 SUBSCN: 1 11/08/2021 09:29:49 (LWN RBA: 0x0004b6.00002a4e.0010 LEN: 0001 NST: 0001 SCN: 0x0003.1dac6449) CHANGE #1 TYP:0 CLS:1 AFN:4 DBA:0x010002b4 OBJ:329536 SCN:0x0003.1dac5957 SEQ:1 OP:4.1 ENC:0 RBL:0 Block cleanout record, scn: 0x0003.1dac6449 ver: 0x01 opt: 0x01, entries follow... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END OF REDO DUMP --//dba=4,692转储： Block header dump: 0x010002b4 Object id on Block? Y seg/obj: 0x50740 csc: 0x03.1dac6449 itc: 2 flg: E typ: 2 - INDEX ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.014.00037e64 0x00c00d31.1297.02 CB-- 0 scn 0x0003.1dac5767 0x02 0x0002.01d.00002f84 0x00c006bc.032a.1e ---- 1 fsc 0x0000.00000000 --//注意看下划线，改变csc的值。ITL槽信息的Scn信息并没有修改，Itl=0x01事务(索引分裂)已经提交。 --//ITL=0x02事务还没有提交。 --//alter system dump datafile 4 block 695 Block header dump: 0x010002b7 Object id on Block? Y seg/obj: 0x50740 csc: 0x03.1dac5775 itc: 2 flg: E typ: 2 - INDEX ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x000a.014.00037e64 0x00c00d31.1297.01 CB-- 0 scn 0x0003.1dac5767 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 Leaf block dump =============== header address 140085000411748=0x7f6814b01a64 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 1 kdxconro 298 kdxcofbo 632=0x278 kdxcofeo 4557=0x11cd kdxcoavs 3925 kdxlespl 0 kdxlende 0 kdxlenxt 16777908=0x10002b4 kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 8032 --//该块的csc应该没有变化。 0x03.1dac5775 与 0x0003.1dac5767 仅仅相差8. --//数据块呢，oracle如何知道了索引发生分裂没有提交，为什么触摸脏块时每次执行一次Block cleanout record， --//alter system dump datafile 4 block 700 ; SCOTT@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_24916_0007.trc SCOTT@book> alter system dump datafile 4 block 700 ; System altered. Block header dump: 0x010002bc Object id on Block? Y seg/obj: 0x50741 csc: 0x03.1dac6348 itc: 2 flg: E typ: 1 - DATA ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ brn: 1 bdba: 0x1000220 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.01d.00002f84 0x00c006bc.032a.1d ---- 1 fsc 0x0000.00000000 0x02 0x000a.00f.00037e62 0x00c00d2f.1297.06 C--- 0 scn 0x0003.1dac5705 --//select /*+ full(t1) */ * from t1 where id=1; --//alter system dump datafile 4 block 700 ; Block header dump: 0x010002bc Object id on Block? Y seg/obj: 0x50741 csc: 0x03.1dac67b1 itc: 2 flg: E typ: 1 - DATA ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ brn: 1 bdba: 0x1000220 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0002.01d.00002f84 0x00c006bc.032a.1d ---- 1 fsc 0x0000.00000000 0x02 0x000a.00f.00037e62 0x00c00d2f.1297.06 C--- 0 scn 0x0003.1dac5705 --//注意看csc前面发生了变化。 --//我估计通过重构块可以知道索引发生了分裂，至于为什么做一次块清除呢我还是不清楚。 --//我找到一个链接blog.sina.com.cn/s/blog_6b8448e70100lvht.html=>索引块分裂引起的交易超时分析(二). --//帖子是2010年的，说明很早之前就有人遇到类似的问题。 5.总结: --//唯一索引分裂时估计影响小一点。 --//还是不清楚oracle为什么要这样设计,不管如何事务还是尽早提交。 6.补充： SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ------------ ------------ ------------ redo size 194 23384 58 SCOTT@book> select * from t1 where id between 510 and 511 ; ID VC ------------ ---------------------------------------------------------------------------------------------------- 510 510xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 511 511xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ------------ ------------ ------------ redo size 194 23492 58 --//可以看出索引范围扫描就不行，redo增加。 SCOTT@book> select * from t1 where id = 510 or id= 511 ; ID VC ------------ ---------------------------------------------------------------------------------------------------- 510 510xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 511 511xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx SCOTT@book> @ viewsessx 'redo size' 58 NAME STATISTIC# VALUE SID ------------------------------ ------------ ------------ ------------ redo size 194 23492 58 --//redo增加。 --//采用or 执行计划是INDEX UNIQUE SCAN。 Plan hash value: 2204817227 ------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | 1 | INLIST ITERATOR | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 65 | 0 (0)| |* 3 | INDEX UNIQUE SCAN | I_T1_ID | 1 | | 0 (0)| ------------------------------------------------------------------------------

编辑推荐：

返回列表

[20211108]索引分裂块清除日志增加(唯一索引)2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推