PLSQL_性能優化系列12_Oracle Index Anaylsis索引分析

2014-10-04 Created By BaoXinjian

一、摘要

1. 索引品質

索引品質的高低對資料庫整體性能有着直接的影響。

良好高品質的索引使得資料庫性能得以數量級别的提升，而低效備援的索引則使得資料庫性能緩慢如牛，即便是使用高檔的硬體配置。

是以對于索引在設計之初需要經過反複的測試與考量。

那對于已經置于生産環境中的資料庫，我們也可以通過查詢相關資料字典得到索引的品質的高低，通過這個分析來指導如何改善索引的性能。

2. 索引建立的基本指導原則

索引的建立應遵循精而少的原則

收集表上所有查詢的各種不同組合，找出具有最佳離散度的列(或主鍵列等)建立單索引

對于頻繁讀取而缺乏比較理想離散值的列為其建立組合索引

對于組合索引應考慮下列因素來制定合理的索引列順序，以下優先級别由高到低來作為索引的前導列，第二列等等

列被使用的頻率

該列是否經常使用“ = ”作為常用查詢條件

列上的離散度

組合列經常按何種順序排序

哪些列會作為附件性列被添加 

二、索引的聚簇因子

聚簇因子是 Oracle 統計資訊中在CBO優化器模式下用于計算cost的參數之一，決定了目前的SQL語句是否走索引，還是全表掃描以及是否作為嵌套連接配接外部表等。

1. 堆表的存儲方式

Oralce 資料庫系統中最普通，最為常用的即為堆表。

堆表的資料存儲方式為無序存儲，也就是任意的DML操作都可能使得目前資料塊存在可用的空閑空間。

處于節省空間的考慮，塊上的可用空閑空間會被新插入的行填充，而不是按順序填充到最後被使用的塊上。

上述的操作方式導緻了資料的無序性的産生。

當建立索引時，會根據指定的列按順序來填充到索引塊，預設的情況下為升序。

建立或重建索引時，索引列上的順序是有序的，而表上的順序是無序的，也就是存在了差異，即表現為聚簇因子。

2. 什麼是聚簇因子(Clustering Factor/CF)

聚簇因子是基于表上索引列上的一個值，每一個索引都有一個聚簇因子。

用于描述索引塊上與表塊上存儲資料在順序上的相似程度，也就說表上的資料行的存儲順序與索引列上順序是否一緻。

在全索引掃描中，CF的值基本上等同于實體I/O或塊通路數，如果相同的塊被連續讀，則Oracle認為隻需要1次實體I/O。

好的CF值接近于表上的塊數，而差的CF值則接近于表上的行數。

聚簇因子在索引建立時就會通過表上存存在的行以及索引塊計算獲得。

3. 聚簇因子結構圖

(1). 良好的索引與聚簇因子的情形

(2). 良好的索引、差的聚簇因子的情形

(3). 差的索引、差的聚簇因子的情形

三、案例 - 表上索引和索引品質

1. 查詢單表上索引列的相關資訊

(1). 從上面的查詢結果可知，目前表TRADE_CLIENT_TBL上含有4個索引，應該來說該表索引存在一定備援。  

(2). 大多數情況下，單表上6-7個索引是比較理想的。過多的索引導緻過大的資源開銷，以及降低DML性能。

2. 擷取指定schema或表上的索引品質資訊報告

(1). 從上面的單表輸出的索引品質可知，出現了4個處于Poor級别的索引，也就是說這些個索引具有較大的聚簇因子，幾乎接近于表上的行了  

(2). 對于這幾個索引的品質還應結合該索引的使用頻率來考量該索引存在的必要性  

(3). 對于聚簇因子，隻能通過重新組織表上的資料來，以及調整相應索引列的順序得以改善

四、案例 - 索引的使用頻率報告

Oracle提供了索引監控特性來判斷索引是否被使用。在Oracle 10g中，收集統計資訊會使得索引被監控，在Oracle 11g中該現象不複存在。

盡管如此，該方式僅提供的是索引是否被使用。索引被使用的頻率未能得以展現。

下面的腳本将得到索引的使用率，可以很好的度量索引的使用情況以及根據這個值來判斷目前的這些索引是否可以被移除或改進。\

參考了沙彌大神

1. 判斷索引是否被使用

(1). 上面的結果列出了目前資料庫中schema為SH且索引大小大于1MB的索引的使用頻率。

(2). 由于目前的資料庫為标準版，沒有分區表功能，是以可以看到很多arc結尾的表，且索引很大，如ACC_POS_STOCK_TBL_ARC上索引達到19G。

(3). 表SALES的主鍵SALES_PROD_BIX上範圍掃描最多，總計被使用次數為3次。

(4). 對于上述列出的被使用的次數為0的那些索引，應考慮索引的設定是否合理。

(5). 過大的索引應考慮能否使用索引壓縮。

(6). 最後列出的是報告的schema名稱以及索引大小的過濾條件、索引被收集的日期。注，索引列的大小sum求和有些不準确。

2. 總結

本使用了2個替代變量，一個是schema，一個是索引的大小。

預設情況下，對于那些較小 的索引以及僅僅運作一至兩次的sql語句的曆史執行計劃不會被收集到DBA_HIST_SQL_PLAN。

是以執行腳本時索引大小輸入的建議值是100。

如果需要收集所有的曆史sql執行計劃來判斷索引是否被使用，需要修改statistics_level為all或者修改snapshot的收集政策。

收集政策對系統性能有一定的影響，以及耗用大量磁盤空間，是以Prod環境應慎用(UAT和DEV則無妨)。

腳本下載下傳 （由了沙彌大神整理，借用下）

1. idx_info.sql               http://files.cnblogs.com/eastsea/idx_info.zip

2. idx_quality.sql           http://files.cnblogs.com/eastsea/idx_quality.zip

3. idx_usage_detail.sql   http://files.cnblogs.com/eastsea/idx_usage_detail.zip

Thanks and Regards

參考：了沙彌大神　http://blog.csdn.net/leshami/article/details/23687137

<b>ERP技術讨論群: 288307890</b>

<b>技術交流，技術讨論，歡迎加入</b>

<b>Technology Blog Created By Oracle ERP - 鮑建立</b>