Oracle 索引质量分析

http://blog.csdn.net/leshami/article/details/23687137 

索引质量的高低对数据库整体性能有着直接的影响。良好高质量的索引使得数据库性能得以数量级别的提升,而低效冗余的索引则使得数据库性能缓慢如牛,即便是使用高档的硬件配置。因此对于索引在设计之初需要经过反复的测试与考量。那对于已经置于生产环境中的数据库,我们也可以通过查询相关数据字典得到索引的质量的高低,通过这个分析来指导如何改善索引的性能。下面给出了演示以及索引创建的基本指导原则,最后给出了索引质量分析脚本。

 

1、查看索引质量

 1 --获取指定schema或表上的索引质量信息报告
 2 gx_adm@CABO3> @idx_quality
 3 Enter value for input_owner: GX_ADM
 4 Enter value for input_tbname: CLIENT_TRADE_TBL  -->如果我们省略具体的表名则会输出整个schema的索引质量报告
 5 
 6                                  Table      Table                             Index Data Blks Leaf Blks        Clust Index
 7 Table                             Rows     Blocks Index                     Size MB   per Key   per Key       Factor Quality
 8 ------------------------- ------------ ---------- ------------------------- ------- --------- --------- ------------ -------------
 9 CLIENT_TRADE_TBL             6,318,035     278488 I_TDCL_ARC_STL_DATE_STOCK      62       312        13      171,017 5-Excellent
10                                                   I_TDCL_ARC_STL_DATE_CASH       62       318        13      174,599 5-Excellent
11                                                   I_TDCL_ARC_CANCEL_DATE         83       238         8      288,678 5-Excellent
12                                                   I_TDCL_ARC_INPUT_DATE         144       249        13      310,974 5-Excellent
13                                                   I_TDCL_ARC_TRADE_DATE         144       269        14      337,097 5-Excellent
14                                                   PK_CLIENT_TRADE_TBL           200         1         1      798,216 2-Good
15                                                   I_TDCL_ARC_GRP_REF_ID         144         1         1      811,468 2-Good
16                                                   UNI_TDCL_ARC_REF_ID           136         1         1      765,603 2-Good
17                                                   I_TDCL_ARC_CONTRACT_NUM        72         1         1      834,491 2-Good
18                                                   I_TDCL_ARC_SETTLED_DATE        61       299         5      380,699 1-Poor
19                                                   I_TDCL_ARC_ACC_NUM            184       624         3    3,899,446 1-Poor
20                                                   I_TDCL_ARC_PL_STK             176       218         1    4,348,804 1-Poor
21                                                   I_TDCL_ARC_INSTRU_ID          120     2,667         8    4,273,038 1-Poor
22 
23 --从上面的单表输出的索引质量可知,出现了4个处于Poor级别的索引,也就是说这些个索引具有较大的聚簇因子,几乎接近于表上的行了
24 --对于这几个索引的质量还应结合该索引的使用频率来考量该索引存在的必要性
25 --对于聚簇因子,只能通过重新组织表上的数据来,以及调整相应索引列的顺序得以改善
26              
27 --查询单表上索引列的相关信息             
28 gx_adm@CABO3> @idx_info
29 Enter value for owner: GX_ADM
30 Enter value for table_name: CLIENT_TRADE_TBL
31 
32 TABLE_NAME                INDEX_NAME                     CL_NAM               CL_POS STATUS   IDX_TYP         DSCD
33 ------------------------- ------------------------------ -------------------- ------ -------- --------------- ----
34 CLIENT_TRADE_TBL          I_TDCL_ARC_ACC_NUM           ACC_NUM                   1 VALID    NORMAL          ASC
35                           I_TDCL_ARC_CANCEL_DATE       CANCEL_DATE               1 VALID    NORMAL          ASC
36                           I_TDCL_ARC_CONTRACT_NUM      CONTRACT_NUM              1 VALID    NORMAL          ASC
37                           I_TDCL_ARC_GRP_REF_ID        GRP_REF_ID                1 VALID    NORMAL          ASC
38                           I_TDCL_ARC_INPUT_DATE        INPUT_DATE                1 VALID    NORMAL          ASC
39                           I_TDCL_ARC_INSTRU_ID         INSTRU_ID                 1 VALID    NORMAL          ASC
40                           I_TDCL_ARC_PL_STK            STOCK_CD                  1 VALID    NORMAL          ASC
41                           I_TDCL_ARC_PL_STK            PL_CD                     2 VALID    NORMAL          ASC
42                           I_TDCL_ARC_SETTLED_DATE      SETTLED_DATE              1 VALID    NORMAL          ASC
43                           I_TDCL_ARC_STL_DATE_CASH     STL_DATE_CASH             1 VALID    NORMAL          ASC
44                           I_TDCL_ARC_STL_DATE_STOCK    STL_DATE_STOCK            1 VALID    NORMAL          ASC
45                           I_TDCL_ARC_TRADE_DATE        TRADE_DATE                1 VALID    NORMAL          ASC
46                           PK_CLIENT_TRADE_TBL          BUSINESS_DATE             1 VALID    NORMAL          ASC
47                           PK_CLIENT_TRADE_TBL          REF_ID                    2 VALID    NORMAL          ASC
48                           UNI_TDCL_ARC_REF_ID          REF_ID                    1 VALID    NORMAL          ASC
49                         
50 --从上面的查询结果可知,当前表TRADE_CLIENT_TBL上含有13个索引,应该来说该表索引存在一定冗余。
51 --大多数情况下,单表上6-7个索引是比较理想的。过多的索引导致过大的资源开销,以及降低DML性能。

