Oracle Monitor Index usage script sharing _oracle

Oracle provides index monitoring features to determine whether an index is in use. In Oracle 10g, collecting statistics will allow indexes to be monitored, and this phenomenon no longer exists in Oracle 11g. However, this method only provides that the index is used. The frequency with which the index was used was not reflected. The following script will get index usage, which can be a good measure of how the index is used and, based on this value, to determine whether the current indexes can be removed or improved.

1. Index Usage Frequency report

--Operating environment sql> SELECT * from V$version where rownum<2; BANNER----------------------------------------------------------------Oracle Database 10g release 10.2.0.3.0-64bit
                                         Production--Gets the current database index usage frequency sql> @idx_usage_detail. SQL Enter value for 1:go_admin enter value for 2:100 Index Table name Index name index type Size MB Index operation Exec Utions---------------------------------------------------------------------------------------------------------
                                            ---------Acc_pos_cash_pl_tbl_arc pk_acc_pos_cash_pl_arch_tbl NORMAL 3,328.00 RANGE SCAN 99          SAMPLE FAST full SCAN 8 UNIQUE SCAN 3 SKIP SCAN 2 ****************************** *********************                                  ---------------------sum   13,312.00 112 Acc_pos_cash_tbl_arc pk_acc_pos_cash_arch_tbl NORMAL 2,560.00 RANGE SCAN UNIQUE SCAN S Ample FAST full SCAN SKIP SCAN 1 **************************** * * ****************************** ************---------------------sum 1 
                                            0,240.00 195 acc_pos_hist_tbl acc_hist_trans_date_idx NORMAL 384.00 RANGE SCAN 917 SKIP SCAN 210 SAMPLE FAS       T full SCAN 4 FAST full SCAN 1 pk_acc_pos_hist_tbl
                NORMAL 192.00 UNIQUE SCAN 7 SAMPLE FAST full SCAN 3 Trans_num_IDX NORMAL 232.00 RANGE SCAN-SAMPLE FAST full SCAN 3 FAST full SCAN 1 ****************************** ******************              ---------------------sum 2,616.00
                               1,187 acc_pos_int_tbl acc_pos_int_10dig_idx function-2,622.00 RANGE SCAN 59
                                            BASED NORMAL SAMPLE FAST full SCAN 4          FAST full SCAN 2 pk_acc_pos_int_tbl NORMAL 2,496.00 RANGE SCAN FAST Full SCAN UN
                                            IQUE SCAN SKIP SCAN 13 SAMPLE FAST Full SCAN 1 ****************************** ****************************** ************-------------------- -Sum 20,346.00 211 Acc_pos_stock_tbl_arc PK_ACC_POS_STOCK_ARCH_TB
                                            L NORMAL 18,977.00 RANGE SCAN 177 SAMPLE FAST full SCAN 10           UNIQUE SCAN 4 SKIP SCAN 3 ****************************** ****************************** ************---------------------su      M 75,908.00 194 Stk_tbl_arc PK_STK_ARCH_TBL NORMAL
                                            920.00 RANGE SCAN 126 UNIQUE SCAN 38 SKIP SCAN SAMPLE FAST Full SCAN 2 * * * ************************* * ****************************** ************---------------------sum 3,680.00 183 stk_tbl_log pk_stk_tbl_log NORMAL 480.00 UNIQUE SCAN 56                                       ************---------------------sum 480.00 Trade_broker_chrg_tbl_arc Pk_trade_broker_chrg_tbl_arc NORMAL 12 8.00-0 uni_tdbk_chrg_arc NORMAL 104.00 RANGE SCAN 283 ****************                                       ************---------------------sum             232.00 283 Trade_broker_journal_tbl_arc idx_tdbk_jrnl_arc_entry_dt NORMAL 168.00- 0 idx_tdbk_jrnl_arc_instru_id NORMAL 144.00 full SCAN 1 idx_td BK_JRNL_ARC_STOCK_CD NOrmal 144.00 full SCAN 1 idx_tdbk_jrnl_arc_traded_price NORMAL 144.00 full SCAN 1 Pk_trade_broker_journal_arc NORMAL 200.00-0 ****************************** * * * ---------------------sum 800.0 
                0 3 Trade_client_chrg_tbl_arc idx_tdcl_chrg_arc_grp_ref_id NORMAL 704.00 RANGE SCAN 3,537
                                            Pk_trade_client_chrg_tbl_arc NORMAL 1,539.00 RANGE SCAN 24 SAMPLE FAST full SCAN 2 uni_tdcl_chrg_arc NORMAL 1,216.00 RANGE SCAN 1,1  FAST Full SCAN 3 SAMPLE FAST full SCAN 2 ****************************** ****************************** ************-------------            --------Sum                          7,430.00 4,671 Trade_client_dtl_tbl_arc idx_tdcl_dtl_arc_action_n_stus NORMAL
                312.00-0 idx_tdcl_dtl_arc_act_td_price NORMAL 184.00 full SCAN 1
                                            idx_tdcl_dtl_arc_ref_id NORMAL 344.00 RANGE SCAN 4,623 FAST full SCAN 1 full SCAN 1 Idx_tdcl_dt    L_arc_traded_price normal 184.00-0 Pk_trade_client_dtl_tbl_arc normal 432.00 -0 uni_tdcl_dtl_arc_trade_dtl_id NORMAL 272.00-0 ***********************                                      ************---------------------sum          2,416.00 4,626 trade_client_tbl_arc idx_tdcl_arc_acc_num NORMAL 152.00 RANGE SCAN
                534idx_tdcl_arc_grp_ref_id NORMAL 120.00 RANGE SCAN # FAST ful L SCAN 1 idx_tdcl_arc_input_date NORMAL 120.00 RANGE SCAN 7,231 ID           X_TDCL_ARC_PL_STK NORMAL 144.00 SKIP SCAN 156 RANGE SCAN 3 full SCAN 1 idx_tdcl_arc_trade_date NOR          MAL 120.00 range SCAN 12,778 pk_trade_client_tbl_arc NORMAL 160.00 RANGE SCAN
                                            Notoginseng uni_tdcl_arc_ref_id NORMAL 112.00 UNIQUE SCAN 157 Fast Full SCAN 8 SAMPLE fast full SCAN 1 ***************                                      ************---------------------sum   1,560.00           21,457--author:robinson--blog:http://blog.csdn.net/robinson_0612 "showed only indexes in Go_admin Sche

 Ma whose size > MB in period: "30.01.2013-07.04.2013

