索引rebuild与rebuild online区别
1.0目的，本篇文档探讨索引rebuild 与 rebuild online的区别
2.0猜测：已有的知识
2.1对索引rebuild重建会对表申请tm4级表锁，将会影响业务修改数据，而对索引进行rebuild online则不影响业务修改数据，但是可能会失败。
2.2对索引rebuild online，对于一个大的分区表，rebuild online速度非常慢，而rebuild速度对比很快
2.3对索引rebuild online查询数据库v$lock视图，发现对多个对象存在tm2、某个对象tm4级锁，但是不知道是什么对象
3.0实验思路
1.从锁角度查询区别
2.从执行计划查询区别
3.从10046分析查询区别(没整明白，可忽略）
申明，本次操作版本11.2.0.4
4.0测试看锁
创建一个分区表local 索引，rebuild :rebuild online对比分析，得出结论
4.1 创建测试对象
scott > create table t(id int,name varchar2(20)) partition by hash(id) partitions 4;
set timing on
declare
v_id int;
begin
for v_id in 1 .. 500000
loop
insert into t values(v_id,’test’||v_id);
end loop;
commit;
end;
/
elapsed: 00:01:36.68
#drop index t_ind;
create index t_p_ind on t(id) local;
scott > select a.table_name,a.partition_name,bytes/1024/1024 from user_tab_partitions a,dba_segments b where a.partition_name=b.partition_name and a.table_name=’t’;
table_name partition_name bytes/1024/1024
—————— ——————- —————
t sys_p112 8
t sys_p111 8
t sys_p110 8
t sys_p109 8
–查询重建索引sql
select ‘alter index scott.’
|| index_name
|| ‘ rebuild partition ‘
|| partition_name||’;’
from dba_ind_partitions
where index_owner=’scott’ and index_name=’t_p_ind’;
‘alterindexscott.’||index_name||’rebuildpartition’||partition_name||’;’
———————————————————————-
alter index scott.t_p_ind rebuild partition sys_p113;
alter index scott.t_p_ind rebuild partition sys_p114;
alter index scott.t_p_ind rebuild partition sys_p115;
alter index scott.t_p_ind rebuild partition sys_p116;
test1
#drop table test1 purge;
create table t1 as select * from t;
scott > select a.table_name,segment_name,bytes/1024/1024 from user_tables a,dba_segments b where a.table_name=b.segment_name and a.table_name=’t1′ and owner=’scott’;
table_name segment_name bytes/1024/1024
—————————— ————————– —————
t1 t1 12
create index t_ind on t1(id);
4.2 分区表，rebuild看锁
–会话一、delete操作
scott > delete t where id=1;
1 row deleted.
–会话二、rebuild
hr > alter session set ddl_lock_timeout=60000;
alter index scott.t_p_ind rebuild partition sys_p113;
alter index scott.t_p_ind rebuild partition sys_p114;
alter index scott.t_p_ind rebuild partition sys_p115;
–执行上述三个分区索引rebuild都不存在问题，可以rebuild ok
hr > alter index scott.t_p_ind rebuild partition sys_p116;
–会话hang住
–select v$lock
select * from
(select s.sid,l.type,id1,lmode,request,username,event,sql_id from v$lock l,v$session s where l.sid=s.sid and l.type in(‘tx’,’tm’)) a left join
(select owner,object_name,object_type,object_id from dba_objects)b on a.id1=b.object_id order by 1;
sid ty id1 lmode request username event sql_id owner object object_type object_id
—– — —— —– ——- ——– —————————- ————- ——– ——- —————————-
58 tm 91888 2 0 hr enq: tm – contention 02ywzvmsk9ng4 scott t table 91888
58 tm 91892 0 4 hr enq: tm – contention 02ywzvmsk9ng4 scott t table partition 91892
64 tx 131087 6 0 scott sql*net message from client
64 tm 91892 3 0 scott sql*net message from client scott t table partition 91892
64 tm 91888 3 0 scott sql*net message from client scott t table 91888
6 rows selected.
rebuild local index申请表分区tm4号锁,申请表tm2号锁
–查询申请分区的记录
sys@enmo>select owner,object_name,subobject_name,object_id,object_type from dba_objects where owner=’scott’ and object_name=’t’;
owner object_name subobject_name object_id object_type
——– ————– ——————– ———- ——————-
scott t 91888 table
scott t sys_p109 91889 table partition
scott t sys_p110 91890 table partition
scott t sys_p111 91891 table partition
scott t sys_p112 91892 table partition —
–会话三、delete操作
sys>delete scott.t where id=300000;–hang住
–dml操作被rebuild会话锁住
sys > select inst_id ii,sid,serial#,username,schemaname,sql_id,machine,program,event,blocking_instance bi,blocking_session bs from gv$session where blocking_session is not null order by blocking_session;
ii sid serial# username schemaname sql_id machine program event bi bs
— ————– ——— ———– ————- ——— ————————- ——————– — ——
1 41 2469 sys sys g4022z4dskb8s enmo sqlplus@enmo (tns v1-v3) enq: tm – contention 1 58
1 58 593 hr hr 02ywzvmsk9ng4 enmo sqlplus@enmo (tns v1-v3) enq: tm – contention 1 64
–因此，直接对索引进行rebuild，重建过程中导致业务无法对分区表的该分区字段进行dml操作[
–疑问？ 为何会话3，执行delete操作会hang住，因为该数据存储在索引rebuild分区上，索引申请该分区tm4号锁，堵塞了dml申请tm3号锁，如果确定？
查询blocl_id
select id,rowid,
dbms_rowid.rowid_object(rowid) object#,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#,
dbms_rowid.rowid_row_number(rowid) row# from scott.t where id in(1,300000);
id rowid object# file# block# row#
———- —————— ———- ———- ———- ———-
1 aaawb0aaeaacvpcaap 91892 4 717762 41
300000 aaawb0aaeaacvn8aaw 91892 4 717692 48
select
t.segment_name,
t.partition_name,
t.block_id,(t.blocks + t.block_id -1) “max_block_id”
from sys.dba_extents t,dba_tab_partitions p
where t.partition_name=p.partition_name and p.table_owner=’scott’ and p.table_name=’t’;
segment_na partition_name block_id max_block_id
———- —————————— ———- ————
t sys_p109 714368 715391
t sys_p110 715392 716415
t sys_p111 716416 717439
t sys_p112 717440 718463 –717692–717762
–无法通过视图中查询索引记录的rowid信息，可以通过索引申请object_id，通过被阻塞的rowid找到对应block_id与之对应的partition name
4.3 分区表，rebuild online看锁
–会话一、delete操作
scott > delete t where id=1;
1 row deleted.
–会话二、rebuild
sys > alter session set ddl_lock_timeout=60000;
sys > alter index scott.t_p_ind rebuild partition sys_p116 online;
–会话hang住
–select v$lock
select * from
(select s.sid,l.type,id1,lmode,request,username,event,sql_id from v$lock l,v$session s where l.sid=s.sid and l.type in(‘tx’,’tm’)) a left join
(select owner,object_name,object_type,object_id from dba_objects)b on a.id1=b.object_id order by 1;
sid ty id1 lmode request username event sql_id owner object_name object_type object_id
—- — ———- —– ——- ——– —————————— ————- ——– ————– ————————–
41 tm 91888 2 0 sys enq: tx – row lock contention 821crb119wpc6 scott t table 91888
41 tx 131074 0 4 sys enq: tx – row lock contention 821crb119wpc6
41 tm 91926 4 0 sys enq: tx – row lock contention 821crb119wpc6 scott sys_journal_91897 table 91926
41 tm 91892 2 0 sys enq: tx – row lock contention 821crb119wpc6 scott t table partition 91892
41 tx 393227 6 0 sys enq: tx – row lock contention 821crb119wpc6
58 tm 91888 3 0 scott sql*net message from client scott t table 91888
58 tx 131074 6 0 scott sql*net message from client
58 tm 91892 3 0 scott sql*net message from client scott t table partition 91892
8 rows selected.
–rebuild online 查询申请tm锁记录
sys@enmo>select owner,object_name,subobject_name,object_id,object_type from dba_objects where owner=’scott’ and object_name=’t’;
owner object_name subobject_name object_id object_type
——– ————– —————————— ———- ——————-
scott t 91888 table –rebuild online申请表tm2级锁
scott t sys_p109 91889 table partition
scott t sys_p110 91890 table partition
scott t sys_p111 91891 table partition
scott t sys_p112 91892 table partition –rebuild online申请、分区对象施加tm2号锁
–疑问？
rebuild 申请了一个91926 tm 4号锁，scott sys_journal_91897 table
scott > select * from sys_journal_91897;
no rows selected
scott > desc sys_journal_91897
name null? type
————- ——– —————
c0 not null number(38)
opcode char(1)
partno number
rid not null rowid
4.4 测试查询rebuild online期间系统自动创建的测试表记录的信息
测试对索引rebuild online分区进行delete/update/insert操作，查询临时表记录信息
–会话三、delete操作
sys > delete scott.t where id=300000;
1 row deleted.
select * from sys_journal_91897
c0 o partno rid
———- – ———- ——————————
300000 d 3 d/////aaeaacvn8aaw –允许操作
–查询分区最大的id值
select max(id) from t partition(sys_p112);
max(id)
———-
499989
–update 非分区列，发现临时表不记录信息
update t set name=’name2′ where id=499989;
scott > select * from sys_journal_91897;
c0 o partno rid
———- – ———- ——————————
300000 d 3 d/////aaeaacvn8aaw
–update 其它列，并不记录在此视图中
–update 索引列，发现还需要找到一个值，update后还存在该表中，暂缓update操作测试
update t set id=id+400 where id=499989
*
error at line 1:
ora-14402: updating partition key column would cause a partition change
–由于insert 随机，因此插入四条记录进行测试
scott > select * from sys_journal_91897;
c0 o partno rid
———- – ———- ——————————
300000 d 3 d/////aaeaacvn8aaw
declare
v_id int;
begin
for v_id in 500001 .. 500004
loop
insert into t values(v_id,’test’||v_id);
end loop;
commit;
end;
/
scott > select max(id) from t partition(sys_p112);
max(id)
———-
500002
scott > select * from sys_journal_91897;
c0 o partno rid
———- – ———- ——————————
300000 d 3 d/////aaeaacvn8aaw
500002 i 3 d/////aaeaacvpiaaa 可以看到insert操作本行记录
–对insert记录进行delete,随后修改之前的记录，为insert记录，保证了在一个分区
delete t where id=500002;
update t set id=500002 where id=499989;
–再次查询视图记录
scott > select * from sys_journal_91897;
c0 o partno rid
———- – ———- ——————————
300000 d 3 d/////aaeaacvn8aaw
500002 d 3 d/////aaeaacvpiaaa–之前记录的insert直接转换为delete记录，对一行记录，最后一次变更操作
499989 d 3 d/////aaeaacvpcaao
500002 i 3 d/////aaeaacvpcaao –非常有意思，这里记录一条update,直接转换为一条delete 一条insert
–本次实验可以得到如下结论： 1.rebuild 操作会对分区对象添加tm4号锁，导致该分区对象无法dml操作影响业务
2.rebuild online则不会影响业务对分区字段dml操作，内部通过临时表记录修改信息，索引重建自动维护（rebuild期间修改的数据）
5.0 表索引，rebuild看执行计划
sys > set autotrace on
delete scott.t where id=1;
alter index scott.t_p_ind rebuild partition sys_p116;
sys > select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
–通过种种sql_id找执行计划，均失败
–通过dbms 根据统计信息，模拟执行计划输出！
5.1 rebuild 分区表、分区索引
explain plan for
alter index scott.t_p_ind rebuild partition sys_p116;
select * from table(dbms_xplan.display);
plan hash value: 451004126
—————————————————————————————————
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
—————————————————————————————————
| 0 | alter index statement | | 124k| 609k| 102 (1)| 00:00:02 | | |
| 1 | index build non unique | t_p_ind | | | | | | |
| 2 | sort create index | | 124k| 609k| | | | |
| 3 | partition hash single| | | | | | 4 | 4 |
| 4 | index fast full scan| t_p_ind | | | | | 4 | 4 |
—————————————————————————————————
可以发现，对于分区表，rebuild索引，通过索引范围全扫描，扫描整个分区
索引快速全扫描->单分区全扫描->创建索引，需要排序->索引创建，非唯一->创建索引语句成功执行
5.2 rebuild online分区表、分区索引
explain plan for
alter index scott.t_p_ind rebuild partition sys_p116 online;
select * from table(dbms_xplan.display);
—————————————————————————————————
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
—————————————————————————————————
| 0 | alter index statement | | 124k| 609k| 102 (1)| 00:00:02 | | |
| 1 | index build non unique | t_p_ind | | | | | | |
| 2 | sort create index | | 124k| 609k| | | | |
| 3 | partition hash single| | 124k| 609k| 102 (1)| 00:00:02 | 4 | 4 |
| 4 | table access full | t | 124k| 609k| 102 (1)| 00:00:02 | 4 | 4 |
—————————————————————————————————
可以发现，对于分区表，rebuild online索引，进行的是全表扫描，随后分区扫描，
全表扫描->单分区扫描->
5.1/ 5.2对比，可以理解实际操作，rebuild online为什么一个多小时，rebuild 几分钟，全表扫描，数据量大差异越大
5.3rebuild 普通堆表、global索引
explain plan for
alter index scott.t_ind rebuild;
select * from table(dbms_xplan.display);
——————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
——————————————————————————–
| 0 | alter index statement | | 500k| 2441k| 402 (1)| 00:00:05 |
| 1 | index build non unique| t_ind | | | | |
| 2 | sort create index | | 500k| 2441k| | |
| 3 | index fast full scan| t_ind | | | | |
——————————————————————————–
explain plan for
alter index scott.t_ind rebuild online;
select * from table(dbms_xplan.display);
——————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time |
——————————————————————————–
| 0 | alter index statement | | 500k| 2441k| 402 (1)| 00:00:05 |
| 1 | index build non unique| t_ind | | | | |
| 2 | sort create index | | 500k| 2441k| | |
| 3 | table access full | t1 | 500k| 2441k| 402 (1)| 00:00:05 |
——————————————————————————–
对于普通表，全局索引而言，完全可以采用rebuild online操作，因为执行计划只有一条路！效率无明显差异
5.4rebuild 分区表、global索引、global索引 online对比
#drop index scott.t_p_ind;
#create index t_p_ind on t(id) local;
#create index scott.t_p_ind on scott.t(id);
explain plan for
alter index scott.t_p_ind rebuild;
select * from table(dbms_xplan.display);
———————————————————————————-
| id | operation | name | rows | bytes | cost (%cpu)| time |
———————————————————————————-
| 0 | alter index statement | | 500k| 2441k| 400 (1)| 00:00:05 |
| 1 | index build non unique| t_p_ind | | | | |
| 2 | sort create index | | 500k| 2441k| | |
| 3 | index fast full scan| t_p_ind | | | | |
———————————————————————————-
explain plan for
alter index scott.t_p_ind rebuild online;
select * from table(dbms_xplan.display);
————————————————————————————————–
| id | operation | name | rows | bytes | cost (%cpu)| time | pstart| pstop |
————————————————————————————————–
| 0 | alter index statement | | 500k| 2441k| 400 (1)| 00:00:05 | | |
| 1 | index build non unique| t_p_ind | | | | | | |
| 2 | sort create index | | 500k| 2441k| | | | |
| 3 | partition hash all | | 500k| 2441k| 400 (1)| 00:00:05 | 1 | 4 |
| 4 | table access full | t | 500k| 2441k| 400 (1)| 00:00:05 | 1 | 4 |
————————————————————————————————–
分区表，全局索引rebuild online操作时，会对所有分区hash查询 ，此处未明显判断是否有影响，暂存
结论，在对大的分区表索引进行重建，可以先尝试使用rebuild 而不加online,除非业务确实繁忙，其它场景可直接使用rebuild online 重建即可
6.0 使用10046 进行分区索引的重建
使用分区索引rebuild
分区索引rebuild online
#drop index scott.t_p_ind;
#create index scott.t_p_ind on scott.t(id) local;
查询分区表、分区段、块id
select
t.segment_name,
t.partition_name,
t.block_id,(t.blocks + t.block_id -1) “max_block_id”
from sys.dba_extents t,dba_tab_partitions p
where t.partition_name=p.partition_name and p.table_owner=’scott’ and p.table_name=’t’;
segment_na partition_name block_id max_block_id
———- —————————— ———- ————
t sys_p109 714368 715391
t sys_p110 715392 716415
t sys_p111 716416 717439
t sys_p112 717440 718463
查询分区索引、分区索引段
select index_name,s.partition_name,segment_name,header_file,header_block,header_block+blocks-1 “max_block” from dba_segments s,user_ind_partitions p where p.partition_name=s.partition_name and p.index_name=’t_p_ind’;
index_name partition_ segment_na header_file header_block max_block
———- ———- ———- ———– ———— ———-
t_p_ind sys_p124 t_p_ind 4 722962 723345
t_p_ind sys_p123 t_p_ind 4 686610 686993
t_p_ind sys_p122 t_p_ind 4 725266 725649
t_p_ind sys_p121 t_p_ind 4 154 537
alter session set events ‘10046 trace name context forever, level 12’;
alter index t_p_ind rebuild partition sys_p121;
exit
找出部分记录进行注释说明，不能确保正确，仅限个人理解
–第一部分，sql语句的执行，获取数据字典表信息，执行计划生成 ！！！没看明白，暂时放弃
select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#
fetch
statnested loops
statnested loops
stat’table access by index rowid ind$
stat’index unique scan i_ind1
stat’table access by index rowid obj$ alter session set events ‘10046 trace name context forever, level 12’;
stat’index range scan i_obj1 alter index object_idx rebuild online;
stat’table access cluster user$
stat’index unique scan i_user#
alter session set events ‘10046 trace name context forever, level 12’;
alter index t_p_ind rebuild partition sys_p121 online;
exit
!!暂且放弃10046