Chinaunix首页 | 论坛 | 博客
  • 博客访问: 705455
  • 博文数量: 102
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3059
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(102)

文章存档

2020年(67)

2014年(3)

2013年(32)

分类: Oracle

2020-07-15 17:21:48

1.背景介绍

随着业务的增长,生命周期管理单纯对表的管理,已经无法满足目前公司业务增长对存储的需求,因此,急需从其它方面对模型进行精简,而索引是占存储较大的对象,由于冗余索引、无效索引、无用索引等存在,导致索引浪费存储,需要制定一套策略进行管理。

索引监控与分析则是一套很好的方法,ORACLE提供索引monitor的方法(对系统资源使用影响小),但是无法监控索引使用频次,另一种方法可以通过使用AWR视图DBA_HIST_SQL_PLAN,可以查询到索引的使用频次,当然,有些SQL可能进入不了AWR中,因此,我们采用综合的方法:

1)AWR中(要求30天以上)查询出未被使用的索引。

2)1)中的索引使用ORACLE INDEX MONITOR监控30天。

   如果最终查询出的索引仍未被使用,则认为此索引是多余索引,提交对应库负责人确认后予以清除。

 

2.方案和步骤

1.  DBA_HIST_SQL_PLAN中查询未被使用的索引,放入临时表中

CREATE TABLE index_mon_tmp TABLESPACE TBS_PERSON AS
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 = I.OWNER
     AND S.OWNER NOT IN ('SYS','OLAPSYS','SYSMAN','EXFSYS',
     'MDSYS','ORDSYS','XDB','CTXSYS','WMSYS',
     'DBSNMP','SYSTEM','OUTLN','DMSYS','TSMSYS','SPA')
     AND INDEX_NAME = SEGMENT_NAME
   GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
  HAVING SUM(S.BYTES) > 1048576 * 300)
 SELECT OWNER,TABLE_NAME,INDEX_NAME,INDEX_TYPE,MB
  FROM (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)
        HAVING COUNT(OPERATION) = 0
        );

 

 

2.  删除属于约束的索引

DELETE FROM INDEX_MON_TMP a
WHERE EXISTS(SELECT 1
FROM DBA_CONSTRAINTS b
WHERE a.owner=b.index_owner
AND a.table_name=b.table_name
AND a.index_name=b.index_name
);
COMMIT;

 



可以查询INDEX_MON_TMP看看共有多少索引未被使用,对应的大小:

SELECT COUNT(*),SUM(mb) from index_mon_tmp;

 

3.  拼语句,进行索引monitoring监控

将下列脚本保存为mon_temp.sql,然后在sqlplus@mon_temp.sql,会生成monitoring.sql,然后@monitoring.sql即可对索引进行监控。

set line 1000 pagesize 0 feedback off echo off trimout on trimspool on termout off timing off
spool monitoring.sql
select 'alter index '||owner||'."'||index_name||'" monitoring usage;'
from INDEX_MON_TMP;
spool off

 


注意执行如果报正在使用错误(ORA-00054: resource busy),不用管,说明此索引被使用。

 

4.  执行第3步对应脚本,并建立监控视图

create  view ALL_OBJECT_USAGE
           (OWNER,
           INDEX_NAME,
           TABLE_NAME,
           MONITORING,
           USED,
           START_MONITORING,
           END_MONITORING)
           as
             select u.name, io.name, t.name,
             decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
             decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
             ou.start_monitoring,
             ou.end_monitoring
           from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
           where i.obj# = ou.obj#
           and io.obj# = ou.obj#
           and t.obj# = i.bo#
           and u.user# = io.owner#;

 


可以查询下有多少索引被监控:

SELECT COUNT(*) ALL_COUNT,
       COUNT(DECODE(USED, 'YES', 1, NULL)) USED_COUNT,
       COUNT(DECODE(USED, 'NO', 1, NULL)) NO_USED_COUNT
  FROM SYS.ALL_OBJECT_USAGE
 WHERE END_MONITORING IS NULL;





5.  查询监控视图,30天后停止监控

SELECT start_monitoring FROM sys.ALL_OBJECT_USAGE WHERE END_MONITORING IS NULL and ROWNUM=1;--查询监控开始时间

停止监控,拼脚本如下:

将下列脚本保存为nomon_temp.sql,然后在sqlplus@nomon_temp.sql,会生成nomonitoring.sql,然后@nomonitoring.sql即可对索引进行监控。


set line 1000 pagesize 0 feedback off echo off trimout on trimspool on termout off timing off
spool nomonitoring.sql
select 'alter index '||owner||'."'||index_name||'" nomonitoring usage;'
from sys.ALL_OBJECT_USAGE WHERE end_monitoring IS NULL;
spool off

    

注意执行过程中可能报ORA-00054错误,需要多次查询下列语句:

select count(*) from sys.ALL_OBJECT_USAGE WHERE end_monitoring IS NULL;

   

直到查询结果为0说明停止监控成功。

 

3.结果分析和处理

根据下列语句查询未被使用的索引及其大小,注意输入监控开始和结束时间,比如2020-2-10日开始监控的,2020-2-20日结束,可以输入20200210 00:00:00,20200220 00:00:00

SELECT /*+rule*/
 A.OWNER, A.TABLE_NAME, A.INDEX_NAME, SUM(B.BYTES) / 1024 / 1024 MB
  FROM SYS.ALL_OBJECT_USAGE A, DBA_SEGMENTS B
 WHERE A.OWNER = B.OWNER
   AND A.INDEX_NAME = B.SEGMENT_NAME
   AND A.USED = 'NO'
   AND to_date(a.start_monitoring,'mm/dd/yyyy hh24:mi:ss')>=to_date('&d1','yyyymmdd hh24:mi:ss')
   AND to_date(a.end_monitoring,'mm/dd/yyyy hh24:mi:ss')<=to_date('&d2','yyyymmdd hh24:mi:ss')
 GROUP BY A.OWNER, A.TABLE_NAME, A.INDEX_NAME
 ORDER BY  A.OWNER, A.TABLE_NAME, A.INDEX_NAME DESC;

 


对查询的结果提交给业务侧进行分析确认,哪些索引可以DROP掉,从而精简数据库的容量。

 

 

阅读(270) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~