大数据量rebuild index过程

昨天做了一次大数据量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的东西还是不全面的。










本文转自 hsbxxl 51CTO博客,原文链接:http://blog.51cto.com/hsbxxl/708886,如需转载请自行联系原作者

上一篇:提高数据中心效率、可用性和容量的五项最佳实践方案


下一篇:阿里云图数据库GDB公测,高度连接数据查询效率提升10倍