大資料量索引分析

標籤：des   style   blog   http   color   io   os   使用   ar   

2014-10-04 BaoXinjian

一、摘要

PLSQL_效能最佳化系列14_Oracle Index Anaylsis

1. 索引品質

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

良好高品質的索引使得資料庫效能得以數量層級的提升，而低效冗餘的索引則使得資料庫效能緩慢如牛，即便是使用高檔的硬體設定。

因此對於索引在設計之初需要經過反覆的測試與考量。

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

 

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

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

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

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

對於複合式索引應考慮下列因素來制定合理的索引列順序，以下優先順序別由高到低來作為索引的前置列，第二列等等

• 列被使用的頻率
• 該列是否經常使用“ = ”作為常用查詢條件
• 列上的離散度
• 組合列經常按何種順序排序
• 哪些列會作為附件性列被添加 

 

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

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

SQL> @/home/oracle/sql/idx_info.sqlEnter value for owner: SHEnter value for table_name: SALESTable                     Index                     CL_NAM               CL_POS STATUS   IDX_TYP         DSCD------------------------- ------------------------- -------------------- ------ -------- --------------- ----SALES                     SALES_CHANNEL_BIX         CHANNEL_ID                1 N/A      BITMAP          ASC                          SALES_CUST_BIX            CUST_ID                   1 N/A      BITMAP          ASC                          SALES_PROD_BIX            PROD_ID                   1 N/A      BITMAP          ASC                          SALES_PROMO_BIX           PROMO_ID                  1 N/A      BITMAP          ASC                          SALES_TIME_BIX            TIME_ID                   1 N/A      BITMAP          ASC5 rows selected.

(1). 從上面的查詢結果可知，當前表TRADE_CLIENT_TBL上含有4個索引，應該來說該表索引存在一定冗餘。  
(2). 大多數情況下，單表上6-7個索引是比較理想的。過多的索引導致過大的資源開銷，以及降低DML效能。

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

SQL> @/home/oracle/sql/idx_quality.sqlEnter value for input_owner: SHEnter value for input_tbname: SALES                                 Table      Table                             Index Data Blks Leaf Blks        Clust IndexTable                             Rows     Blocks Index                     Size MB   per Key   per Key       Factor Quality------------------------- ------------ ---------- ------------------------- ------- --------- --------- ------------ -------------SALES                          918,843       1769 SALES_PROD_BIX                  0        14         1        1,074 5-Excellent                                                  SALES_CUST_BIX                  0         5         1       35,808 5-Excellent                                                  SALES_TIME_BIX                  0         1         1        1,460 5-Excellent                                                  SALES_CHANNEL_BIX               0        23        11           92 5-Excellent                                                  SALES_PROMO_BIX                 0        13         7           54 5-Excellent     5 rows selected.

(1). 從上面的單表輸出的索引品質可知，出現了4個處於Poor層級的索引，也就是說這些個索引具有較大的聚簇因子，幾乎接近於表上的行了  
(2). 對於這幾個索引的品質還應結合該索引的使用頻率來考量該索引存在的必要性  
(3). 對於聚簇因子，只能通過重新組織表上的資料來，以及調整相應索引列的順序得以改善
 

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

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

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

下面的指令碼將得到索引的使用率，可以很好的度量索引的使用方式以及根據這個值來判斷當前的這些索引是否可以被移除或改進。\

參考了沙彌大神

 

1. 判斷索引是否被使用

SQL> @/home/oracle/sql/idx_usage_detail.sql SH 1                                                                                    IndexTable name                     Index name                     Index type          Size MB Index operation       Executions------------------------------ ------------------------------ --------------- ----------- --------------------- ----------COSTS                          COSTS_PROD_BIX                 BITMAP                 1.75        -                       0                               COSTS_TIME_BIX                 BITMAP                 1.75        -                       0****************************** ****************************** *************** -----------                       ----------sum                                                                                  3.50                                0SALES                          SALES_CHANNEL_BIX              BITMAP                 1.75        -                       0                               SALES_CUST_BIX                 BITMAP                 5.69 SINGLE VALUE                   2                                                                                          FAST FULL SCAN                 1                               SALES_PROD_BIX                 BITMAP                 1.75 SINGLE VALUE                   3                                                                                          FAST FULL SCAN                 1                               SALES_PROMO_BIX                BITMAP                 1.75 FULL SCAN                      1                               SALES_TIME_BIX                 BITMAP                 1.94        -                       0****************************** ****************************** *************** -----------                       ----------sum                                                                                 20.31                                89 rows selected. 

(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

 

 

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

大資料量索引分析