Oracle Index Quality Introduction and analysis script sharing _oracle

The index quality has a direct impact on the overall performance of the database. A good quality index allows database performance to be upgraded at a quantitative level, while inefficient, redundant indexes make database performance slow, even with high-end hardware configurations. Therefore, the index in the beginning of design needs to undergo repeated testing and consideration. For databases already in production environments, we can also query the relevant data dictionaries to get the quality of the index, and this analysis will guide how to improve the performance of the index. The following are the basic guidelines for demo and index creation, and the index quality analysis script is given at the end.

1. View Index Quality

--Gets the index quality information report on the specified schema or table gx_adm@cabo3> @idx_quality enter value for Input_owner:gx_adm enter value for Input_tbname: Client_trade_tbl--> If we omit a specific table name, the index quality report for the entire schema is output table table index Data blks Leaf B LKS clust index Table Rows Blocks Index Size MB/key per key Factor Quality--------- ---------------- ------------ ---------- ------------------------- ------- --------- --------- ------------ -----------
                         --Client_trade_tbl 6,318,035 278488 I_tdcl_arc_stl_date_stock 312 171,017 I_tdcl_arc_stl_date_cash 318 174,599 5-excellent I_tdcl_arc_cancel _date 238 8 288,678 5-excellent i_tdcl_arc_input_date 144 249 13 310,
                         974 5-excellent i_tdcl_arc_trade_date 144 MB 337,097 5-excellent Pk_client_trade_tbL 1 1 798,216 2-good i_tdcl_arc_grp_ref_id 144 1 1 811,468 2-goo D uni_tdcl_arc_ref_id 136 1 1 765,603 2-good I_tdcl_arc_c Ontract_num 1 1 834,491 2-good i_tdcl_arc_settled_date 61 299 5 380,6 1-poor I_tdcl_arc_acc_num 184 624 3 3,899,446 1-poor i_t     DCL_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--The quality of the index output from the above table shows that there are 4 indexes at the Poor level, which means that these indexes have a larger clustering factor, almost as close to the rows on the table-- The quality of these indexes should also be considered in conjunction with the frequency of use of the index--for clustering factors, only by organizing the data on the table and by adjusting the order of the corresponding indexed columns--Information about indexed columns on a single table Gx_adm@cabo3&gt ;           @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 Canc
             El_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_sto CK 1 VALID NORMAL ASC
             I_tdcl_arc_trade_date trade_date 1 VALID NORMAL ASC pk_client_trade_tbl busi
             Ness_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--from the above query results, the current table TRADE_CLIENT_TBL contains 13
Index, there should be a certain redundancy in the table index. In most cases, 6-7 indexes on a single table are ideal.

 Too many indexes cause too much resource overhead and degrade DML performance.

2, the basic Guiding principles of Index creation

The creation of indexes should follow the principle of fine and few
Collects various combinations of all queries on the table, finds columns with the best degree of dispersion (or primary key columns, and so on) to create a single index
To create a composite index for a column that is frequently read and lacks a relatively ideal discrete value
For a composite index, you should consider the following factors to make a reasonable indexed column order, the following priority from high to low as the leading column of the index, the second column, and so on
How often the column is used
Does the column often use "=" as a common query condition
The degree of dispersion on a column
How often the grouped columns are sorted
Which columns will be added as attachment columns

3. Index Quality Analysis Script

--script name:idx_quality.sql--author:leshami--blog:http://blog.csdn.net/leshami--index quality retrieval SET L Inesize 145 Set PAGESIZE 1000 SET VERIFY off clear computes clear BREAKS break on table_name on num_rows on blocks COLU MN owner FORMAT A14 HEADING ' Index owner ' column table_name format a25 HEADING ' table ' column index_name format A25 G ' 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 99 9g990 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/1
    048576 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 Sta TS ' When NVL (t.num_rows, 0) = 0 THEN ' 0-no Stats ' when (ROUND (i.clustering_factor/t.num_rows)) <
      6 THEN ' 5-excellent ' When (ROUND (i.clustering_factor/t.num_rows)) BETWEEN 7 and THEN ' 4-very good ' When (ROUND (i.clustering_factor/t.num_rows)) BETWEEN THEN ' 2-good ' When (ROUND tor/t.num_rows)) BETWEEN THEN ' 2-fair ' ELSE ' 1-poor ' End index_quality from Dba_index
     Es 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 = up (' &input_owner ') and t.table_name like UPPER ('%&input_tbname% ') ORDER by TABLE_NAME, num_rows, b Locks, 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 form
      At 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_column s A, dba_indexes b WHERE a.index_name = b.index_name and owner = Upper (' &owner ') and a.table_name like UPP

 ER ('%&table_name% ') Order by 2, 4;