昨天做了一次大数据量rebuild index的经历，挺有意思和挑战的，我把这次经历分享出来，供大家讨论
1. 为什么要rebuild index?
在表上频繁的update和delete的操作会导致索引出现很多空间碎片，从而使得访问该索引的SQL查询效率下降，通过rebuild index,可以回缩空间碎片，并提供查询效率。
2. 问题描述
OS信息： Solaris 10
数据库信息： Oracle 10.2.0.4，两节点的RAC
需要重建schema(TLMDBA)下所有的索引，总大小为782G：
A105024@O02RCD3>select sum(BYTES)/1024/1024/1024||'G' from dba_segments where WNER='TLMDBA' and SEGMENT_TYPE='INDEX';
SUM(BYTES)/1024/1024/1024||'G'
-----------------------------------------
782.255126953125G
其中有两个表的索引比较大，分别是ITEM表，有174G:
A105024@O02RCD3>select sum(BYTES)/1024/1024/1024||'G' from dba_segments where WNER='TLMDBA' and SEGMENT_TYPE='INDEX' and SEGMENT_NAME like '%ITEM%';
SUM(BYTES)/1024/1024/1024||'G'
-----------------------------------------
174.479248046875G
和AUDIT_TRAIL表，有437G:
A105024@O02RCD3>select sum(BYTES)/1024/1024/1024||'G' from dba_segments where WNER='TLMDBA' and SEGMENT_TYPE='INDEX' and SEGMENT_NAME like '%AUDIT_TRAIL%';
SUM(BYTES)/1024/1024/1024||'G'
-----------------------------------------
437.37255859375G
3. 问题分析
1）由于要求要在比较短的时间内(1天)完成，且在该时间段内，应用程序是不跑的，所有我们不采用online的方式以加快速度。
2）为了提高效率，我们把剩下空闲的内存都暂时分配给PGA。
3）为了提高效率，我们参考主机CPU个数，把平行度尽量设大。
4）为了减少redo产生量，提高效率，用NOLOGGING的方式跑。
5）充分利用RAC有两个节点的优势，在两边同时跑。
4. 前期工作
1）增大内存：
把尽量多的空闲内存都分给pga：
总内存大小为49G：
$ prtconf |grep Mem
Memory size: 49152 Megabytes
其中还有26G空闲：
$ vmstat
kthr memory page disk faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 92817784 26035744 384 2744 722 55 54 0 0 0 8 5 0 2830 21851 4366 3 1 96
首先确定当前的PGA管理方式为AUTO:
A105024@O02RCD3>show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
再看一下原来pga的大小：
A105024@O02RCD3>show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 2G
记住这个配置，因为我们等rebuild index结束后，我们要恢复为原来的配置。
把pga增大为22G:
A105024@O02RCD3>alter system set pga_aggregate_target=22g scope=memory sid='*';
System altered.
确认一下是否修改成功：
A105024@O02RCD3>show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 22G
2）增大临时表空间
要保证临时表空间比最大的index还要大一些。
最大的index为173G:
A105024@O02RCD3>select max(BYTES)/1024/1024/1024||'G' from dba_segments where WNER='TLMDBA' and SEGMENT_TYPE='INDEX';
MAX(BYTES)/1024/1024/1024||'G'
-----------------------------------------
173.9619140625G
原有的临时表空间有247G，已经足够，不需要再增加：
A105024@O02RCD3>select sum(BYTES)/1021/1024/1024||'G' from dba_temp_files where TABLESPACE_NAME='TEMP';
SUM(BYTES)/1021/1024/1024||'G'
-----------------------------------------
247.796278158667972575905974534769833497G
3）增大index的表空间
要保证index所在表空间的空闲空间比改表空间上最大的index还要大一些。
首先查出index所在表空间上最大的index的大小：
A105024@O02RCD3>select TABLESPACE_NAME,max(BYTES)/1024/1024/1024||'G' from dba_segments where SEGMENT_TYPE='INDEX' and WNER='TLMDBA' group by TABLESPACE_NAME order by TABLESPACE_NAME;
TABLESPACE_NAME MAX(BYTES)/1024/1024/1024||'G'
------------------------------ -----------------------------------------
TLM_D_MED 18.2982177734375G
TLM_I_AUDIT_TRAIL_16K 173.9619140625G
TLM_I_ITEM_16K 27.01513671875G
TLM_I_LARGE 8.2547607421875G
TLM_I_MED 7.23779296875G
TLM_I_SMALL 11.330810546875G
TLM_I_STATIC .21240234375G
再查询这些表空间还剩多少空闲空间：
A105024@O02RCD3>select tablespace_name, sum(bytes)/1024/1024/1024||'G' from dba_free_space where TABLESPACE_NAME in ('TLM_I_STATIC','TLM_I_MED','TLM_I_LARGE','TLM_I_SMALL','TLM_D_MED','TLM_I_ITEM_16K','TLM_I_AUDIT_TRAIL_16K') group by tablespace_name
2 order by tablespace_name;
TABLESPACE_NAME SUM(BYTES)/1024/1024/1024||'G'
------------------------------ -----------------------------------------
TLM_D_MED 34.8515625G
TLM_I_AUDIT_TRAIL_16K 82.1962890625G
TLM_I_ITEM_16K 84.6650390625G
TLM_I_LARGE 101.707763671875G
TLM_I_MED 8.78302001953125G
TLM_I_SMALL 40.51806640625G
TLM_I_STATIC .3388671875G
比较一下，可以发现表空间TLM_I_AUDIT_TRAIL_16K的空闲空间是不够的，我们需要增加100G：
A105024@O02RCD3>alter tablespace TLM_I_AUDIT_TRAIL_16K add datafile '/drcd04/rcd/o02rcd3ndx2/tlm_i_audit_trail_16k_20.O02RCD3' size 50G;
Tablespace altered.
A105024@O02RCD3>alter tablespace TLM_I_AUDIT_TRAIL_16K add datafile '/drcd04/rcd/o02rcd3ndx1/tlm_i_audit_trail_16k_21.O02RCD3' size 50G;
Tablespace altered.
4. 编辑好rebuild index的脚本
1） 编辑表“ITEM" rebuild index 的脚本
找出表"ITEM"上所有的NORMAL索引，我们这里只重建普通索引，LOB,IOT等类型索引不重建：
A105024@O02RCD3>select OWNER,INDEX_NAME,INDEX_TYPE from dba_indexes where TABLE_OWNER='TLMDBA' and TABLE_NAME='ITEM';
OWNER INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
TLMDBA ITEMIXC NORMAL
TLMDBA ITEMIXD NORMAL
TLMDBA ITEMIXE NORMAL
TLMDBA ITEMIX1_SSC NORMAL
TLMDBA ITEMIXG NORMAL
TLMDBA ITEM_IND_KEY NORMAL
TLMDBA ITEM_IDX_001 NORMAL
TLMDBA ITEMIXA NORMAL
TLMDBA ITEMIXB NORMAL
创建一个脚本为item.sql，内容如下：
spool item.log
set echo on
set timing on
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
alter index TLMDBA.ITEMIXC rebuild parallel 16;
alter index TLMDBA.ITEMIXD rebuild parallel 16;
alter index TLMDBA.ITEMIXE rebuild parallel 16;
alter index TLMDBA.ITEMIX1_SSC rebuild parallel 16;
alter index TLMDBA.ITEMIXG rebuild parallel 16;
alter index TLMDBA.ITEM_IND_KEY rebuild parallel 16;
alter index TLMDBA.ITEM_IDX_001 rebuild parallel 16;
alter index TLMDBA.ITEMIXA rebuild parallel 16;
alter index TLMDBA.ITEMIXB rebuild parallel 16;
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End time" from dual;
spool off
2）编辑表“AUDIT_TRAIL" rebuild index 的脚本
找出表"AUDIT_TRAIL"上所有的NORMAL索引，我们这里只重建普通索引，LOB,IOT等类型索引不重建：
A105024@O02RCD3>select OWNER,INDEX_NAME,INDEX_TYPE from dba_indexes where TABLE_OWNER='TLMDBA' and TABLE_NAME='AUDIT_TRAIL';
OWNER INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
TLMDBA AUDIT_TRAILIXA NORMAL
TLMDBA AUDIT_TRAILIXB NORMAL
TLMDBA AUDIT_TRAIL_IND_KEY NORMAL
创建一个脚本为audit_trail.sql，内容如下：
spool audit_trail.log
set echo on
set timing on
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
alter index TLMDBA.AUDIT_TRAILIXA rebuild parallel 16 nologging;
alter index TLMDBA.AUDIT_TRAILIXB rebuild parallel 16 nologging;
alter index TLMDBA.AUDIT_TRAIL_IND_KEY rebuild parallel 16 nologging;
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End time" from dual;
spool off
3）编辑schema "TLMDBA" 下剩余表rebuild index 的脚本
找出表schema "TLMDBA" 下剩余表上所有的NORMAL索引，我们这里只重建普通索引，LOB,IOT等类型索引不重建：
A105024@O02RCD3>select OWNER,INDEX_NAME,INDEX_TYPE from dba_indexes where TABLE_OWNER='TLMDBA' and TABLE_NAME not in ('ITEM','AUDIT_TRAIL') and INDEX_TYPE='NORMAL';
由于上面这条语句返回太多，这里就不一一列出来了，和前面类似。
再编辑两个脚本remaining_tlmdba_a.sql用于在节点A上跑，remaining_tlmdba_b.sql用于在节点B上跑。
5. 运行脚本
为了充分利用RAC的优势，我们在A,B两节点上同时跑。
节点A:
A105024@O02RCD3>@item.sql
A105024@O02RCD3>set timing on
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
Start time
--------------------------
03-NOV-2011 01:46:16
Elapsed: 00:00:00.00
A105024@O02RCD3>alter index TLMDBA.ITEMIXC rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:37:08.49
A105024@O02RCD3>alter index TLMDBA.ITEMIXD rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:38:43.83
A105024@O02RCD3>alter index TLMDBA.ITEMIXE rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:35:38.63
A105024@O02RCD3>alter index TLMDBA.ITEMIX1_SSC rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:35:44.66
A105024@O02RCD3>alter index TLMDBA.ITEMIXG rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:16:45.56
A105024@O02RCD3>alter index TLMDBA.ITEM_IND_KEY rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:17:57.46
A105024@O02RCD3>alter index TLMDBA.ITEM_IDX_001 rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:35:11.97
A105024@O02RCD3>alter index TLMDBA.ITEMIXA rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:34:46.61
A105024@O02RCD3>alter index TLMDBA.ITEMIXB rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:29:37.80
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End time" from dual;
End time
--------------------------
03-NOV-2011 06:27:51
Elapsed: 00:00:00.00
A105024@O02RCD3>spool off
脚本item.sql跑了大概4.5个小时。
A105024@O02RCD3>@remaining_tlmdba_a.sql
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
Start time
--------------------------
03-NOV-2011 05:55:41
。。。。。。。。。
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End time" from dual;
End time
--------------------------
03-NOV-2011 07:18:27
脚本remaining_tlmdba_a.sql跑了大概1.5个小时
节点B:
A105024@O02RCD3>audit_trail.sql
A105024@O02RCD3>set timing on
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
Start time
--------------------------
03-NOV-2011 01:51:56
Elapsed: 00:00:00.01
A105024@O02RCD3>alter index TLMDBA.AUDIT_TRAILIXA rebuild parallel 16 nologging;
Index altered.
Elapsed: 01:10:36.75
A105024@O02RCD3>alter index TLMDBA.AUDIT_TRAILIXB rebuild parallel 16 nologging;
Index altered.
Elapsed: 01:31:51.16
A105024@O02RCD3>alter index TLMDBA.AUDIT_TRAIL_IND_KEY rebuild parallel 16 nologging;
Index altered.
Elapsed: 00:47:43.17
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End time" from dual;
End time
--------------------------
03-NOV-2011 05:22:07
Elapsed: 00:00:00.00
A105024@O02RCD3>spool off
脚本audit_trail.sql大概跑了3.5个小时。
A105024@O02RCD3>@remaining_tlmdba_b.sql
A105024@O02RCD3>set timing on
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
Start time
--------------------------
03-NOV-2011 08:00:12
。。。。。。。
A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;
Start time
--------------------------
03-NOV-2011 09:55:07
脚本remaining_tlmdba_b.sql大概跑了2个小时
至此，所有的rebuild index脚本都已跑完，总共花了6小时左右。
6. 后期工作
1） 验证schema下所有的index是否都已经rebuild了
A105024@O02RCD3>select object_name,LAST_DDL_TIME from dba_objects where WNER='TLMDBA' and OBJECT_TYPE='INDEX';
rebuild index之后会把LAST_DDL_TIME修改，因此只有看该列的值就可以判断是否有漏网之鱼了。
2）把index的degree恢复为原来的1
A105024@O02RCD3>select distinct DEGREE from dba_indexes where WNER='TLMDBA' and INDEX_TYPE='NORMAL';
DEGREE
----------------------------------------
16
从以上语句的返回结果我们可以看出现在的degree=16，这是因为我们在rebuild index后面加了parallel 16，语句执行完之后会自动把degree设为16，但是由于这个是个OLTP系统，语句执行不太需要并行，所有我们把degree改回原来的1.
编辑一个脚本alter_degree.sql，语句如下：
alter index TLMDBA.AUDIT_TRAILIXA noparallel;
alter index TLMDBA.AUDIT_TRAILIXB noparallel;
alter index TLMDBA.AUDIT_TRAIL_IND_KEY noparallel;
.......................................
然后运行该脚本。
最后再验证一下：
A105024@O02RCD3>select distinct DEGREE from dba_indexes where WNER='TLMDBA' and INDEX_TYPE='NORMAL';
DEGREE
----------------------------------------
1
如果只返回1这个值，就说明对了。
3）把pga_aggregate_target改回2G
alter system set pga_aggregate_target=2G scope=memory sid='*';
=================================
auto pga 受限于 _pga_max_size 真正用到的内存可能不多
Oracle中加速索引创建或重建的方法
以上主要优化的几点：
1.普通多块读和排序多块读的大小
2.直接路径IO的大小，10351 event level 128
3.内存排序空间的大小，10g中存在bug需要2次设置。在10g中针对parallel execution环境也需要设置_sort_multiblock_read_count。但是仅对能从内存获益的排序操作有利，适合大多数场景
4.nologging
5.并行，一般这个业务人员也会想到
6.独立的临时表空间
7.使用备选的排序算法_newsort_type或_newsort_enabled，一般不要用
8.禁用block checksum/checking，不推荐，尽在新系统加载大量数据时使用
=====================================
开并发数还要要时刻监视oracle的wait， 看一下此刻什么最慢，有可能你的存储，在你开4个并发重建的时候，就已经100％ load了，这时候你开更高的并发只会更慢，或者此刻排序区不够，或者os已经开始大量swap了，总之一边作一遍监控，各种问题都要考虑以下，只考虑 oracle的东西还是不全面的。