Oracle索引的监控

  1. 云栖社区>
  2. 博客>
  3. 正文

Oracle索引的监控

小麦苗 2016-06-22 21:28:47 浏览563
展开阅读全文

Oracle索引的监控

 

一.1  BLOG文档结构图

wps10E1.tmp 

 

一.2  前言部分

 

一.2.1  导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~

掌握oracle中索引的监控方法

② sys.col_usage$的初步了解

 

  Tips:

① 本文在ITpubhttp://blog.itpub.net/26736162)和博客园(http://www.cnblogs.com/lhrbest)有同步更新

② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/

若文章代码格式有错乱,推荐使用搜狗360QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/

本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体注;对代码或代码输出部分的注释一般采用蓝色字体表示

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

 

一.3  相关知识点扫盲(摘自网络)

 

合理的为数据库表上创建战略性索引,可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引,恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间,导致DML性能低下。 应用程序在开发时,可能会建立众多索引,但是这些索引的使用到底怎么样,是否有些索引一直都没有用到过,这需要我们对这些索引进行监控,以便确定他们的使用情况,并为是否可以清除它们给出依据。

冗余索引的弊端

大量冗余和无用的索引导致整个数据库性能低下,耗用了大量的CPUI/O开销,具体表现如下:

       a浪费大量的存储空间,尤其是大表的索引,浪费的存储空间尤其可观(索引段的维护与管理)

       b、增加了DML 操作(UPDATEINSERTDELETE)的开销

       c、耗用大量统计信息(索引)收集的时间

       d、结构性验证时间

       f、增加了恢复所需的时间

 

    本文介绍两种方式:

    第一:开启监控功能;

    第二:查看历史的执行计划,进行分析;

一.4  索引监控的方法

一.4.1  方法一:开启监控功能

 

 

1、单个索引监控  

       a、对于单个索引的监控,可以使用下面的命令来完成

           alter index <INDEX_NAME> monitoring usage;

       b、关闭索引监控

          alter index <INDEX_NAME> nomonitoring usage;

       c、观察监控结果(查询v$object_usage视图)

          select * from v$object_usage;

 

2schema级别索引监控

 

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

1LOB indexes不能修改,否则会报ORA-22864错误(ORA-22864: cannot ALTER or DROP LOB indexes)。

2ORA-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.

 

直接执行脚本来开启索引监控,当然监控索引时长非常重要,太短的话有可能导致查询出来的数据有问题,一般建议监控一周后即可,OLAP系统则需要适当延长监控的时间。

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,

       'ALTER INDEX ' || owner || '.' || index_name ||

       ' NOMONITORING USAGE;' disable_monitor

  FROM dba_indexes

 WHERE INDEX_TYPE != 'LOB'

   and owner IN

       (SELECT username FROM dba_users WHERE account_status = 'OPEN')

   AND owner NOT IN ('SYS',

                     'SYSTEM',

                     'PERFSTAT',

                     'MGMT_VIEW',

                     'MONITOR',

                     'SYSMAN',

                     'DBSNMP')

   AND owner not like '%SYS%';

 

 

监控一个月就大概可以知道那些是无用的索引了。

虽然v$object_usage表能记录索引监控和使用的状态,但它不能统计索引被使用的次数和频率,只记录了在开启索引监控的时间段索引是否被使用过,这一点要值的注意。

另外需要注意的2点:

10g在收集统计信息时会导致索引被监控、这并非SQL语句产生、而在11g则不会出现这种情况了

② 外键索引不会因为主表的DML操作而被监控到、不要因为该索引没用而将它给删了

 

  

一.4.1.1  个人实验

新建1个表TB_LHR_20160622,并创建2个索引:

SYS@raclhr2> select * from v$version;

 

BANNER

-------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SYS@raclhr2> Create Table TB_LHR_20160622 nologging As select *  from dba_objects;

 

Table created.

 

SYS@raclhr2> create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

 

Index created.

 

SYS@raclhr2> create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

 

Index created.

 

 

查询v$object_usage视图,收集统计信息:

SYS@raclhr2> select * from v$object_usage;

 

no rows selected

 

SYS@raclhr2> BEGIN

  2      dbms_stats.gather_table_stats(USER,

  3                                    'TB_LHR_20160622',

  4                                    cascade      => TRUE,

  5                                    degree       => 8);

  6  END;

  7  /

 

