rebuild index online的锁机制浅析(续)
上一篇文章介绍了Oracle10.2.0.4中rebuild index online的锁机制，在开始和结束的时候需要对表加一个模式为4的TM锁，导致在这两个时刻会短暂的阻塞DML。到了Oracle11g，这种情况有所变化，还是通过同样的实验来观察一下Oracle11g到底做出了怎样的改进，对于DBA来说又有怎样的好处。实验环境为Oracle11.1.0.6。
session 1：
SQL> delete from t where object_id=28; 1 row deleted.
session 2：
SQL> alter index ix_t rebuild online;
session 2同样被挂起，查看v$lock：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
137 DL 13596 0 3 0
137 DL 13596 0 3 0
137 TX 458781 377 0 4
170 TM 13596 0 3 0
137 TM 13596 0 2 0
137 TM 13599 0 4 0
170 TX 458781 377 6 0
137 TX 524304 402 6 0
其中170为session 1，137为session 2。可以看到session 2正在请求一个模式为4的TX锁，注意和Oracle10.2.0.4请求的TM锁是不一样的，而且在我们以前的概念中，TX锁的模式都是6，这里出现了模式4的TX锁请求，应该是Oracle11g中新引入的。那么模式4的TX锁和TM锁有什么不同呢？我们继续前面的实验步骤：
session 3：
SQL> delete from t where object_id=46; 1 row deleted.
session 3的DML操作顺利完成，没有被阻塞。而在10g当中，session 3是会被session 2请求的TM锁所阻塞的，这一点改进是非常有意思的，这样即使rebuid online操作被session 1的长事务阻塞，其他会话的DML操作，只要不和session 1冲突，都可以继续操作，在Oracle10g及以前版本中的执行rebuild index online而造成锁等待的风险被大大的降低了。
接下来在session 1执行rollback，观察rebuild index online执行期间的锁的情况，136是session 3：
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
137 DL 13596 0 3 0
137 DL 13596 0 3 0
137 TM 13596 0 2 0
137 TM 13599 0 4 0
136 TM 13596 0 3 0
136 TX 327684 414 6 0
137 TX 524304 402 6 0
137 TX 524321 402 6 0
等待一段时间，rebuild index online临近结束，再次观察锁的情况：
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
137 DL 13596 0 3 0
137 DL 13596 0 3 0
137 TX 327684 414 0 4
137 TM 13596 0 2 0
137 TM 13599 0 4 0
136 TM 13596 0 3 0
136 TX 327684 414 6 0
137 TX 524304 402 6 0
可以看到session 2又在请求一个模式为4的TX锁，同样的，这个锁也不会阻塞其他的DML。由于session 3的事务没有提交，session 2被阻塞，这时再将session 3执行提交或者rollback，则session 2的rebuild立即完成。
Oracle11g在很多细节方面确实做了不少的优化，而且像这样的优化，对于提高系统的高可用性的好处是不言而喻的，在Oracle11g中，执行rebuild index online的风险将比10g以及更老版本中小得多，因为从头至尾都不再阻塞DML操作了，终于可以算得上名副其实的online操作了。
rebuild index online的锁机制浅析
一般都说，rebuild index online不阻塞DML操作，这是相对于rebuild index来说的，加上了online，只是在rebuild的期间不阻塞DML，但是在开始和结束阶段还是可能阻塞其他进程的DML的，要弄清楚到底是阻塞还是不阻塞，何处阻塞，为什么阻塞，还是要从锁的角度来分析。本文实验环境为Oracle 10.2.0.4。
Oracle中的锁，一共有6两种模式：
- 0：none
- 1：null 空
- 2：Row-S 行共享(RS)：共享表锁，sub share
- 3：Row-X 行独占(RX)：用于行的修改，sub exclusive
- 4：Share 共享锁(S)：阻止其他DML操作，share
- 5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
- 6：exclusive 独占(X)：独立访问使用，exclusive
我们知道，DML操作一般要加两个锁，一个是对表加模式为3的TM锁，一个是对数据行的模式为6的TX锁。只要操作的不是同一行数据，是互不阻塞的。但是rebuild index online在开始和结束的时候是需要对表加一个模式为4的TM锁的，这个可以很容易通过实验观察到，实验中的测试表t是通过create table t as select * from all_objects生成，并且多次执行insert into t select * from t产生较多的数据，以便延迟rebuild的时间来观察系统中锁的情况：
session 1：
SQL> delete from t where object_id=28; 1 row deleted.
session 2：
SQL> alter index ix_t rebuild online;
Session 2被阻塞，会话挂起，这时查询v$lock，可以得到如下结果：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1622 TX 655398 1361 6 0
Oracle 9i和10g在create index和rebuild index的统计信息的区别
在9.2.0.6和10.2.0.4做了个小小的试验，演示9i和10g对于create index和rebuild index时统计信息的区别。这里列出试验的过程，由于结果比较明显，就懒得写太多文字做说明了。其中tbsql是一个常用脚本的集成环境，tbsql tabstat用户输出一个表以及列和索引的信息，其实就是关联dba_tables/dba_indexes/dba_tab_columns的一个查询。
先来看9.2.0.6的情况：
SQL> create table t as select * from all_objects; Table created. SQL> create index t_id on t(object_id); Index created.
$tbsql tabstat t
Table Number Empty Average Chain Average
OWNER Name of Rows Blocks Blocks Space Count Row Len
---------- ------ -------- ------- ------- -------- -------- --------
SYS T
Column Column Distinct
Name Details Values Density
------------------ ------------------------ -------- -------
OWNER VARCHAR2(30) NOT NULL
OBJECT_NAME VARCHAR2(30) NOT NULL
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER(22) NOT NULL
DATA_OBJECT_ID NUMBER(22)
OBJECT_TYPE VARCHAR2(18)
CREATED DATE NOT NULL
LAST_DDL_TIME DATE NOT NULL
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
13 rows selected.
B Average Average
Index Tree Leaf Distinct Leaf Blocks Data Blocks Cluster
Name Unique Level Blks Keys Per Key Per Key Factor
--------- --------- ----- ----- -------------- ----------- ----------- -------
T_ID NONUNIQUE
Index Column Col Column
Name Name Pos Details
--------- --------------- ---- ------------------------
T_ID OBJECT_ID 1 NUMBER(22) NOT NULL
可以看到到表，列和索引都没有统计信息。
SQL> alter index t_id rebuild online compute statistics; Index altered.
$tbsql tabstat t
Table Number Empty Average Chain Average
OWNER Name of Rows Blocks Blocks Space Count Row Len
------- ------ -------- ------- ------- -------- -------- --------
SYS T 25,420 348 0 0 0 100
Column Column Distinct
Name Details Values Density
----------------- ------------------------ --------- -------
OWNER VARCHAR2(30) NOT NULL
OBJECT_NAME VARCHAR2(30) NOT NULL
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER(22) NOT NULL 25,420 0
DATA_OBJECT_ID NUMBER(22)
OBJECT_TYPE VARCHAR2(18)
CREATED DATE NOT NULL
LAST_DDL_TIME DATE NOT NULL
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
13 rows selected.
B Average Average
Index Tree Leaf Distinct Leaf Blocks Data Blocks Cluster
Name Unique Level Blks Keys Per Key Per Key Factor
-------- --------- ----- ----- --------- ----------- ----------- -------
T_ID NONUNIQUE 1 56 25,420 1 1 22,731
Index Column Col Column
Name Name Pos Details
------- ---------- ---- ------------------------
T_ID OBJECT_ID 1 NUMBER(22) NOT NULL
注意到表，object_id列和索引都有统计信息了
使用虚拟索引进行数据库优化
在数据库优化中，索引的重要性不言而喻。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是数据量较大的时候。
虚拟索引（Virtual Index）不是物理存在的，它并不会创建实际的索引段，只是在数据字典中加了一个索引的记录，使得优化器能够意识到一个索引的存在，从而判断是否使用该索引作为访问路径。当然，实际上最终查询的访问路径是不会使用该虚拟索引的。
所以，虚拟索引的用处就是用来判断一个索引对于sql的执行计划的影响，尤其是对整个数据库的影响，从而判断是否需要创建物理索引。
oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个nosegment关键字即可，B*Tree index和bitmap index都可以。
