oracle查询基表阻塞,叶摇 » Blog Archive » index 重建实验观查1

index 重建实验观查1

索引的重构由：drop and re-create,rebuild,rebuild online

1.准备

SQL> create table index_rebuild_test (a number);

Table created.

SQL> select count(*) from index_rebuild_test;

COUNT(*)

----------

6981120

SQL> select sid from v$mystat where rownum=1;

SID

----------

139

SQL> create index ind_a on index_rebuild_test(a);

Index created.

在创建索引的过程中，开session2查询锁的sid=139锁的信息：

SQL> col object_name for a20

SQL> select object_name,lmode from dba_objects d,v$lock l where l.type='TM' and l.id1=d.object_id and sid=139;

OBJECT_NAME LMODE

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

OBJ$ 3

INDEX_REBUILD_TEST 4

正常普通的创建index，oracle会对基表obj$加share锁，由于share锁和row-x是不兼容的，所有在创建index时，不能对基表做DML操作。

下面看rebuild时锁的情况：

-- session 1

SQL> alter index ind_a rebuild;

Index altered.

-- session 2

SQL> select object_name,lmode from dba_objects d,v$lock l where l.type='TM' and l.id1=d.object_id and sid=139;

OBJECT_NAME LMODE

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

INDEX_REBUILD_TEST 4

-- 此时session 2的执行计划

SQL> select /*+ index(INDEX_REBUILD_TEST) */ count(*) from INDEX_REBUILD_TEST;

Execution Plan

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

Plan hash value: 3571554732

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 15822 (1)| 00:03:10 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| IND_A | 7337K| 15822 (1)| 00:03:10 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

rebuild索引时，只在对象上加了4号共享锁，和create一样。

在索引正在rebuild时，别的session查询相应的表，依然可以走索引，说明在rebuild时，并没有删除旧的索引，直到新的索引创建成功。由此也可以想象的到，在rebuild时，索引的表空间是正常创建索引的2倍。

下面看下rebuild online

-- session 1

SQL> alter index ind_a rebuild online ;

Index altered.

-- session 2 查询时执行计划依然走索引

SQL> select /*+ index(INDEX_REBUILD_TEST) */ count(*) from INDEX_REBUILD_TEST;

Execution Plan

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

Plan hash value: 3571554732

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 15822 (1)| 00:03:10 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| IND_A | 7337K| 15822 (1)| 00:03:10 |

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

Note

-----

- dynamic sampling used for this statement (level=2)

-- session 3 查询锁的情况

SQL> select object_name,lmode from dba_objects d,v$lock l where l.type='TM' and l.id1=d.object_id and sid=139;

OBJECT_NAME LMODE

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

INDEX_REBUILD_TEST 2

SYS_JOURNAL_81308 4

-- session 4 插入数据

SQL> insert into think.index_rebuild_test values(100000) ;

1 row created.

执行计划依然走旧的索引，索引 变成了2号锁，多出一个sys_journal_81308

设置跟踪事件，可看sys_journal_81308是什么：

SQL> alter session set events '10046 trace name context forever ,level 12';

Session altered.

SQL> alter index think.ind_a rebuild online;

Index altered.

SQL>

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_8076.trc

SQL> alter session set events '10046 trace name context off';

Session altered.

create table "THINK"."SYS_JOURNAL_81308" (C0 NUMBER, opcode char(1), partno number,

rid rowid, primary key( C0 , rid )) organization index TABLESPACE "THINK"

END OF STMT

PARSE #3:c=0,e=4562,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=11114012684

CREATE UNIQUE INDEX "THINK"."SYS_IOT_TOP_81316" on "THINK"."SYS_JOURNAL_81308"("C0","RID")

INDEX ONLY TOPLEVEL TABLESPACE "THINK"

STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) NOPARALLEL

END OF STMT

PARSE #7:c=0,e=1480,p=0,cr=4,cu=0,mis=1,r=0,dep=2,og=4,plh=436696080,tim=11114134543

drop table "THINK"."SYS_JOURNAL_81308" purge;

SQL> select object_name from dba_objects where object_id=81308;

OBJECT_NAME

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

IND_A

对操作对象加2号锁，防止对象结构被破坏。增加了一个对象sys_journal_81308

是为了在线重建索引时，不阻塞dml操作而生成的，它存储的是在索引重建期间的发生的基表的数据变化，当索引在线重建完成后，再加上临时表sys_journal_81308上的数据，就不漏索引数据了。其中的数字代表正重建索引的object_id

总结：

1、rebuild会阻塞对基表的DML操作，但不会影响rebuild期间查询对原有索引的使用。

2、rebuild的数据源可能是基表，也可能是原索引。取决于基表和原索引的大小，那个小，rebuild时就会用那个作为数据源。

3、rebuild online运行用户在索引重建期间执行DML操作。

4、rebuild online的数据源是基表。