2. Result Analysis and suggestion

A, the results above list the frequency with which the schema in the current database is goex_admin and the index size is greater than 100MB.
b, because the current database is Standard Edition, there is no partition table function, so you can see many of the end of the arc of the table, and the index is very large, such as the index on the Acc_pos_stock_tbl_arc reached 19G.
C, the table Acc_pos_cash_pl_tbl_arc on the primary key PK_ACC_POS_CASH_PL_ARCH_TBL scan the largest range, the total was used 112 times.
D, for those indexes listed above that have been used for 0, consider whether the index setting is reasonable.
E, too large indexes should consider the ability to use index compression.
The last list is the schema name of the report and the filtering criteria for the index size, and the date the index was collected. Note, the size of the indexed column sum sum is somewhat inaccurate.

3. Get Index usage Frequency script

--The script author for Damir Vadas, thanks to Damir Vadas's contribution robin@szdb:~/dba_scripts/custom/sql> more Idx_usage_detail.sql/*-----------  ----------------------------------------------------------------cr/tr#: Purpose:shows index usage by execution
 Problematic indexes) date:22.01.2008. Author:damir Vadas, damir.vadas@gmail.com remarks:run as privileged user must have AWR run because SQL joins Data from there works on 10g > @index_usage SCHEMA min_index_size Changes (DD. Mm. YYYY, Name, cr/tr#): 25.11.2010, Damir Vadas added index size as parameter 30.11.2010, Damir Vad As fixed bug in query----------------------------------------------------------------------- ----*/Set Linesize 140 set pagesize 160 clear breaks clear computes break on table_name Skip 2 on Index_name on IND Ex_type on MB compute sum of NR_EXEC on table_name skip 2 compute sum of MB on table_name SKIP 2 SET TIMI OFF set linesize 140 set pagesize 10000 set verify off Col OWNER noprint Col table_name for A30 heading ' TABLE NAME ' col in Dex_name for A30 heading ' index NAME ' col index_type for A15 heading ' index TYPE ' col index_operation for A21 ' heading Ex operation ' Col nr_exec for 9g999g990 heading ' executions ' col MB for 999g990d90 heading ' index|        Size MB ' justify right with Q as (SELECT S.owner A_owner, table_name A_table_name, Index_name a_index_name, Index_type a_index_type, SUM (s.b
          ytes)/1048576 a_mb from Dba_segments S, dba_indexes I WHERE s.owner = ' &&1 ' and I.owner = ' &&1 ' and index_name = Segment_name GROUP by S.owner, TABLE_NAME, index_name
        , Index_type has SUM (s.bytes) > 1048576 * &&2) SELECT/*+ no_query_transformation (S) * * A_owner OWNER, a_tAble_name table_name, A_index_name index_name, A_index_type I Ndex_type, A_mb MB, DECODE (options, NULL, '-', options) index_operation, CO UNT (OPERATION) nr_exec from Q, Dba_hist_sql_plan D WHERE d.object_owner (+) = Q.a_own 
        ER and d.object_name (+) = Q.a_index_name GROUP by A_owner, A_table_name, A_index_name,
        A_index_type, A_MB, DECODE (options, NULL, '-', options) Order by A_owner,

A_table_name, A_index_name, A_index_type, A_mb DESC, nr_exec DESC;
PROMPT "showed only indexes in &&1 schema whose size > &&2 MB in Period:" SET head off; Select To_char (min (begin_interval_time), ' DD. Mm. YYYY ') | |
    '-' || To_char (Max (end_interval_time), ' DD. Mm.
 
YYYY ') from Dba_hist_snapshot;

 Set head on set TIMI on

4, supplemental description
    script uses 2 substitution variables, one is schema, one is the size of the index. By default, historical execution plans for smaller indexes and SQL statements that run only one to two times are not collected to Dba_hist_sql_plan. So the recommended value for index size input when executing scripts is 100. If you need to collect all the history SQL execution plans to determine whether the index is being used, you need to modify statistics_level to all or modify the snapshot collection policy. The collection strategy has a certain impact on system performance and consumes a lot of disk space, so PROD environments should be used with caution (UAT and Dev are ok).