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

Read more of this post

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列和索引都有统计信息了

Read more of this post

使用虚拟索引进行数据库优化

在数据库优化中,索引的重要性不言而喻。但是,在性能调整过程中,一个索引是否能被查询用到,在索引创建之前是无法确定的,而创建索引是一个代价比较高的操作,尤其是数据量较大的时候。

虚拟索引(Virtual Index)不是物理存在的,它并不会创建实际的索引段,只是在数据字典中加了一个索引的记录,使得优化器能够意识到一个索引的存在,从而判断是否使用该索引作为访问路径。当然,实际上最终查询的访问路径是不会使用该虚拟索引的。

所以,虚拟索引的用处就是用来判断一个索引对于sql的执行计划的影响,尤其是对整个数据库的影响,从而判断是否需要创建物理索引。

oracle文档中并没有提到虚拟索引的创建语法,实际上就是普通索引语法后面加一个nosegment关键字即可,B*Tree index和bitmap index都可以。

Read more of this post

无觅相关文章插件,快速提升流量