online indexbuild (online create或者rebuild index)是oracle的一个非常常用的online操作，我们知道当创建索引或者重建索引没有加online关键字的话，会请求表对象上的4号TM锁，而DML请求的是3号TM锁，3和4的TM锁并不兼容，所以在索引创建或者重建期间是无法进行DML操作的，等待事件为enq: TM – contention。为了改进这一缺陷，在oracle 8i推出了online indexbuild功能，并且在11.1该功能得到了进一步增强。online indexbuild就不会影响其他DML操作的正常运行。本文将深入解析 online indexbuild的工作原理。
在分析online indexbuild实现原理之前，先介绍一下 indexbuild online与非online除了对表持有的TM锁级别不同之外的第二个比较大的区别，就是 indexbuild的执行计划不同。
- indexbuild online只能使用全表扫描的方式
- indexbuild非online，遵循CBO最小cost原则去选择执行计划，索引快速全扫描或者全表扫描
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SQL> explain plan for alter index test.i1 rebuild; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2094272848 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 86355 | 421K| 344 (1)| 00:00:05 | | 1 | INDEX BUILD NON UNIQUE| I1 | | | | | | 2 | SORT CREATE INDEX | | 86355 | 421K| | | | 3 | INDEX FAST FULL SCAN| I1 | | | | | ------------------------------------------------------------------------------- 10 rows selected. SQL> explain plan for alter index test.i1 rebuild online; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3918846387 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 86355 | 421K| 344 (1)| 00:00:05 | | 1 | INDEX BUILD NON UNIQUE| I1 | | | | | | 2 | SORT CREATE INDEX | | 86355 | 421K| | | | 3 | TABLE ACCESS FULL | T1 | 86355 | 421K| 344 (1)| 00:00:05 | ------------------------------------------------------------------------------- 10 rows selected. |
这应该是indexbuild的代码kdic.c中限定的，online indexbuild就只能全表扫描，有这样一段话描述了在设计online indexbuild功能时，索引快速全扫描比全表扫描要难实现很多。
Restartable online indexbuild, means that online build can proceed after the databaserestarts. It is hard to implement because current Oracle implementation does not supportrestartable sort.(it is a future ehancement)• Build the new indexby scanning fromthe old indexsegment. Because the indexrowsmove fromone block to another block in the indexblock split, reading from indexisdifficult to implement than reading from table
那么online indexbuild，是如何不影响dml操作的情况下完成索引创建的呢？我们通过测试来验证，由于online create index和online rebuild index在实现原理上基本一致，所以此次测试online rebuild index即可，测试环境选择的11.2.0.4，通过gdb在kdic.c的重要函数kdicdrv、kdic_create_journal_table、kdic_indrebuild_set_oldidx_fields、kdic_indrebuild_update、kdic_indrebuild_delete上打断点，结合event 10046、event 10704进行深入分析。
测试使用的是TEST.T1表上的索引TEST.I1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select owner,object_id,object_name,object_type from dba_objects where object_name in ('T1','I1'); OWNER OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- ---------- -------------------- ------------------- TEST 87636 I1 INDEX TEST 87596 T1 TABLE SQL> oradebug setmypid; Statement processed. SQL> oradebug event 10046 trace name context forever,level 12; Statement processed. SQL> oradebug event 10704 trace name context forever,level 12; Statement processed. |
第一步：发起online rebuild index，调用kdicdrv接口进行索引重建，该步骤就会对表持有2号的TM锁
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> alter index test.i1 rebuild online; PARSING IN CURSOR #140354389999888 len=52 dep=1 uid=0 oct=26 lid=0 tim=1637408543128280 hv=3177647904 ad='c163a228' sqlid='2hc694qyqf3t0' LOCK TABLE FOR INDEX "TEST"."I1" IN ROW SHARE MODE END OF STMT PARSE #140354389999888:c=515,e=557,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1637408543128279 *** 2021-11-20 19:42:23.128 ksqgtl *** TM-0001562c-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0xbff0cf98, ktcdix=2147483647, topxcb=0xbff0cf98 ktcipt(topxcb)=0x0 |
第二步：调用kdic_create_journal_table，创建一个命名为 sys_journal _indobject#表的索引组织表，并持有该表的4号TM锁，该表对于online indexbuild非常重要，其设计目的是为了记录索引的变更。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> select dbms_metadata.get_ddl('TABLE','SYS_JOURNAL_87636','TEST') from dual; DBMS_METADATA.GET_DDL('TABLE','SYS_JOURNAL_87636','TEST') -------------------------------------------------------------------------------- CREATE TABLE "TEST"."SYS_JOURNAL_87636" ( "C0" NUMBER, "OPCODE" CHAR(1), "PARTNO" NUMBER, "RID" ROWID, PRIMARY KEY ("C0", "RID") ENABLE ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 IN ITRANS 2 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH _CACHE DEFAULT) TABLESPACE "LXY" PCTTHRESHOLD 50 *** 2021-11-20 20:49:00.372 ksqgtl *** TM-000156eb-00000000 mode=4 flags=0x401 timeout=0 *** |
第三步：正式开始进行索引重建，该过程会对ind$、ind_online$做一些变更说明该索引处于online rebuild状态，修改flags为0xa02，并且在索引未创建完成期间新的索引会以临时段存在
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
SQL> select flags,to_char(flags,'xxxxxxxxxxxxxxxxxxxx') from ind$ where obj#=87636; FLAGS TO_CHAR(FLAGS,'XXXXXX ---------- --------------------- 2562 a02 SQL> select * from ind_online$; OBJ# TYPE# FLAGS ---------- ---------- ---------- 87636 1 2562 SQL> select owner,segment_name from dba_segments where segment_type='TEMPORARY'; OWNER SEGMENT_NAME ------------------------------ --------------------------------------------------------------------------------- TEST 5.1498 对于FLAGS的定义为： flags number not null, /* mutable flags: anything permanent should go into property */ /* unusable (dls) : 0x01 */ /* analyzed : 0x02 */ /* no logging : 0x04 */ /* index is currently being built : 0x08 */ /* index creation was incomplete : 0x10 */ /* key compression enabled : 0x20 */ /* user-specified stats : 0x40 */ /* secondary index on IOT : 0x80 */ /* index is being online built : 0x100 */ /* index is being online rebuilt : 0x200 */ /* index is disabled : 0x400 */ /* global stats : 0x800 */ /* fake index(internal) : 0x1000 */ /* index on UROWID column(s) : 0x2000 */ /* index with large key : 0x4000 */ /* move partitioned rows in base table : 0x8000 */ /* index usage monitoring enabled : 0x10000 */ /* 4 bits reserved for bitmap index version : 0x1E0000 */ /* Delayed Segment Creation: 0x4000000 */ 0xa02=index is being online rebuilt+analyzed+global stats |
在此过程中的DML操作如果涉及对该索引的变更都会记录在journal表中，update会以INSERT+DELETE形式记录
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SQL> insert into test.t1 select * from dba_objects where rownum<=1; 1 row created. SQL> commit; Commit complete. SQL> delete from test.t1 where object_id=100; 1 row deleted. SQL> commit; Commit complete. SQL> update test.t1 set object_id=10000 where object_name='T1'; 1 row updated. SQL> commit; Commit complete. SQL> select * from test.SYS_JOURNAL_87636; C0 O PARTNO RID ---------- - ---------- ------------------ 20 I 0 D/////AAFAAABCDAAA 20 I 0 D/////AAFAAABCDAAB 100 D 0 D/////AAFAAABCEAAK 10000 I 0 D/////AAFAAACtsAAw 87596 D 0 D/////AAFAAACtsAAw |
第四步：索引初步重建完成之后，会调用kdic_indrebuild_update函数对ind$和ind_online$的flags进行修改，表示索引初步已创建完成。并且临时段”转正”了。但是在重建期间的dml涉及该索引的变更还没有作用在新索引上。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select flags,to_char(flags,'xxxxxxxxxxxxxxxxxxxx') from ind$ where obj#=87636; FLAGS TO_CHAR(FLAGS,'XXXXXX ---------- --------------------- 4196866 400a02 SQL> select * from ind_online$; OBJ# TYPE# FLAGS ---------- ---------- ---------- 87636 1 4196866 0x400a02=index is being online rebuilt+analyzed+global stats+Delayed Segment Creation SQL> select owner,segment_name from dba_segments where segment_type='TEMPORARY'; no rows selected |
第五步：调用kdic_do_merge函数将 journal表记录的变更合并到新索引上。该阶段在merge过程中接受的dml操作会不断的merge，直至final merge，如果事务未提交，merge请求的4号锁将被TX 6号锁阻塞。
|
1 2 3 4 5 6 7 8 |
SQL> select * from v$lock where type='TX'; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000C2D40DF0 00000000C2D40E48 144 TX 524303 1548 6 0 10 00000000C2D41170 00000000C2D411C8 125 TX 589850 2002 0 4 550 00000000BFAADA70 00000000BFAADAE8 144 TX 589850 2002 6 0 1751 00000000BFB36A38 00000000BFB36AB0 125 TX 196608 1473 6 0 2300 |
During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, it’s reference will be deleted from the branch block.
This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML’s again to do the final merge and drop the journal table before releasing the mode 6 exclsuive table lock.
但是通过event 10704并未看到进程请求过6号TM锁，如果在线重建索引merge期间，有大量的DML操作，那么merge操作将可能会被阻塞。
第六步：再次调用kdic_indrebuild_update 对ind$和ind_online$的flags进行修改，并且进行索引切换。切换完成之前，dml操作会被阻塞，原因是在此阶段会在表和索引的library cache handle上加持有X模式的lock，而dml操作会以S模式请求表的library cache handle的lock，会被X模式阻塞。这样做的目的是为了保证在切换过程中，不再接收新的变更，保证在后续新老索引切换时保证一致性。切换完成之后释放表和索引的library cache handle的X模式lock，此时就不会阻塞DML操作了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SQL> insert into test.t1 select * from dba_objects where rownum<10; --hang SQL> select event,to_char(p3,'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'),to_char(p1,'xxxxxxxxxxxxxxxxxxxxx') from v$session where program like '%sqlplus%' and status='ACTIVE' and state='WAITING'; EVENT TO_CHAR(P3,'XXXXXXXXXXXXXXXXX TO_CHAR(P1,'XXXXXXXXXX ---------------------------------------------------------------- ----------------------------- ---------------------- library cache lock 1562c00010002 c167aeb0 namespace为1(table/procedure)、请求模式为S、object_id为0x1562c SQL> select KGLLKMOD,KGLHDPAR,KGLNAOBJ from x$kgllk where KGLLKSNM=144 and KGLLKMOD=3; KGLLKMOD KGLHDPAR KGLNAOBJ ---------- ---------------- ---------- 3 00000000C17FCB10 I1 3 00000000C167AEB0 T1 切换完成之后： SQL> select flags,to_char(flags,'xxxxxxxxxxxxxxxxxxxx') from ind$ where obj#=87636; FLAGS TO_CHAR(FLAGS,'XXXXXX ---------- --------------------- 134219778 8000802 SQL> select * from ind_online$; OBJ# TYPE# FLAGS ---------- ---------- ---------- 87636 1 4196866 |
第七步：调用kdic_indrebuild_delete函数，并且做online indexbuild的收尾工作，删除 journal、修改索引flags、释放表上的2号TM锁等等
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select * from test.SYS_JOURNAL_87636; select * from test.SYS_JOURNAL_87636 * ERROR at line 1: ORA-00942: table or view does not exist SQL> select flags,to_char(flags,'xxxxxxxxxxxxxxxxxxxx') from ind$ where obj#=87636; FLAGS TO_CHAR(FLAGS,'XXXXXX ---------- --------------------- 2050 802 SQL> select * from ind_online$; no rows selected |
至此online indexbuild流程完毕。