[20120712]10g下Oracle Index rebuild online.txt
oracle rebuild online时要建立一张IOT表，为了保证事务依旧能操作，需要记录索引rebuild期间的DML操作。
但是10g与11g下rebuild的机制有一些不同。
index online rebuild 前先建立一张IOT表跟踪后续DML操作，然后merge全部的改变到索引中。
下面通过例子来说明：
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
create table t (id number,name varchar2(10));
create index i_t_id on t(id);
insert into t select rownum id ,'test' name from dual connect by level
commit ;
跟踪rebuild online的过程：
alter session set events '10046 trace name context forever, level 12';
alter index i_t_id rebuild online ;
alter session set events '10046 trace name context off';
--看跟踪文件如下：
create table "SCOTT"."SYS_JOURNAL_254930" (C0 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE "USERS"
CREATE UNIQUE INDEX "SCOTT"."SYS_IOT_TOP_254932" on "SCOTT"."SYS_JOURNAL_254930"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "USERS" NOPARALLEL;
drop table "SCOTT"."SYS_JOURNAL_2543930" purge
可以看出在发出命令alter index i_t_id rebuild online;后,要建立一张IOT表：
C0对应索引字段ID。
OPCODE字段表示操作类型。"I" ＝》 Insert ，"D" ＝》 Delete ,"U"=>UPDATE.
PARTNO字段表示该表T的partition number。
rid字段类型rowid，一定与表的rowid相对应。
SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name='I_T_ID';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
I_T_ID 254930 254931
--建立的IOT表SYS_JOURNAL_254930的数字与I_T_ID的OBJECT_ID对应。
1.开始测试：
会话1插入1行：
SQL> select * from v$mystat where rownum
SID STATISTIC# VALUE
------ ---------- ----------
390 0 1
SQL> insert into t (101,'a');
会话2索引rebuild online：
SQL> select * from v$mystat where rownum
SID STATISTIC# VALUE
---------- ---------- ----------
375 0 1
SQL> alter index i_t_id rebuild online ;
可以发现系统挂起！
执行如下:
SQL> host cat viewlock.sql
SELECT lk.SID, se.username, se.osuser, se.machine,
DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
DECODE (lk.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.lmode)
) mode_held,
DECODE (lk.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lk.request)
) mode_requested,
TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,
DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait
FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TX','TM','UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+);
SQL> @viewlock ;
SID USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
375 SCOTT oracle qyytest DML Share None 254935 0 SCOTT TABLE SYS_JOURNAL_254930 No 0000000221383B78
390 SCOTT oracle qyytest DML Row-X (SX) None 254929 0 SCOTT TABLE T Yes
375 SCOTT oracle qyytest DML Row-S (SS) Share 254929 0 SCOTT TABLE T No 0000000221383B78
390 SCOTT oracle qyytest Transaction Exclusive None 327776 335372 No
--可以发现会话1(SID=390)的事务没有提交，rebuild online无法获得exclusive lock锁。
--注意SID=390,LOCK_TYPE=DML(type='TM'),block=YES.这样其他用户执行DML首先要获得TM锁，这样其他用户执行DML会出现阻塞情况。
打开会话3：
SQL> select * from v$mystat where rownum
SID STATISTIC# VALUE
---------- ---------- ----------
398 0 1
SQL> select * from SYS_JOURNAL_254930;
no rows selected
SQL> insert into t values (102,'b');
会话3挂起！
在回话1执行：
SQL> @viewlock ;
SID USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
375 SCOTT oracle qyytest DML Share None 254935 0 SCOTT TABLE SYS_JOURNAL_254930 No 0000000221383B78
398 SCOTT oracle qyytest DML None Row-X (SX) 254929 0 SCOTT TABLE T No 0000000221383D78
390 SCOTT oracle qyytest DML Row-X (SX) None 254929 0 SCOTT TABLE T Yes
375 SCOTT oracle qyytest DML Row-S (SS) Share 254929 0 SCOTT TABLE T No 0000000221383B78
390 SCOTT oracle qyytest Transaction Exclusive None 327776 335372 No
2.继续测试，回到会话1:
SQL> select * from SYS_JOURNAL_254930;
no rows selected
SQL>rollback;
Rollback complete.
SQL> @viewlock ;
SID USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
375 SCOTT oracle qyytest DML Share None 254935 0 SCOTT TABLE SYS_JOURNAL_254930 No 0000000221383B78
398 SCOTT oracle qyytest DML Row-X (SX) None 254929 0 SCOTT TABLE T Yes
375 SCOTT oracle qyytest DML Row-S (SS) Share 254929 0 SCOTT TABLE T No 0000000221383B78
375 SCOTT oracle qyytest Transaction Exclusive None 589904 365176 No 0000000221383B78
398 SCOTT oracle qyytest Transaction Exclusive None 655391 397962 No
--回到会话3(SID=398),可以发现阻塞的会话执行完成。
--但是注意回话3(SID=398),LOCK_TYPE=DML(type='TM'),block=YES.(原来是回话1).这样其他用户再执行DML也会出现阻塞情况。
--在回话1执行：
SQL> insert into t values (101,'a');
--再次挂起！
3.回到会话3，执行提交语句：
SQL> @viewlock.sql
SID USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
375 SCOTT oracle qyytest DML Share None 254938 0 SCOTT TABLE SYS_JOURNAL_254930 No 0000000221383B78
398 SCOTT oracle qyytest DML Row-X (SX) None 254929 0 SCOTT TABLE T Yes
390 SCOTT oracle qyytest DML None Row-X (SX) 254929 0 SCOTT TABLE T No 0000000221383A78
375 SCOTT oracle qyytest DML Row-S (SS) Share 254929 0 SCOTT TABLE T No 0000000221383B78
375 SCOTT oracle qyytest Transaction Exclusive None 589877 365190 No 0000000221383B78
398 SCOTT oracle qyytest Transaction Exclusive None 655443 397972 No
6 rows selected.
SQL>commit;
检查回话1，发现插入执行完成！why?
因为回话1(sid=390),LOCK_TYPE=DML,mode_request的是Row-X(SX),lock_id1=254929(对应表T),并不会阻塞。
检查会话2，可以发现rebuild online完成。
SQL> @viewlock.sql
SID USERNAME OSUSER MACHINE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
390 SCOTT oracle qyytest DML Row-X (SX) None 254929 0 SCOTT TABLE T No
390 SCOTT oracle qyytest Transaction Exclusive None 196662 332227 No
--可以发现回话1(sid=390),mode_held变成了 Row-X (SX)。
总结：
如果在10g下执行reuild online，之前的事务没有commit或者rollback，rebuild online会挂起，同时其他用户对该表DML操作，都会出现阻塞情况。
时间： 2024-09-20 15:02:05