Oracle效能分析9：重建索引

標籤：database   oracle   效能   資料庫   

當索引出現問題時，會導致嚴重的效能問題，索引問題包括索引不可用、索引片段導致效能下降，我們需要一些手段在檢測索引的問題，並解決這些問題。這一篇將為你講述怎麼定位索引問題，並提供瞭解決的辦法。

索引不可用

索引停用原因有很多，包括：
 1）索引空間耗盡，導致SQL*Loader更新索引失敗；
 2）建立索引的過程中執行個體失敗；
 3）唯一鍵有重複值；
 4）某個索引的順序與sorted indexes子句中指定的順序不同；
 5）移動表或表分區（alter table move和alter table move partition）；
 6）對錶執行線上重定義；
 7）截斷表分區（alter table truncate partition）；
 8）匯入分區；
 9）刪除表分區；
 10）拆分表的分區或子分區（alter table split partition）；
 11）分區索引的維護操作（alter index split partition）。
除了上述的這些原因之外，你還可以手動的將索引標註為不可用，這樣可以使批量載入速度更快，下面是把索引的狀態改變為停用方法：

alter index IDX_HISTORYALARM_HOUR$01 unusable

如果你的索引為分區索引，這個操作將導致所有分區的索引都不可用，你也可以指定某個分區的索引不可用：

alter index IDX_HISTORYALARM$02 modify partition HISTORYALARM20140731 unusable

通過下面的方法可以查看索引的狀態：

select ind.INDEX_NAME,ind.status,ind.PARTITIONED from user_indexes ind where index_name like '%HISTORYALARM%'INDEX_NAMESTATUSPARTITIONED---------------------------------------------------------------------IDX_HISTORYALARM_HOUR$01UNUSABLENOIDX_HISTORYALARM$02N/AYES

可以看到，全域索引的狀態已經變為UNUSABLE，但本地索引的狀態標識為N/A，通過下面的方法可以查看本地索引在每一個分區中的索引狀態：

INDEX_NAMEPARTITION_NAMESTATUS------------------------------------------------------------------------------------------IDX_HISTORYALARM$02HISTORYALARM20140731UNUSABLEIDX_HISTORYALARM$02HISTORYALARM20140801USABLEIDX_HISTORYALARM$02HISTORYALARM20140802USABLEIDX_HISTORYALARM$02HISTORYALARM20140803USABLEIDX_HISTORYALARM$02HISTORYALARM20140804USABLEIDX_HISTORYALARM$02HISTORYALARM20140805USABLE......

可以看到分區HISTORYALARM20140731的索引已經標註為UNUSABLE。
當索引被標註為不可用後，最佳化器就會忽略這些索引，Oracle在DML更改表時也不再維護這些索引，如果希望最佳化器再次使用它，就必須先重建（rebuild）索引。

索引片段

隨著時間的推移，由於大量的刪除操作，索引可能會產生片段。Oracle文檔（Performance Tuning Manual for Oracle DataBase 11.2）建議運行"analyze...validate"語句來識別需要重建的索引，這個操作會將索引的統計資料放到INDEX_STATS視圖中，下面是該視圖中的關鍵列：
 1）高度（HEIGHT）：索引的高度，從1開始，1代表只有根的索引；
 2）塊數（BLOCKS）：分配給索引的塊數；
 3）葉行數（LF_ROWS）：葉行數（包括已刪除的行）；
 4）已刪除的葉行數（DEL_LF_ROWS）：已刪除尚未清理的葉行條目數；
 5）已用空間（USED_SPACE）：索引內使用的總空間（包括已刪除的條目）；
 6）已用百分比（PCT_USED）：索引內使用空間的百分比（包括已刪除的條目）。。計算公式：(USED_SPACE / BTREE_SPACE) * 100;
 7）B樹空間（BTREE_SPACE）：索引的總大小（包括已刪除的條目）。
下面通過一個例子來學習該視圖的使用。
先建立一個測試表格，並在上面建立索引：

create table test as select rownum id,'Test' text from dual connect by level <= 100000;create index idx_test on test(id);

然後執行索引分析語句：

analyze index idx_test validate structure;

注意在執行分析語句之前INDEX_STATS視圖是空的，現在查詢該視圖來檢查被刪除的葉行數：

select lf_rows,lf_blks,del_lf_rows from index_stats;LF_ROWSLF_BLKSDEL_LF_ROWS--------------------------------------------------------------------------------1000002220

這裡可以看出刪除的葉行數為0，接下來我們刪除表中大量的行，再次運行分析語句：

delete test where id <= 99999;commit;analyze index idx_test validate structure;

然後查詢被刪除的葉行數：

select lf_rows,lf_blks,del_lf_rows from index_stats;LF_ROWSLF_BLKSDEL_LF_ROWS-----------------------------------------------------------10000022299999