PL/SQL procedure successfully completed.

 

SYS@raclhr2> select * from v$object_usage;

 

no rows selected

 

 

开启索引的监控:

SYS@raclhr2> alter index   ind_TB_LHR_20160622_id monitoring usage;

 

Index altered.

 

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v$object_usage;

 

INDEX_NAME                TABLE_NAME           MONITORING USED       START_MONITORING     END_MONITORING

------------------------- -------------------- ---------- ---------- -------------------- --------------------

IND_TB_LHR_20160622_ID    TB_LHR_20160622      YES        NO         06/22/2016 15:15:54

 

SYS@raclhr2> alter index   ind_TB_LHR_20160622_name monitoring usage;

 

Index altered.

 

SYS@raclhr2> select count(1) from TB_LHR_20160622 t where t.object_id=88;

 

  COUNT(1)

----------

         1

 

SYS@raclhr2> explain plan for select count(1) from TB_LHR_20160622 t where t.object_id=88;

 

Explained.

 

SYS@raclhr2> select * from table(dbms_xplan.display());

 

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------

Plan hash value: 2688591802

 

--------------------------------------------------------------------------------------------

| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |

|*  2 |   INDEX RANGE SCAN| IND_TB_LHR_20160622_ID |     1 |     5 |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("T"."OBJECT_ID"=88)

 

14 rows selected.

 

SYS@raclhr2> COL INDEX_NAME FOR A25

SYS@raclhr2> COL TABLE_NAME FOR A20

SYS@raclhr2> COL MONITORING FOR A10

SYS@raclhr2> COL USED FOR A10

SYS@raclhr2> COL START_MONITORING FOR A20

SYS@raclhr2> COL END_MONITORING FOR A20

SYS@raclhr2> select * from v$object_usage;

 

INDEX_NAME                TABLE_NAME           MONITORING USED       START_MONITORING     END_MONITORING

------------------------- -------------------- ---------- ---------- -------------------- --------------------

IND_TB_LHR_20160622_ID    TB_LHR_20160622     YES        YES        06/22/2016 15:15:54

IND_TB_LHR_20160622_NAME  TB_LHR_20160622      YES        NO         06/22/2016 15:16:17

 

 

注意:SELECT * FROM V$OBJECT_USAGE; 只能查看当前用户下被监控的索引信息。即使syssystem用户也不能查看其它用户的信息,如下,但我们可以创建一个视图来解决这个问题。

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> select * from v$object_usage;

 

no rows selected

 

SCOTT@raclhr2> conn / as sysdba

Connected.

SYS@raclhr2> create or replace view vw_INDEX_USAGE_lhr     AS

  2  SELECT U.NAME OWNER,

  3         IO.NAME INDEX_NAME,

  4         T.NAME TABLE_NAME,

  5         DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

  6         DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

  7         OU.START_MONITORING START_MONITORING,

  8         OU.END_MONITORING END_MONITORING

  9    FROM SYS.USER$        U,

10         SYS.OBJ$         IO,

11         SYS.OBJ$         T,

12         SYS.IND$         I,

13         SYS.OBJECT_USAGE OU

14   WHERE I.OBJ# = OU.OBJ#

15     AND IO.OBJ# = OU.OBJ#

16     AND T.OBJ# = I.BO#

17     AND U.USER# = IO.OWNER#;

 

View created.

 

SYS@raclhr2> create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

 

Synonym created.

 

SYS@raclhr2> grant select on sys.vw_INDEX_USAGE_lhr to public;

 

Grant succeeded.

 

SYS@raclhr2> conn scott/tiger

Connected.

SCOTT@raclhr2> set line 9999 pagesize 9999

SCOTT@raclhr2> col owner format A10

SCOTT@raclhr2> COL INDEX_NAME FOR A25

SCOTT@raclhr2> COL TABLE_NAME FOR A20

SCOTT@raclhr2> COL MONITORING FOR A10

SCOTT@raclhr2> COL USED FOR A10

SCOTT@raclhr2> COL START_MONITORING FOR A20

SCOTT@raclhr2> COL END_MONITORING FOR A20

SCOTT@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

 

OWNER      INDEX_NAME                TABLE_NAME           MONITORING USED       START_MONITORING     END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS        IND_TB_LHR_20160622_ID    TB_LHR_20160622      YES        YES        06/22/2016 15:15:54

