Oracle 索引质量分析

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

1、查看索引质量

--获取指定schema或表上的索引质量信息报告
gx_adm@CABO3> @idx_quality
Enter value for input_owner: GX_ADM
Enter value for input_tbname: CLIENT_TRADE_TBL  -->如果我们省略具体的表名则会输出整个schema的索引质量报告

                                 Table      Table                             Index Data Blks Leaf Blks        Clust Index
Table                             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_info
Enter value for owner: GX_ADM
Enter value for table_name: CLIENT_TRADE_TBL

TABLE_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 retrieval
SET LINESIZE 145
SET PAGESIZE 1000
SET VERIFY OFF

CLEAR COMPUTES
CLEAR BREAKS

BREAK ON table_name ON num_rows ON blocks

COLUMN 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 table
set linesize 180
col cl_nam format a20
col table_name format a25
col cl_pos format 9
col idx_typ format a15
SELECT 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         dscd
FROM   dba_ind_columns a, dba_indexes b
WHERE  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 值与索引(二)
  • 函数使得索引列失效

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

PL/SQL --> INSTEAD OF 触发器

INSTEAD OF 触发器常用于管理编写不可更新的视图,INSTEAD-OF触发器必须是行级的。

812
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

9172
来自专栏数据和云

利用分析函数改写范围判断自关联查询

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

1284
来自专栏眯眯眼猫头鹰的小树杈

猫头鹰的深夜翻译:如何优化MYSQL查询

索引除了能够确保唯一的标记一条记录,还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

922
来自专栏杨建荣的学习笔记

关于索引扫描的极速调优实战(第二篇)(r3笔记第82天)

在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我们大体介绍了下问题的情况,已经初步根据awr能...

3547
来自专栏Grace development

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下

1782
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

1653
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(二) (r7笔记第9天)

之前分享过一篇关于merge语句导致的CPU使用率过高优化的案例。http://blog.itpub.net/23718752/viewspace-181947...

3164
来自专栏landv

一、K3 WISE 开发插件《K3 WISE常用数据表整理》

4097
来自专栏跟着阿笨一起玩NET

SQL Server 2008中增强的汇总技巧

SQL Server 2008中对汇总有明显的增强,有点像Oracle的语法了。请看下面五个例子:

1323

扫码关注云+社区

领取腾讯云代金券