為了讓Oracle能夠得到正確的執行計畫，我們先收集表和索引的統計資訊：

begin  dbms_stats.gather_table_stats(ownname   => user,                                tabname => 'TEST',                                cascade   => TRUE);end;

然後執行一個索引範圍掃描的查詢：

select * from test where id > 10;call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.27          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        1      0.01       1.53         56        224          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        3      0.01       1.81         56        224          0           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 5  Rows     Row Source Operation-------  ---------------------------------------------------      1  TABLE ACCESS BY INDEX ROWID TEST (cr=224 pr=56 pw=0 time=1538212 us)      1   INDEX RANGE SCAN IDX_TEST (cr=223 pr=56 pw=0 time=1527442 us)(object id 58594)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                       2        0.00          0.00  SQL*Net message from client                     2        0.79          0.83  db file sequential read                        56        0.03          0.41********************************************************************************

查詢使用了索引範圍掃描，雖然整個表只有一條資料，但由於索引中的索引片段，導致索引掃描任然讀取了56個資料區塊。下面重建該索引：

alter index IDX_TEST rebuild;

然後按上面的方法產生並查看索引資訊：

LF_ROWSLF_BLKSDEL_LF_ROWS-----------------------------------110

可以看出索引片段消失。

注意事項：

 1）執行索引分析會鎖定表，直到索引分析完畢才解除鎖定；
 2）在大多數情況下，Oracle會儘可能的重用索引中已刪除條目的空間。

為什麼重建索引

實際上，重建（rebuild）索引就是重新建立索引，但它比刪除原索引再重新建立索引的做法要好，因為在重建索引時儲存空間已指派給索引，而不需要再指定索引建立語句。
關於索引重建有很多依據，但其中有一些並不準確，下面列舉了一些：
 1）Oracle的B樹索引隨著時間的推移變得不平衡
 由於B樹索引的根塊和所有的葉塊之間的高度始終是一致的，所以這不正確。
 2）索引中被刪除的空間無法重用
 實際上Oracle會重用被刪除的空間。
 3）達到一定層數的索引是低效的
 索引的層數取決於索引有多少條目，重建不能解決問題。
 4）具有糟糕的聚蔟因子的索引，可以通過重建修複
 重建索引並不能改變表中資料行或索引的順序，因此聚蔟因子（見使用索引的聚蔟因子）完全不受索引重建影響。如果想改進聚蔟因子，實際上需要重建表。
那麼具體為什麼重建索引呢？

當索引不可用時，應該重建索引，但我們是否應該重建索引來消除索引片段呢？
當你的查詢大部分都是通過索引訪問讀取單個行，那麼重建索引對效能影響很小。但對於範圍查詢，由於大量的索引片段會導致查詢增加大量的無效IO，因此重建索引是有意義的，即使Oracle會重用索引片段，但重建索引也可以使索引變得更加緊湊，從而提高查詢的效率。

重建索引

上面已經使用到重建索引的方法：

alter index IDX_TEST rebuild;

但重建索引的過程中會對錶加鎖，阻止其他對錶的操作，直到索引重建完成。從Oracle 10g開始，Oracle提供了線上重建索引的方法：

alter index IDX_TEST rebuild online;

線上重建索引不會再導致索引鎖定。
Oracle也為創佳和重建索引提供了一些參數，用於提高建立和重建索引的效率。

並行建立或重建索引

為了建立索引，資料庫需要進行全表掃描，並行建立可以加快索引的建立速度，速度的提升由並行度和CPU數量決定：

create index IDX_TEST on test(id) parallel 4 online;

也可以用於重建索引：

alter index IDX_TEST rebuild parallel 4 online;

需要注意的是這個操作將使索引的並行度（見使用索引）永遠變為這個值，如下：

select degree from user_indexes where index_name = 'IDX_TEST';DEGREE--------------------4

如果打算讓資料庫在處理你的索引時使用並行機制，則正好，否則，你需要在執行了並行建立和重建操作後禁用並行：

alter index IDX_TEST noparallel;

如果忘記禁用並行，可能會導致嚴重的效能問題。

在索引建立或重建時避免產生重做資訊

不把建立或者重建的索引項目寫入重做日誌，可以大大縮短索引建立或重建的時間：

create index IDX_TEST on test(id) nologging online;

也可以在重建索引時使用：

alter index IDX_TEST rebuild nologging online;

nologging不僅可以極大地提高效能，而且不填充多個重做記錄檔，節省空間的。

壓縮索引

在非唯一索引中使用壓縮，可以減少重複鍵佔用的空間：

compress <數字，並小於等於索引包括的欄位值>

一個執行個體如下：

create index IDX_TEST on test(id) compress 1 online;

同樣可以用於重建索引：

alter index IDX_TEST rebuild compress 1 online;

 

 

Oracle效能分析9：重建索引