昨天做了一次大資料量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%';
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%';
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
pga_aggregate_target big integer 2G
記住這個配置，因為我們等rebuild index結束後，我們要恢複為原來的配置。
把pga增大為22G:
A105024@O02RCD3>alter system set pga_aggregate_target=22g scope=memory sid='*';
System altered.
确認一下是否修改成功：
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;
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';
TLMDBA AUDIT_TRAILIXA NORMAL
TLMDBA AUDIT_TRAILIXB NORMAL
TLMDBA AUDIT_TRAIL_IND_KEY NORMAL
建立一個腳本為audit_trail.sql，内容如下：
spool audit_trail.log
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;
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;
Elapsed: 00:38:43.83
A105024@O02RCD3>alter index TLMDBA.ITEMIXE rebuild parallel 16 nologging;
Elapsed: 00:35:38.63
A105024@O02RCD3>alter index TLMDBA.ITEMIX1_SSC rebuild parallel 16 nologging;
Elapsed: 00:35:44.66
A105024@O02RCD3>alter index TLMDBA.ITEMIXG rebuild parallel 16 nologging;
Elapsed: 00:16:45.56
A105024@O02RCD3>alter index TLMDBA.ITEM_IND_KEY rebuild parallel 16 nologging;
Elapsed: 00:17:57.46
A105024@O02RCD3>alter index TLMDBA.ITEM_IDX_001 rebuild parallel 16 nologging;
Elapsed: 00:35:11.97
A105024@O02RCD3>alter index TLMDBA.ITEMIXA rebuild parallel 16 nologging;
Elapsed: 00:34:46.61
A105024@O02RCD3>alter index TLMDBA.ITEMIXB rebuild parallel 16 nologging;
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
A105024@O02RCD3>spool off
腳本item.sql跑了大概4.5個小時。
A105024@O02RCD3>@remaining_tlmdba_a.sql
03-NOV-2011 05:55:41
。。。。。。。。。
03-NOV-2011 07:18:27
腳本remaining_tlmdba_a.sql跑了大概1.5個小時
節點B:
A105024@O02RCD3>audit_trail.sql
03-NOV-2011 01:51:56
Elapsed: 00:00:00.01
A105024@O02RCD3>alter index TLMDBA.AUDIT_TRAILIXA rebuild parallel 16 nologging;
Elapsed: 01:10:36.75
A105024@O02RCD3>alter index TLMDBA.AUDIT_TRAILIXB rebuild parallel 16 nologging;
Elapsed: 01:31:51.16
A105024@O02RCD3>alter index TLMDBA.AUDIT_TRAIL_IND_KEY rebuild parallel 16 nologging;
Elapsed: 00:47:43.17
03-NOV-2011 05:22:07
腳本audit_trail.sql大概跑了3.5個小時。
A105024@O02RCD3>@remaining_tlmdba_b.sql
03-NOV-2011 08:00:12
。。。。。。。
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;
.......................................
然後運作該腳本。
最後再驗證一下：
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，如需轉載請自行聯系原作者
![Nacos 2.0 更新前後性能對比壓測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)