ALTER INDEX … REBUILD ONLINE AND CREATE INDEX … ONLINE实验

实验结论:

在11g环境下,onlne rebuild的成功完成仍受到表的并行事务影响,即表有排他锁模式6的情况下是无法完成的,但不会阻塞该表新的事务操作(而10g环境下是会阻塞新事务的)

*** online rebuild in 11g can still be impacted by concurrent transactions but it in turn will not cause locking issues for other concurrent transactions on the base table

实验步骤:

select userenv('sid') from dual;              <--查看当前会话id

session1: 2101   session2:960   session3:383

 

*****In Session(1):

SQL>CREATE TABLE qingjiangshi AS SELECT rownum id, 'David Bowie' name FROM dual CONNECT BY LEVEL <= 10000;

SQL>CREATE INDEX qingjiangshi_i ON qingjiangshi(id);

 

****In  other Session(2):

SQL>INSERT INTO qingjiangshi VALUES (10001, 'diandian1');

*****In the orginal session(1);

SQL> ALTER INDEX qingjiangshi_i REBUILD ONLINE;

此时会话被hang(因为无法得到表锁,已经被session2占有)

查询此时的锁情况

SQL> select * from v$lock where sid in ('2101','960','383') order by sid;

可以看到 session1(2101)要请求 4模式的行锁 ,已经被 session1(960)以排他模式 6所占有 ,只能等待释放

******In another session(3):

SQL>INSERT INTO qingjiangshi VALUES (10002, 'diandian2');

注意:这里可以愉快的进行了,不再像10g环境下会受到阻塞而无法继续

此时提交session(2)的会话事务会允许session(1)的rebuild继续,但仍然不会完成,因为会受到session(3)的事务影响

可以看到 session1(2101)完成需要请求的行锁模式 4,已经被 session3(383)用排他模式 6占有而无法完成 ,此时 session2(960)事务完成 ,不再拥有 TM,TX锁了

 ***** In another session(2):

SQL>INSERT INTO qingjiangshi VALUES (10003, 'diandian3');

可以完成,但又会阻塞session1完成rebuild操作的

也就是说 session1完成 rebuild需要等待 session2和 session3都要完成事务才行

SQL> commit (session2)

SQL> commit (session3)

Index altered (session 1)

英文参考:

Although the CREATE INDEX … ONLINE and ALTER INDEX … REBUILD ONLINE options have been available for a long while, they can still introduce locking issues in highly active databases.

Oracle requires a table lock on the index base table at the start of the CREATE or REBUILD process (to guarantee DD information) and a lock at the end of the process (to merge index changes made during the rebuild into the final index structure).

These locks have two implications. Firstly, if there’s an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required, the indexing process will hang. This will of course impact the time it takes to complete the indexing process. However the second far more serious issue is that any other active transactions on the base table starting after the indexing process hangs will likewise be locked and be prevented from continuing, until the indexing process obtains and releases its locks. In highly concurrent environments with many transactions, this can cause serious disruptions to the response times of these impacted transactions. Of course, depending on the time the initial locking transactions take to commit or rollback, this backlog of locked transactions can be quite significant.

Oracle11g has made some improvements in the locking implications regarding creating or rebuilding indexes online.

During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as its done previously until all these prior active transactions have completed. No change so far.

However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully. The indexing process no longer impacts other concurrent transactions on the base table, it will be the only process potentially left hanging while waiting to acquire its associated lock resource.

This means it may not be quite so “risky” to urgently introduce that new index or rebuild that troublesome index during core business hours due to the reduced locking implications introduced in 11g.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/61604/viewspace-2061225/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/61604/viewspace-2061225/