2、索引创建的基本指导原则    

索引的创建应遵循精而少的原则     

收集表上所有查询的各种不同组合,找出具有最佳离散度的列(或主键列等)创建单索引     

对于频繁读取而缺乏比较理想离散值的列为其创建组合索引     

对于组合索引应考虑下列因素来制定合理的索引列顺序,以下优先级别由高到低来作为索引的前导列,第二列等等           

  列被使用的频率           

  该列是否经常使用“ = ”作为常用查询条件           

  列上的离散度           

  组合列经常按何种顺序排序           

  哪些列会作为附件性列被添加 

3、索引质量分析脚本

 

 1 --script name: idx_quality.sql     --Author : Leshami  --Blog: http://blog.csdn.net/leshami 
 2 --index quality retrieval
 3 SET LINESIZE 145
 4 SET PAGESIZE 1000
 5 SET VERIFY OFF
 6 
 7 CLEAR COMPUTES
 8 CLEAR BREAKS
 9 
10 BREAK ON table_name ON num_rows ON blocks
11 
12 COLUMN owner FORMAT a14 HEADING Index owner
13 COLUMN table_name FORMAT a25 HEADING Table
14 COLUMN index_name FORMAT a25 HEADING Index
15 COLUMN num_rows FORMAT 999G999G990 HEADING Table|Rows
16 COLUMN MB FORMAT 9G990 HEADING Index|Size MB
17 COLUMN blocks HEADING Table|Blocks
18 COLUMN num_blocks FORMAT 9G990 HEADING Data|Blocks
19 COLUMN avg_data_blocks_per_key FORMAT 999G990 HEADING Data Blks|per Key
20 COLUMN avg_leaf_blocks_per_key FORMAT 999G990 HEADING Leaf Blks|per Key
21 COLUMN clustering_factor FORMAT 999G999G990 HEADING Clust|Factor
22 COLUMN Index_Quality FORMAT A13 HEADING Index|Quality
23 
24 --SPOOL index_quality
25 
26   SELECT i.table_name,
27          t.num_rows,
28          t.blocks,
29          i.index_name,
30          o.bytes / 1048576 mb,
31          i.avg_data_blocks_per_key,
32          i.avg_leaf_blocks_per_key,
33          i.clustering_factor,
34          CASE
35             WHEN NVL (i.clustering_factor, 0) = 0 THEN 0-No Stats
36             WHEN NVL (t.num_rows, 0) = 0 THEN 0-No Stats
37             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) < 6 THEN 5-Excellent
38             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 7 AND 11 THEN 4-Very Good
39             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 12 AND 15 THEN 2-Good
40             WHEN (ROUND (i.clustering_factor / t.num_rows * 100)) BETWEEN 16 AND 25 THEN 2-Fair
41             ELSE 1-Poor
42          END
43             index_quality
44     FROM dba_indexes i, dba_segments o, dba_tables t
45    WHERE 
46      --    i.index_name LIKE UPPER (‘%&&1%‘) AND
47          i.owner = t.owner
48          AND i.table_name = t.table_name
49          AND i.owner = o.owner
50          AND i.index_name = o.segment_name
51          AND t.owner = UPPER(&input_owner)
52          AND t.table_name LIKE UPPER(%&input_tbname%)
53 ORDER BY table_name,
54          num_rows,
55          blocks,
56          index_quality DESC;
57 
58 --SPOOL OFF;
59 
60 ===========================================================================================
61 --script name: idx_info.sql 
62 --get the index column information by specified table
63 set linesize 180
64 col cl_nam format a20
65 col table_name format a25
66 col cl_pos format 9
67 col idx_typ format a15
68 SELECT b.table_name,
69            a.index_name,
70            a.column_name     cl_nam,
71            a.column_position cl_pos,
72            b.status,
73            b.index_type      idx_typ,
74            a.descend         dscd
75 FROM   dba_ind_columns a, dba_indexes b
76 WHERE  a.index_name = b.index_name
77            AND owner = upper(&owner)
78            AND a.table_name LIKE upper(%&table_name%)
79 ORDER  BY 2, 4;

4、相关参考    

Oracle 聚簇因子(Clustering factor)     

Oracle 索引监控(monitor index)    

Oracle 索引监控与外键索引     

收集统计信息导致索引被监控     

Oracle 监控索引的使用率    

NULL 值与索引(一)    

NULL 值与索引(二)    

函数使得索引列失效

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。