大資料量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 9281778426035744384 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 integer2G
記住這個配置，因為我們等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 integer22G

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-201101: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-201106: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-201105:55:41

。。。。。。。。。

A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "End time" from dual;

End time
--------------------------
03-NOV-201107: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-201101: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-201105: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-201108:00:12

。。。。。。。

A105024@O02RCD3>select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Start time" from dual;

Start time
--------------------------
03-NOV-201109: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='*';