当前位置: 首页 > 编程笔记 >

Oracle索引质量介绍和分析脚本分享

夏理
2023-03-14
本文向大家介绍Oracle索引质量介绍和分析脚本分享,包括了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;

 类似资料:
  • 本文向大家介绍Oracle 分区索引介绍和实例演示,包括了Oracle 分区索引介绍和实例演示的使用技巧和注意事项,需要的朋友参考一下 分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长,普通的堆表需要转换到分区表,其索引呢,则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断,在获取所需数据时,只需要访问更小的索引片断(块)即可实现。同时把分区放在

  • 本文向大家介绍Oracle重建索引Shell脚本、SQL脚本分享,包括了Oracle重建索引Shell脚本、SQL脚本分享的使用技巧和注意事项,需要的朋友参考一下 索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的DML的情形下会产生相应的碎片,以及B树高度会发生相应变化,因此可以对这些变化较大的索引进

  • 本文向大家介绍Oracle 监控索引使用率脚本分享,包括了Oracle 监控索引使用率脚本分享的使用技巧和注意事项,需要的朋友参考一下 Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管如此,该方式仅提供的是索引是否被使用。索引被使用的频率未能得以体现。下面的脚本将得到索引的使用率,可以很好的度量

  • 本文向大家介绍oracle索引介绍(图文详解),包括了oracle索引介绍(图文详解)的使用技巧和注意事项,需要的朋友参考一下 对于数据库来说,索引是一个必选项,但对于现在的各种大型数据库来说,索引可以大大提高数据库的性能,以至于它变成了数据库不可缺少的一部分。 索引分类: 逻辑分类 single column or concatenated     对一列或多列建所引 unique or non

  • 一、创建索引: 在SQLite中,创建索引的SQL语法和其他大多数关系型数据库基本相同,因为这里也仅仅是给出示例用法: sqlite> CREATE TABLE testtable (first_col integer,second_col integer); --创建最简单的索引,该索引基于某个表的一个字段。 sqlite> CREATE INDEX testtable_idx ON test

  • 本文向大家介绍C#索引器介绍,包括了C#索引器介绍的使用技巧和注意事项,需要的朋友参考一下 索引器是一种特殊的类成员,它能够让对象以类似数组的方式来存取,使程序看起来更为直观,更容易编写。 1、索引器的定义 C#中的类成员可以是任意类型,包括数组和集合。当一个类包含了数组和集合成员时,索引器将大大简化对数组或集合成员的存取操作。 定义索引器的方式与定义属性有些类似,其一般形式如下: 修饰符包括 p