Oracle 监控索引使用率脚本分享(2)-木庄网络博客

当前第2页返回上一页

a、上面的结果列出了当前数据库中schema为GOEX_ADMIN且索引大小大于100MB的索引的使用频率。
b、由于当前的数据库为标准版，没有分区表功能，所以可以看到很多arc结尾的表，且索引很大，如ACC_POS_STOCK_TBL_ARC上索引达到19G。
c、表ACC_POS_CASH_PL_TBL_ARC上的主键PK_ACC_POS_CASH_PL_ARCH_TBL上范围扫描最多，总计被使用次数为112次。
d、对于上述列出的被使用的次数为0的那些索引，应考虑索引的设置是否合理。
e、过大的索引应考虑能否使用索引压缩。
f、最后列出的是报告的schema名称以及索引大小的过滤条件、索引被收集的日期。注，索引列的大小sum求和有些不准确。

3、获得索引使用频率脚本

--该脚本作者为Damir Vadas，感谢Damir Vadas的贡献
robin@SZDB:~/dba_scripts/custom/sql> more idx_usage_detail.sql
/* ---------------------------------------------------------------------------
 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

4、补充说明
脚本使用了2个替代变量，一个是schema，一个是索引的大小。缺省情况下，对于那些较小的索引以及仅仅运行一至两次的sql语句的历史执行计划不会被收集到DBA_HIST_SQL_PLAN。因此执行脚本时索引大小输入的建议值是100。如果需要收集所有的历史sql执行计划来判断索引是否被使用，需要修改statistics_level为all或者修改snapshot的收集策略。收集策略对系统性能有一定的影响，以及耗用大量磁盘空间，因此Prod环境应慎用(UAT和DEV则无妨)。

标签：Oracle

返回前面的内容