Oracle Index Quality Analysis

The index quality has a direct impact on the overall performance of the database. Good and high-quality indexes increase the database performance by an order of magnitude, while inefficient and redundant indexes make the database performance slow, even with high-end hardware configuration. Therefore, the index must be tested and considered repeatedly at the beginning of the design. For databases already in the production environment, we can also obtain the index quality by querying the relevant data dictionary. Through this analysis, we can guide how to improve the index performance. The following is a demonstration and basic guiding principles for index creation. Finally, an index quality analysis script is provided.

1. View index quality

-- Report gx_adm @ CABO3> @ idx_qualityEnter value for input_owner: GX_ADMEnter value for input_tbname: CLIENT_TRADE_TBL --> If the specific Table name is omitted, the Index Quality Report of the entire schema is output. Table Index Data Blks Leaf Blks Clust IndexTable Rows Blocks Index Size MB per Key Factor Quality ------------------------- ------------------------------------------------------------------------------------------- ------ CLIENT_TRADE_TBL 6,318,035 278488 limit 62 312 13 171,017 5-Excellent limit 62 318 13 174,599 5-Excellent limit 83 238 8 288,678 5-Excellent limit 144 13 249 5-Excellent I _TDCL_ARC_TRADE_DATE 310,974 14 337,097 5-Excellent PK_CLIENT_TRADE_TBL 200 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 765,603 2-Good I _TDCL_ARC_CONTRACT_NUM 72 1 834,491 2-Good runtime 61 299 5 380,699 1-Poor I _TDCL_ARC_ACC_NUM 184 624 3 3,899,446 1-Poor runtime 176 218 1 4,348,804 1- poor I _TDCL_ARC_INSTRU_ID 120 2,667 8 4,273,038 1-Poor -- from the index quality output from a single table, four Indexes at the Poor level appear, that is, these indexes have large clustering factors, almost similar to the rows on the table. The quality of these indexes should also be determined based on the index usage frequency. for clustering factors, you can only reorganize the table data, and Adjust the order of the corresponding index columns to improve -- query the information about the index columns on a single table. gx_adm @ CABO3> @ idx_infoEnter value for owner: GX_ADMEnter value for table_name: CLIENT_TRADE_TBLTABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD certificate -------------------- -------- ----------------- ---- CLIENT_TRADE_TBL certificate ACC_NUM 1 valid normal asc limit CANCEL_DATE 1 VALI D normal asc limit CONTRACT_NUM 1 valid normal asc limit GRP_REF_ID 1 valid normal asc limit INPUT_DATE 1 valid normal asc limit INSTRU_ID 1 valid normal asc limit STOCK_CD 1 valid normal asc limit PL_CD 2 valid normal asc limit SETTLED_DATE 1 valid normal asc I _TDCL_ARC_STL_DATE_CASH STL_DAT E_CASH 1 valid normal asc region STL_DATE_STOCK 1 valid normal asc region TRADE_DATE 1 valid normal asc region BUSINESS_DATE 1 valid normal asc PK_CLIENT_TRADE_TBL REF_ID 2 valid normal asc region REF_ID 1 valid normal asc -- result from the above yes, the current table TRADE_CLIENT_TBL contains 13 indexes. The indexes of this table are redundant. -- In most cases, 6-7 indexes are ideal for a single table. Excessive indexing results in excessive resource overhead and reduces DML performance.

-- Script name: idx_quality. SQL -- Author: Leshami -- Blog: http://blog.csdn.net/leshami -- Index quality retrievalSET LINESIZE 145 set pagesize 1000 set verify offclear limit breaksbreak on table_name ON num_rows ON blocksCOLUMN 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 names FORMAT 999G990 HEADING 'data Blks | per key' COLUMN names 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 tableset linesize 180col cl_nam format a20col table_name format a25col cl_pos format 9col idx_typ format a15SELECT B. table_name,. index_name,. column_name cl_nam,. column_position cl_pos, B. status, B. index_type idx_typ,. descend dscdFROM dba_ind_columns a, dba_indexes bWHERE. index_name = B. index_name AND owner = upper ('& owner') AND. table_name LIKE upper ('% & table_name %') order by 2, 4; 4. References