If index monitor is not enabled, check whether the index has been used.

If you do not enable index monitor, you can determine whether the index has been used. If you want to know whether an index has been used, the traditional method is to enable index monitor. However, this method has two shortcomings: (1) it will bring about a small amount of performance load (2) it can only be used after monitoring is enabled and you have to observe for a while, how long does it take to determine whether the data can be obtained? However, it is often necessary to confirm whether the index is used out of date. In the hope that the data can be obtained immediately, a method is suddenly created today, does the "NAME" column of the access object in the SQL Execution Plan also contain the accessed object information, which also contains the accessed index NAME. Check the SQL Execution Plan information to see if the index is used. An execution plan is generated if the SQL statement is well parsed during execution. Even soft parsing, the execution plan also exists in the memory, even if the execution plan has been flushed out of the shared_pool, as long as there is a shared_pool when a snapshot is generated, you can also find the historical execution plan from the AWR data. With this idea, it is easy to do. The following two methods can be used to check whether the specified index has been used before: 1. view the execution plan records that have accessed the specified index in the shared_pool from the v $ SQL _plan view.

    SQL>select sql_id,plan_hash_value,timestamp,object_namefrom v$sql_plan whereobject_name='INDEX_LITEST2_OBJECTID'sql_idplan_hash_valueoptionstimestampobject_name3j6xbabaz36v92954962778RANGE SCAN2013/9/12 23:38INDEX_LITEST2_OBJECTIDdpaj071ypmj9j3938988231RANGE SCAN2013/9/12 23:38INDEX_LITEST2_OBJECTID

 

The preceding query results indicate that the index "INDEX_LITEST2_OBJECTID" is used in SQL statements with plan_hash_value 2954962778 and 3938988231 parsed. 2. view the execution plan records of the specified index from the AWR history data in the dba_hist_ SQL _plan view. If the records of the specified index cannot be found in the shared_pool, you can also find the records from the AWR history data, as shown below:

        SQL> select sql_id,plan_hash_value,operation,options,timestamp,object_namefrom dba_hist_sql_plan whereobject_name='IDX_INVOICE_001'orderby5 descsql_idplan_hash_valueoptionstimestampobject_namef5bnpqug8a13f1001597107RANGE SCAN2013/9/2 19:00IDX_INVOICE_001db0zvtnr5qugm1001597107RANGE SCAN2013/9/2 18:58IDX_INVOICE_001fy7r1q6bdpk0f1001597107RANGE SCAN2013/9/2 18:56IDX_INVOICE_0010n5b0jfdaukvq1001597107RANGE SCAN2013/9/2 18:54IDX_INVOICE_0016zpj6sdmmhh141001597107RANGE SCAN2013/9/2 18:48IDX_INVOICE_001gwcpdx6yafsm21001597107RANGE SCAN2013/9/2 18:42IDX_INVOICE_00154nfxwgjw95g41001597107RANGE SCAN2013/9/2 18:40IDX_INVOICE_00140797ymubw53n1001597107RANGE SCAN2013/9/2 18:35IDX_INVOICE_0017t7267bz2qvjy1001597107RANGE SCAN2013/9/2 18:26IDX_INVOICE_001

 

This method allows you to view the historical duration, which depends on the retention duration of your AWR historical data.