你的位置:首页 > 数据库

[数据库]如何监控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能修改Alert(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