• 如何监控ORACLE索引使用与否

    在数据库管理与维护中，我们总会遇到一个问题：我们创建的索引是否会被某些SQL语句使用呢？换个通俗表达方式：我创建的索引是否是未使用的索引（unused Indexes），是否有价值呢？如果创建的某个索引是Unused Indexes，尤其是没有合理规划索引的系统或那些管理控制不规范的系统。有可能建立了N个索引，其实有些索引都是没有任何SQL会使用，那么此时这些多余的索引其实会带来两个问题：1：浪费存储空间，尤其是大表的索引，浪费的存储空间尤其可观； 2：加重DML操作（UPDATE、INSERT、DELETE）的开销。

    ORACLE其实提供了监控索引使用情况的功能。ALTER INDEX <index_name> MONITORING USAGE; 我们下面来测试验证一下吧。

    创建一个表TEST作为实验测试验证的样例

    CREATE TABLE TEST
    (
        ID    NUMBER(10),
        NAME  VARCHAR2(32)
    );
    CREATE INDEX IDX_TEST_ID ON TEST(ID);
     
    INSERT INTO TEST 
    SELECT 1001, 'Kerry' FROM DUAL UNION ALL
    SELECT 1002, 'Ken'   FROM DUAL UNION ALL
    SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
    SELECT 1004, 'Jack'  FROM DUAL;
    COMMIT;
     
    execute dbms_stats.gather_table_stats(ownname => 'ETL', tabname =>'TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

    启用对索引IDX_TEST_ID的监控

    ALTER INDEX IDX_TEST_ID MONITORING USAGE;

    此时观察V$OBJECT_USAGE表数据的变化，如下所示，MONITORIN字段值变为YES，表示索引IDX_TEST_ID已经被置于监控状态。USED字段为NO表示暂时没有SQL使用该索引

    SQL> COL INDEX_NAME FOR A20       
    SQL> COL TABLE_NAME FOR A10
    SQL> COL MONITORING FOR A10
    SQL> COL USED FOR A10
    SQL> COL START_MONITORING FOR A20
    SQL> COL END_MONITORING FOR A20
    SQL> SELECT * FROM V$OBJECT_USAGE;
     
    INDEX_NAME   TABLE_NAME MONITORING USED  START_MONITORING     END_MONITORING
    ------------ ---------- ---------- ---- -------------------- ----------------
    IDX_TEST_ID   TEST       YES        NO   11/28/2015 14:57:41

    此时我们执行下面SQL，因为此时使用全表扫描，那么索引IDX_TEST_ID依然没有被使用，此时可以查看V$OBJECT_USAGE进行验证。

    SQL> SET AUTOTRACE ON;
    SQL> SELECT * FROM TEST WHERE ID =1001;
     
            ID NAME
    ---------- --------------------------------
          1001 Kerry
     
     
    Execution Plan
    ----------------------------------------------------------
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     9 |     2 |
    |   1 |  TABLE ACCESS FULL| TEST |     1 |     9 |     2 |
    ----------------------------------------------------------
    Note
    -----
       - 'PLAN_TABLE' is old version
     
     
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            578  bytes sent via SQL*Net to client
            492  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

    clip_image001

    如下所示，此时索引IDX_TEST_ID依然没有被使用。

    clip_image002

    我们使用索引提示强制下面SQL使用索引IDX_TEST_ID

    SELECT /*+ INDEX(TEST IDX_TEST_ID) */* FROM TEST WHERE ID =1001;

    此时你就会发现USED的值变为了YES了。

    clip_image003

    ALTER INDEX IDX_TEST_ID NOMONITORING USAGE;

    执行上面命令后，在V$OBJECT_USAGE表中，就会更新表TEST记录的END_MONITORING、MONITORING的值。

    clip_image004

    如果你又启用监控索引使用情况，那么系统会更新START_MONITORING、END_MONITORING字段的值（END_MONITORING的值更新为NULL）。如果删除表

    TEST，此时你会发现V$OBJECT_USAGE对象中关于表TEST的记录也不见了。

    注意：SELECT * FROM V$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使sys、system用户也不能查看其它用户的信息。

    在测试过程中有个小疑问，就是在准备测试环境时，如果不对表收集统计信息的话，那么即使SQL走全表扫描，你依然发现V$OBJECT_USAGE中索引被标记使用了。如下所示

    DROP TABLE TEST PURGE;
     
    CREATE TABLE TEST
    (
        ID    NUMBER(10),
        NAME  VARCHAR2(32)
    );
    CREATE INDEX IDX_TEST_ID ON TEST(ID);
     
    INSERT INTO TEST 
    SELECT 1001, 'Kerry' FROM DUAL UNION ALL
    SELECT 1002, 'Ken'   FROM DUAL UNION ALL
    SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
    SELECT 1004, 'Jack'  FROM DUAL;
    COMMIT;
     
    ALTER INDEX IDX_TEST_ID MONITORING USAGE;
    SQL> SET AUTOTRACE ON;
    SQL> SELECT *  FROM TEST WHERE ID =1001;
     
            ID NAME
    ---------- --------------------------------
          1001 Kerry
     
     
    Execution Plan
    ----------------------------------------------------------
    ----------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost  |
    ----------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |    31 |     2 |
    |   1 |  TABLE ACCESS FULL| TEST |     1 |    31 |     2 |
    ----------------------------------------------------------
    Note
    -----
       - 'PLAN_TABLE' is old version
     
     
    Statistics
    ----------------------------------------------------------
              7  recursive calls
              0  db block gets
             10  consistent gets
              0  physical reads
              0  redo size
            578  bytes sent via SQL*Net to client
            492  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    SQL> SELECT * FROM V$OBJECT_USAGE;
     
    INDEX_NAME   TABLE_NAME MONITORING USED   START_MONITORING    END_MONITORING
    ------------ ---------- ---------- ------  -----------    -------------------
    IDX_TEST_ID   TEST       YES        YES   11/28/2015 15:11:46

    那么为什么呢？ 猜测是在解析生成执行计划时，用到了索引的一些信息，导致V$OBJECT_USAGE表中的字段USED被标记为YES。

    如果我们想在系统中监控所有的索引，那么我们可以通过下面脚本实现监控数据库所有的索引。注意我们要排除一些系统表的索引、以及LOB indexes。原因有下面两个：

    1：LOB indexes不能修改，否则会报ORA-22864错误（ORA-22864: cannot ALTER or DROP LOB indexes）。

    2：ORA-00701: object necessary for warmstarting database cannot be altered

    ORA-00701: object necessary for warmstarting database cannot be altered

    00701. 00000 - "object necessary for warmstarting database cannot be altered"

    *Cause: Attempt to alter or drop a database object (table, cluster, or

    index) which are needed for warmstarting the database.

    *Action: None.

    SET PAGES 999;
    SET HEADING OFF;
    SPOOL run_monitor.sql
     
    SELECT
       'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;'
    FROM
       DBA_INDEXES
    WHERE
       INDEX_TYPE != 'LOB' AND OWNER NOT IN  ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
    ;
     
    SPOOL OFF;
     
    @run_monitor.sql

    此时使用下面脚本就能查出那些索引是未使用索引，当然监控索引时长非常重要，太短的话有可能导致查询出来的数据有问题，一般建议监控一周后即可，OLAP系统则需要适当延长监控的时间。

     
    SELECT I.TABLE_OWNER,
           T.TABLE_NAME,
           I.INDEX_NAME,
           U.USED,
           U.START_MONITORING,
           U.END_MONITORING
    FROM USER_TABLES T
    INNER JOIN USER_INDEXES I
    ON T.TABLE_NAME = I.TABLE_NAME
    INNER JOIN V$OBJECT_USAGE U
    ON U.TABLE_NAME    = I.TABLE_NAME
    AND I.INDEX_NAME   = U.INDEX_NAME
    WHERE I.TABLE_OWNER=SYS_CONTEXT('USERENV','CURRENT_USER')

    另外，博客Oracle - Find unused Indexes中介绍了一个查找没有使用索引的SQL语句。如下所示statspack_unused_indexes.sql 

    col owner heading "Index Owner" format a30
    col index_name heading "Index Name" format a30
     
    set linesize 95 trimspool on pagesize 80
     
    select * 
    from
       (select 
          owner, 
          index_name
       from 
          dba_indexes di
       where
          di.index_type != 'LOB'
       and
          owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
    minus
    select 
       index_owner owner, 
       index_name
    from 
       dba_constraints dc
    where
       index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
    minus
    select
       p.object_owner owner,
       p.object_name  index_name
    from
       stats$snapshot       sn,
       stats$sql_plan       p,
       stats$sql_summary    st,
       stats$sql_plan_usage spu
    where
       st.sql_id = spu.sql_id 
    and 
       spu.plan_hash_value = p.plan_hash_value
    and
       st.hash_value = p.plan_hash_value
    and
       sn.snap_id = st.snap_id 
    and 
       sn.dbid = st.dbid 
    and 
       sn.instance_number = st.instance_number
    and
       sn.snap_id = spu.snap_id
    and 
       sn.dbid = spu.snap_id 
    and 
       sn.instance_number = spu.instance_number
    and
       sn.snap_id between &begin_snap and &end_snap
    and
       p.object_type = 'INDEX'
    )
    where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')
    order by 1, 2
    /

    这里是另一个脚本用来跟踪未使用的索引并展示给所有索引的调用计数。最重要的是，这个脚本显示了多列索引引用的列（这个脚本执行时间较长，资源开销较大。） 

    col c1 heading 'Begin|Interval|time' format a20
    col c2 heading 'Search Columns'      format 999
    col c3 heading 'Invocation|Count'    format 99,999,999
     
     
    break on c1 skip 2
     
    accept idxname char prompt 'Enter Index Name: '
     
    ttitle 'Invocation Counts for index|&idxname'
     
    select
       to_char(sn.begin_interval_time,'yy-mm-dd hh24')  c1,
       p.search_columns                                 c2,
       count(*)                                         c3
    from
       dba_hist_snapshot  sn,
       dba_hist_sql_plan   p,
       dba_hist_sqlstat   st
    where
       st.sql_id = p.sql_id
    and
       sn.snap_id = st.snap_id   
    and   
       p.object_name = '&idxname'
    group by
       begin_interval_time,search_columns;

    参考资料：

    http://www.dba-oracle.com/oracle_tips_unused_indexes.htm

     

  • 相关阅读:
    SVN版本库修改URL路径或者IP地址
    ES-PHP向ES批量添加文档报No alive nodes found in your cluster
    ansible IP
    ansible ansible_os_family == "RedHat" and ansible_lsb.major_release|int >= 6 转为数字比大小
    Centos下Yum安装PHP5.5,5.6,7.0
    centos6.8上yum安装zabbix3.2
    线性筛的理解及应用
    5分钟使用docker搭建一个WordPress
    使用 Docker-Compose 编排容器
    Bootstrap基础
  • 原文地址：https://www.cnblogs.com/kerrycode/p/5006035.html
Copyright © 2020-2023  润新知