Oracle效能分析9：重建索引，oracle效能重建索引

Oracle效能分析9：重建索引，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提供了線上重建索引的方法：

<pre class="sql" name="code">alter index IDX_TEST rebuild online;

線上重建索引不會再導致索引鎖定。

重建分區索引則需要帶上分區資訊：

alter index IDX_TEST rebuild partition partition_name 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索引問題，刪除再重建索引與索引分析

1. 應該是可行的， 具體 會不會節省時間 試一下就可以了。

2. 大概每個月儲存四五十萬的資料，裡面只儲存最新四個月的資料

每次create這7個索引用時都特別長，大概需要三四個小時；

200萬的資料，重建索引花費的時間太長了；很奇怪。

3. 估計之前的 先drop掉索引，然後插入資料完畢後create索引 也是為了避免 插入資料時，索引對插入效率的影響。
 
oracle 重建索引

1、重新收集統計資訊
analyze table 表名 compute statistics for table for all indexes for all indexed columns
analyze index 索引名 compute statistics
2、把表現不同的sql及其執行計畫發上來看看