SYS        IND_TB_LHR_20160622_NAME  TB_LHR_20160622      YES        NO         06/22/2016 15:16:17

 

 

 

取消索引的监控:

 

SCOTT@raclhr2> CONN / AS SYSDBA

Connected.

SYS@raclhr2> alter index   ind_TB_LHR_20160622_id nomonitoring usage;

 

Index altered.

 

SYS@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

 

OWNER      INDEX_NAME                TABLE_NAME           MONITORING USED       START_MONITORING     END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS        IND_TB_LHR_20160622_ID    TB_LHR_20160622      NO         YES        06/22/2016 15:15:54  06/22/2016 15:22:30

SYS        IND_TB_LHR_20160622_NAME  TB_LHR_20160622      YES        NO         06/22/2016 15:16:17

 

 

SYS@raclhr2> alter index   ind_TB_LHR_20160622_name nomonitoring usage;

 

Index altered.

 

SYS@raclhr2> SELECT * FROM syn_INDEX_USAGE_lhr;

 

OWNER      INDEX_NAME                TABLE_NAME           MONITORING USED       START_MONITORING     END_MONITORING

---------- ------------------------- -------------------- ---------- ---------- -------------------- --------------------

SYS        IND_TB_LHR_20160622_ID    TB_LHR_20160622      NO         YES        06/22/2016 15:15:54  06/22/2016 15:22:30

SYS        IND_TB_LHR_20160622_NAME  TB_LHR_20160622      NO         NO         06/22/2016 15:22:45  06/22/2016 15:23:12

一.4.1.2  实验中用到的SQL

drop table TB_LHR_20160622 purge;

Create Table TB_LHR_20160622 nologging As select *  from dba_objects;

create index ind_TB_LHR_20160622_id on TB_LHR_20160622(object_id);

create index ind_TB_LHR_20160622_name on TB_LHR_20160622(object_name);

 

 

select * from v$object_usage;

 

BEGIN

    dbms_stats.gather_table_stats(USER,

                                  'TB_LHR_20160622',

                                  cascade      => TRUE,

                                  degree       => 8);

END;

/

 

 

alter index   ind_TB_LHR_20160622_id monitoring usage; 

alter index   ind_TB_LHR_20160622_name monitoring usage;

 

select count(1) from TB_LHR_20160622 t where t.object_id=88;

 

set line 9999 pagesize 9999

col owner format A10

COL INDEX_NAME FOR A25

COL TABLE_NAME FOR A20

COL MONITORING FOR A10

COL USED FOR A10

COL START_MONITORING FOR A20

COL END_MONITORING FOR A20

select * from v$object_usage;

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

wps10F1.tmp 

alter index   ind_TB_LHR_20160622_id nomonitoring usage; 

alter index   ind_TB_LHR_20160622_name nomonitoring usage;

 

 

---  drop table t purge;  表删掉后    v$object_usage 中关于监控的信息也删除了

 

----切换用户后查询select * from v$object_usage;查询不到数据,下边这个SQL可以查询任何用户下的索引使用情况

create or replace view vw_INDEX_USAGE_lhr     AS

SELECT U.NAME OWNER,

       IO.NAME INDEX_NAME,

       T.NAME TABLE_NAME,

       DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MONITORING,

       DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,

       OU.START_MONITORING START_MONITORING,

       OU.END_MONITORING 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#;

 

create or replace public synonym syn_INDEX_USAGE_lhr for sys.vw_INDEX_USAGE_lhr;

 

set line 9999 pagesize 9999

col owner format A10

COL INDEX_NAME FOR A25

COL TABLE_NAME FOR A20

COL MONITORING FOR A10

COL USED FOR A10

COL START_MONITORING FOR A20

COL END_MONITORING FOR A20

SELECT * FROM syn_INDEX_USAGE_lhr;

 

批量监控系统的所有索引:

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,

       'ALTER INDEX ' || owner || '.' || index_name ||

       ' NOMONITORING USAGE;' disable_monitor

  FROM dba_indexes

 WHERE INDEX_TYPE != 'LOB'

   and owner IN

       (SELECT username FROM dba_users WHERE account_status = 'OPEN')

   AND owner NOT IN ('SYS',

                     'SYSTEM',

                     'PERFSTAT',

                     'MGMT_VIEW',

                     'MONITOR',

                     'SYSMAN',

                     'DBSNMP')

   AND owner not like '%SYS%';

 

 

