Oracle 索引质量分析
浏览数：63 /
时间：2015年06月12日
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、相关参考
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
