索引的使用率

今天面试的时候，面试官问我有没有监控过索引的使用率，这个问题我没有回答出来，平时都没有做过这方面的东西，网上查了一下，在这里整理一下。如何查看索引是否被使用，如何使用。

其中用来监控使用的代码如下：

<pre class="sql" name="code"><span style="font-size:14px;">--该脚本作者为Damir Vadas，感谢Damir Vadas的贡献
/* ---------------------------------------------------------------------------
 CR/TR#  :
 Purpose : Shows index usage by execution (find 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 Vadas
                      fixed bug in query
                                 
--------------------------------------------------------------------------- */

set linesize 140
set pagesize 160
 
clear breaks
clear computes
 
break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_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 INDEX_NAME for a30 heading 'Index name'
col INDEX_TYPE for a15 heading 'Index type'
col INDEX_OPERATION for a21 Heading 'Index 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.bytes) / 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
                HAVING 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                               INDEX_TYPE,
               A_MB                                       MB,
               DECODE (OPTIONS, null, '       -',OPTIONS) INDEX_OPERATION,
               COUNT(OPERATION)                           NR_EXEC
         FROM  Q,
               DBA_HIST_SQL_PLAN d
         WHERE
               D.OBJECT_OWNER(+)= q.A_OWNER 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

</span>

监控scott模式下的索引使用情况：

由于是自己机器上安装的库，所以数据比较少。

其中执行次数为0的索引可以考虑删除。