一.4.2  方法二:查看历史的执行计划进行分析

虽然v$object_usage表能记录索引监控和使用的状态,但它不能统计索引被使用的次数和频率,只记录了在开启索引监控的时间段索引是否被使用过,因此想详细了解索引的使用情况我们可以利用AWR的一些视图dba_hist_sql_plandba_hist_sqlstat来弄清楚数据库访问某个索引的次数、索引访问的类型,如索引范围扫描或索引唯一扫描

 

WITH tmp1 AS

 (SELECT i.OWNER INDEX_OWNER,

         i.table_owner,

         TABLE_NAME,

         INDEX_NAME,

         INDEX_TYPE,

         (select nb.created

            from dba_objects nb

           WHERE nb.owner = i.owner

             and nb.object_name = i.index_name

             and nb.subobject_name is null) created,

         (SUM(S.bytes) / 1024 / 1024) INDEX_MB

    FROM DBA_SEGMENTS S, DBA_INDEXES I

   WHERE i.INDEX_NAME = s.SEGMENT_NAME

     and i.owner = s.owner

     and s.owner not like '%SYS%'

  /*and s.owner = 'FUNDZ'*/

   GROUP BY i.OWNER, i.table_owner, TABLE_NAME, INDEX_NAME, INDEX_TYPE

  HAVING SUM(S.BYTES) > 1024 * 1024),

tmp2 as

 (SELECT index_owner,

         index_name,

         plan_operation,

         (SELECT min(to_char(nb.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS'))

            FROM dba_hist_snapshot nb

           where nb.snap_id = v.min_snap_id) min_date,

         (SELECT max(to_char(nb.end_interval_time, 'YYYY-MM-DD HH24:MI:SS'))

            FROM dba_hist_snapshot nb

           where nb.snap_id = v.max_snap_id) max_date,

         counts

    FROM (SELECT d.object_owner index_owner,

                  d.object_name index_name,

                  d.operation || ' ' || d.options plan_operation,

                  min(h.snap_id) min_snap_id,

                  max(h.snap_id) max_snap_id,

                  COUNT(1) counts

             FROM dba_hist_sql_plan d, dba_hist_sqlstat h

            WHERE /*d.object_owner = 'FUNDZ'

                                              AND */

            d.operation LIKE '%INDEX%'

         AND d.sql_id = h.sql_id

            GROUP BY d.object_owner, d.object_name, d.operation, d.options) v)

SELECT a.table_owner,

       a.TABLE_NAME,

       a.index_owner,

       a.index_name,

       a.created,

       a.INDEX_TYPE,

       a.INDEX_MB,

       b.plan_operation,

       min_date,

       max_date,

       counts

  from tmp1 a

  left outer join tmp2 b

    on (a.index_owner = b.index_owner and a.index_name = b.index_name);

 

wps1102.tmp 

如上图所示,有一个3.6G大的索引在13号到22号从没使用过,接下来,我们可以继续查询该索引是否联合索引,创建是否合理,分析为何不走该索引,从而判断是否可以删除索引。

另外下边的SQL可以查询出表上列的使用情况:

CREATE OR REPLACE VIEW VW_COLUMN_USAGE_LHR AS

SELECT oo.name             owner,

       o.name              table_name,

       c.name              column_name,

       u.equality_preds,

       u.equijoin_preds,

       u.nonequijoin_preds,

       u.range_preds,

       u.like_preds,

       u.null_preds,

       u.timestamp

  FROM sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c

 WHERE o.obj# = u.obj#

   AND oo.user# = o.owner#

   AND c.obj# = u.obj#

   AND c.col# = u.intcol#

;

 

 

About Me

..........................................................................................................................................................................................................

本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

本文在ITpub(http://blog.itpub.net/26736162)和博客园(http://www.cnblogs.com/lhrbest)有同步更新

本文地址:

本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b)

小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

联系我请加QQ好友(642808185),注明添加缘由

于 2016-04-06 10:00~ 2016-04-11 19:00 在中行完成

【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

..........................................................................................................................................................................................................

 

 

 

网友评论

登录后评论
0/500
评论