oracle rebuild online,ORACLE alter index rebuild online 操作产生的锁

ORACLE通过锁和闩的方式实现了并发控制，v$lock 视图列出了数据库中当前拥有的锁以及未完成的锁请求。当发觉有session处于等待事件时 ，可以通过v$lock查询信息。

v$lock中的常用列有以下列：

sid：持有锁的会话SID，通常与v$session关联。

type：锁的类型，其中TM表示表锁或DML锁，TX表示行锁或事务锁，UL表示用户锁。我们主要关注TX和TM两种型的锁，其它均为系统锁，会很快自动释放，不用关注。当 Oracle执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。TM 锁包括了SS 、 SX、 S 、X 等多种模式，在数据库中用 0 －6 来表示。不同的 SQL 操作产生不同类型的 TM锁。

lmode：会话保持的锁的模式。

0＝None；

1＝Null ;

2＝Row-S (SS,行级共享锁，其他对象只能查询这些数据行)，sql操作有select for update、lock for update、lock row share；

3＝Row-X (SX,行级排它锁,在提交前不允许做DML操作)，sql操作有insert、update、delete、lock row share；

4＝Share(共享锁)，sql操作有create index、lock share；

5＝S/Row-X (SSX,共享行级排它锁)，sql操作有lock share row exclusive；

6＝Exclusive(排它锁)，alter table、drop table、drop index、truncate table、look exclusive等DDL

ID1,ID2:  ID1,ID2的取值含义根据type的取值而有所不同，对于TM 锁ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0；对于TX 锁ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号，其组形式: 0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER，ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数。实际上这两个字段构成了事务在回滚段中的位置。

对于alter index rebuild online引发的的锁，下面我们来验证一下

另外打开一个服务端口，对索引idx_a1 做rebuild online 操作，发现索引的rebuild操作迟迟不能完成。这个时候需要通过第三个窗口查询一下v$lock，发现锁的模式是TM，通过hash值获得sqltext内容正是我们的索引rebuild onlin操作

说明在做rebuild online操作，会等待它之前未提交dml操作，直到dml操作释放资源

下面是MOS上给出的解释，原理很重要，认真看完。

========

- Online Index rebuild takes a long time.

- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

Symptoms:

=========

Performance issues while rebuilding very large indexes.

- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.

- Issuing index rebuild ONLINE statement => finishes in about an hour.

- This behavior of ONLINE index rebuilds makes it a non-option for large tables

as it just takes too long to scan the table to rebuild the index. The offline may not be feasible due to due to the 24/7 nature of the database.

- This may be a loss of functionality for such situations.

- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter hanging behavior indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:

--------------------

We can trace the sessions rebuilding the indexes with 10046 level 12.

Comparing the IO reads for the index-rebuild and the index-rebuild-online reveals the following:

-ONLINE index rebuilds

It scans the base table and it doesn't scan the blocks of the index.

-OFFLINE index rebuilds

It scans the index for the build operation.

- This behaviour is across all versions.

Cause

Cause/Explanation

=============

When you rebuild index online,

- it will do a full tablescan on the base table.

- At the same time it will maintain a journal table for DML data, which has

changed during this index rebuilding operation.

So it should take longer time, specially if you do lots of DML on the same table,while rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle will grab the index in X-mode and rebuild a new index segment by selecting the data from the old index. So here we are

- not allowing any DML on the table hence there is no journal table involved

- and it is doing an index scan

Hence it will be pretty fast.

Fix

Solution/Conclusion:

===========

- The ONLINE index rebuild reads the base table, and this is by design.

- Rebuilding index ONLINE is pretty slow.

- Rebuilding index offline is very fast, but it prevents any DML on the base table.

参考博客：

http://blog.itpub.net/26770925/viewspace-1293245/