Oracle 索引品質分析

      索引品質的高低對資料庫整體效能有著直接的影響。良好高品質的索引使得資料庫效能得以數量層級的提升，而低效冗餘的索引則使得資料庫效能緩慢如牛，即便是使用高檔的硬體設定。因此對於索引在設計之初需要經過反覆的測試與考量。那對於已經置於生產環境中的資料庫，我們也可以通過查詢相關資料字典得到索引的品質的高低，通過這個分析來指導如何改善索引的效能。下面給出了示範以及索引建立的基本指導原則，最後給出了索引品質分析指令碼。

 

1、查看索引品質

--擷取指定schema或表上的索引品質資訊報告gx_adm@CABO3> @idx_qualityEnter value for input_owner: GX_ADMEnter value for input_tbname: CLIENT_TRADE_TBL  -->如果我們省略具體的表名則會輸出整個schema的索引品質報告                                 Table      Table                             Index Data Blks Leaf Blks        Clust IndexTable                             Rows     Blocks Index                     Size MB   per Key   per Key       Factor Quality------------------------- ------------ ---------- ------------------------- ------- --------- --------- ------------ -------------CLIENT_TRADE_TBL             6,318,035     278488 I_TDCL_ARC_STL_DATE_STOCK      62       312        13      171,017 5-Excellent                                                  I_TDCL_ARC_STL_DATE_CASH       62       318        13      174,599 5-Excellent                                                  I_TDCL_ARC_CANCEL_DATE         83       238         8      288,678 5-Excellent                                                  I_TDCL_ARC_INPUT_DATE         144       249        13      310,974 5-Excellent                                                  I_TDCL_ARC_TRADE_DATE         144       269        14      337,097 5-Excellent                                                  PK_CLIENT_TRADE_TBL           200         1         1      798,216 2-Good                                                  I_TDCL_ARC_GRP_REF_ID         144         1         1      811,468 2-Good                                                  UNI_TDCL_ARC_REF_ID           136         1         1      765,603 2-Good                                                  I_TDCL_ARC_CONTRACT_NUM        72         1         1      834,491 2-Good                                                  I_TDCL_ARC_SETTLED_DATE        61       299         5      380,699 1-Poor                                                  I_TDCL_ARC_ACC_NUM            184       624         3    3,899,446 1-Poor                                                  I_TDCL_ARC_PL_STK             176       218         1    4,348,804 1-Poor                                                  I_TDCL_ARC_INSTRU_ID          120     2,667         8    4,273,038 1-Poor--從上面的單表輸出的索引品質可知，出現了4個處於Poor層級的索引，也就是說這些個索引具有較大的聚簇因子，幾乎接近於表上的行了--對於這幾個索引的品質還應結合該索引的使用頻率來考量該索引存在的必要性--對於聚簇因子，只能通過重新組織表上的資料來，以及調整相應索引列的順序得以改善             --查詢單表上索引列的相關資訊             gx_adm@CABO3> @idx_infoEnter value for owner: GX_ADMEnter value for table_name: CLIENT_TRADE_TBLTABLE_NAME                INDEX_NAME                     CL_NAM               CL_POS STATUS   IDX_TYP         DSCD------------------------- ------------------------------ -------------------- ------ -------- --------------- ----CLIENT_TRADE_TBL          I_TDCL_ARC_ACC_NUM           ACC_NUM                   1 VALID    NORMAL          ASC                          I_TDCL_ARC_CANCEL_DATE       CANCEL_DATE               1 VALID    NORMAL          ASC                          I_TDCL_ARC_CONTRACT_NUM      CONTRACT_NUM              1 VALID    NORMAL          ASC                          I_TDCL_ARC_GRP_REF_ID        GRP_REF_ID                1 VALID    NORMAL          ASC                          I_TDCL_ARC_INPUT_DATE        INPUT_DATE                1 VALID    NORMAL          ASC                          I_TDCL_ARC_INSTRU_ID         INSTRU_ID                 1 VALID    NORMAL          ASC                          I_TDCL_ARC_PL_STK            STOCK_CD                  1 VALID    NORMAL          ASC                          I_TDCL_ARC_PL_STK            PL_CD                     2 VALID    NORMAL          ASC                          I_TDCL_ARC_SETTLED_DATE      SETTLED_DATE              1 VALID    NORMAL          ASC                          I_TDCL_ARC_STL_DATE_CASH     STL_DATE_CASH             1 VALID    NORMAL          ASC                          I_TDCL_ARC_STL_DATE_STOCK    STL_DATE_STOCK            1 VALID    NORMAL          ASC                          I_TDCL_ARC_TRADE_DATE        TRADE_DATE                1 VALID    NORMAL          ASC                          PK_CLIENT_TRADE_TBL          BUSINESS_DATE             1 VALID    NORMAL          ASC                          PK_CLIENT_TRADE_TBL          REF_ID                    2 VALID    NORMAL          ASC                          UNI_TDCL_ARC_REF_ID          REF_ID                    1 VALID    NORMAL          ASC                        --從上面的查詢結果可知，當前表TRADE_CLIENT_TBL上含有13個索引，應該來說該表索引存在一定冗餘。--大多數情況下，單表上6-7個索引是比較理想的。過多的索引導致過大的資源開銷，以及降低DML效能。

