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://www.linuxidc.com

--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;