2014-10-04 BaoXinjian
一、摘要
PLSQL_性能優化系列14_Oracle Index Anaylsis
1. 索引質量
索引質量的高低對數據庫整體性能有著直接的影響。
良好高質量的索引使得數據庫性能得以數量級別的提升，而低效冗余的索引則使得數據庫性能緩慢如牛，即便是使用高檔的硬件配置。
因此對於索引在設計之初需要經過反復的測試與考量。
那對於已經置於生產環境中的數據庫，我們也可以通過查詢相關數據字典得到索引的質量的高低，通過這個分析來指導如何改善索引的性能。
2. 索引創建的基本指導原則
索引的創建應遵循精而少的原則
收集表上所有查詢的各種不同組合，找出具有最佳離散度的列(或主鍵列等)創建單索引
對於頻繁讀取而缺乏比較理想離散值的列為其創建組合索引
對於組合索引應考慮下列因素來制定合理的索引列順序，以下優先級別由高到低來作為索引的前導列，第二列等等
二、案例 - 表上索引和索引質量
1. 查詢單表上索引列的相關信息
SQL> @/home/oracle/sql/idx_info.sql
Enter value for owner: SH
Enter value for table_name: SALES
Table 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 ASC
5 rows selected.
(1). 從上面的查詢結果可知，當前表TRADE_CLIENT_TBL上含有4個索引，應該來說該表索引存在一定冗余。
(2). 大多數情況下，單表上6-7個索引是比較理想的。過多的索引導致過大的資源開銷，以及降低DML性能。
2. 獲取指定schema或表上的索引質量信息報告
SQL> @/home/oracle/sql/idx_quality.sql
Enter value for input_owner: SH
Enter value for input_tbname: SALES
Table Table Index Data Blks Leaf Blks Clust Index
Table 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
Index
Table 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 0
SALES 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 8
9 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
具體問題具體解決
首先從業務角度去分析這500W的數據查詢邏輯，往往能從業務上發掘出很大的優化空間
如果是查詢性能，在技術上，可以用索引，索引跟主鍵是沒有必然關系的，沒有主鍵，照樣可以創建索引。索引是用來優化查詢性能的，不是用來做業務完整性等約束的
如果是因為主鍵問題帶來的數據不正確，就需要從業務根源上找原因了
普通索引時建立BTree，可以直接定位到你的檢索條件指定的結果。
如果是單條查詢，應該沒有太多能優化的吧？
但是如果牽涉到子查詢，應該是可優化的地方會增加，
我的考慮是在有索引的表檢索時，要盡量縮小檢索出來的子集（或者說把最大數據量查詢放在有索引的地方，提取出子集後再做進一步處理），這樣能比較大的提高效率，特別是在大數據量的時候。
具體問題具體分析吧。