2、索引建立的基本指導原則
     索引的建立應遵循精而少的原則
     收集表上所有查詢的各種不同組合，找出具有最佳離散度的列(或主鍵列等)建立單索引
     對於頻繁讀取而缺乏比較理想離散值的列為其建立複合式索引
     對於複合式索引應考慮下列因素來制定合理的索引列順序，以下優先順序別由高到低來作為索引的前置列，第二列等等
           列被使用的頻率
           該列是否經常使用“ = ”作為常用查詢條件
           列上的離散度
           組合列經常按何種順序排序
           哪些列會作為附件性列被添加  
 
3、索引品質分析指令碼

--script name: idx_quality.sql     --Author : Leshami  --Blog: http://blog.csdn.net/leshami --index quality retrievalSET LINESIZE 145SET PAGESIZE 1000SET VERIFY OFFCLEAR COMPUTESCLEAR BREAKSBREAK ON table_name ON num_rows ON blocksCOLUMN owner FORMAT a14 HEADING 'Index owner'COLUMN table_name FORMAT a25 HEADING 'Table'COLUMN index_name FORMAT a25 HEADING 'Index'COLUMN num_rows FORMAT 999G999G990 HEADING 'Table|Rows'COLUMN MB FORMAT 9G990 HEADING 'Index|Size MB'COLUMN blocks HEADING 'Table|Blocks'COLUMN num_blocks FORMAT 9G990 HEADING 'Data|Blocks'COLUMN avg_data_blocks_per_key FORMAT 999G990 HEADING 'Data Blks|per Key'COLUMN avg_leaf_blocks_per_key FORMAT 999G990 HEADING 'Leaf Blks|per Key'COLUMN clustering_factor FORMAT 999G999G990 HEADING 'Clust|Factor'COLUMN Index_Quality FORMAT A13 HEADING 'Index|Quality'--SPOOL index_quality  SELECT i.table_name,         t.num_rows,         t.blocks,         i.index_name,         o.bytes / 1048576 mb,         i.avg_data_blocks_per_key,         i.avg_leaf_blocks_per_key,         i.clustering_factor,         CASE            WHEN NVL (i.clustering_factor, 0) = 0 THEN '0-No Stats'            WHEN NVL (t.num_rows, 0) = 0 THEN '0-No Stats'            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6 THEN '5-Excellent'            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7 AND 11 THEN '4-Very Good'            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12 AND 15 THEN '2-Good'            WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16 AND 25 THEN '2-Fair'            ELSE '1-Poor'         END            index_quality    FROM dba_indexes i, dba_segments o, dba_tables t   WHERE      --    i.index_name LIKE UPPER ('%&&1%') AND         i.owner = t.owner         AND i.table_name = t.table_name         AND i.owner = o.owner         AND i.index_name = o.segment_name         AND t.owner = UPPER('&input_owner')         AND t.table_name LIKE UPPER('%&input_tbname%')ORDER BY table_name,         num_rows,         blocks,         index_quality DESC;--SPOOL OFF;===========================================================================================--script name: idx_info.sql --get the index column information by specified tableset linesize 180col cl_nam format a20col table_name format a25col cl_pos format 9col idx_typ format a15SELECT b.table_name,           a.index_name,           a.column_name     cl_nam,           a.column_position cl_pos,           b.status,           b.index_type      idx_typ,           a.descend         dscdFROM   dba_ind_columns a, dba_indexes bWHERE  a.index_name = b.index_name           AND owner = upper('&owner')           AND a.table_name LIKE upper('%&table_name%')ORDER  BY 2, 4;

4、相關參考
    Oracle 聚簇因子(Clustering factor) 
    Oracle 索引監控(monitor index)
    Oracle 索引監控與外鍵索引 
    收集統計資訊導致索引被監控 
    Oracle 監控索引的使用率
    NULL 值與索引(一)
    NULL 值與索引(二)
    函數使得索引列失效