Oracle索引监控(monitor index)-白红宇的个人博客

Oracle索引监控(monitor index)

发布日期：2021-09-16 04:38:24 浏览次数：46 分类：技术文章

本文共 5929 字，大约阅读时间需要 19 分钟。

Oracle索引监控(monitor index)

合理的为数据库表上创建战略性索引，可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。Oracle/" target=_blank>Oracle 提供了索引监控特性来初略判断未使用到的索引。本文描述如何使用Oracle 索引的监控。

1、冗余索引的弊端

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

a、耗用大量的存储空间(索引段的维护与管理)

b、增加了DML完成的时间

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

d、结构性验证时间

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

2、单个索引监控

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

alter index <INDEX_NAME> monitoring usage;

b、关闭索引监控

alter index <INDEX_NAME> nomonitoring usage;

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

select * from v$object_usage

3、schema级别索引监控(不含SYS用户)

[sql]

a、直接执行脚本来开启索引监控

robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_on.sql

SET HEADING OFF FEEDBACK OFF TERMOUT OFF ECHO OFF;

SET PAGESIZE 0;

SPOOL /tmp/mnt_idx.sql



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

FROM dba_indexes

WHERE owner IN (SELECT username

FROM dba_users

WHERE account_status = 'OPEN')

AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP');



SPOOL OFF;

@/tmp/mnt_idx.sql;

SET HEADING ON FEEDBACK ON TERMOUT ON;

SET PAGESIZE 80;



SELECT index_name,

monitoring,

used,

start_monitoring,

end_monitoring

FROM v$object_usage;



ho rm -rf /tmp/mnt_idx.sql



b、禁用索引监控

robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_off.sql

SET HEADING OFF FEEDBACK OFF TERMOUT OFF ECHO OFF;

SET PAGESIZE 0;

SPOOL /tmp/un_mnt_idx.sql

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' NOMONITORING USAGE;'

FROM dba_indexes

WHERE owner IN (SELECT username

FROM dba_users

WHERE account_status = 'OPEN')

AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP');



SPOOL OFF;

@/tmp/un_mnt_idx.sql;

SET HEADING ON FEEDBACK ON TERMOUT ON;

SET PAGESIZE 80;



--> Author : Robinson

--> Blog : http://blog.csdn.net/robinson_0612



SELECT index_name,

monitoring,

used,

start_monitoring,

end_monitoring

FROM v$object_usage;



ho rm -rf /tmp/un_mnt_idx.sql



c、查看索引监控结果

set linesize 190

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#

AND u.name=decode(upper('&input_owner'),'ALL',u.name,upper('&input_owner'));

4、演示索引监控

[sql]

a、单个索引监控

-->演示环境

scott@CNMMBO> select * from v$version where rownum<2;



BANNER

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

Oracle Database 10g Release 10.2.0.3.0 - 64bit Production



-->创建测试表

scott@CNMMBO> create table tb_emp as select * from emp;



-->为测试表创建索引

scott@CNMMBO> create index i_tb_emp_empno on tb_emp(empno);



-->收集统计信息

scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP',cascade=>true);



-->查看索引信息

scott@CNMMBO> @idx_info

Enter value for owner: scott

Enter value for table_name: tb_emp



Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD

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

TB_EMP I_TB_EMP_EMPNO EMPNO 1 VALID NORMAL ASC



-->查看索引使用情况

-->此时use列为NO，表明索引未被使用到

scott@CNMMBO> @idx_usage_tb

Enter value for 1: tb_emp

Enter value for 2: all

Enter value for 2: all



Table Name INDEX_NAME USE START_MONITORING END_MONITORING

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

TB_EMP I_TB_EMP_EMPNO NO 03/19/2013 17:43:49



-->实施即席查询

scott@CNMMBO> select empno,ename,job from tb_emp where empno=7788;



EMPNO ENAME JOB

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

7788 SCOTT ANALYST



-->再次查看时USE列已经为YES

scott@CNMMBO> @idx_usage_tb

Enter value for 1: tb_emp

Enter value for 2: all

Enter value for 2: all



Table Name INDEX_NAME USE START_MONITORING END_MONITORING

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

TB_EMP I_TB_EMP_EMPNO YES 03/19/2013 17:43:49



-->禁用索引监控

scott@CNMMBO> alter index I_TB_EMP_EMPNO nomonitoring usage;



Index altered.



b、schema级别的索引监控

-->切换到另外一个数据库cnbo1

scott@CNMMBO> conn goex_admin/xxxxx@cnbo1

Connected.



-->下面的查询表明没有表开启索引监控

goex_admin@CNBO1> @idx_usage;



no rows selected



-->开启索引监控

goex_admin@CNBO1> @idx_monitor_on



INDEX_NAME MON USE START_MONITORING END_MONITORING

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

PK_AAH YES NO 03/19/2013 17:48:32

IDX_GOAAE1 YES NO 03/19/2013 17:48:32

PK_GOAAT YES NO 03/19/2013 17:48:32

PK_GOAACTL YES NO 03/19/2013 17:48:32

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



-->关闭索引监控

goex_admin@CNBO1> @idx_monitor_off

INDEX_NAME MON USE START_MONITORING END_MONITORING

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

PK_GOARL NO NO 03/19/2013 17:48:30 03/19/2013 17:50:02

IDX_GOAQU1 NO NO 03/19/2013 17:48:30 03/19/2013 17:50:02

IDX_GOAQU2 NO NO 03/19/2013 17:48:30 03/19/2013 17:50:02



-->连接到原来的db，查看曾经开启索引监控的使用情况

goex_admin@CNBO1> conn scott/tiger@cnmmbo



Connected.



goex_admin@CNMMBO> @idx_usage

Enter value for input_owner: GOEX_ADMIN

Enter value for input_owner: GOEX_ADMIN



OWNER INDEX_NAME Table Name MON USE START_MONITORING END_MONITORING

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

SCOTT I_TB_EMP_EMPNO TB_EMP NO YES 03/19/2013 17:43:49 03/19/2013 17:46:04

GOEX_ADMIN ACC_GRP_EXT_INFO_TBL_LOG_PK ACC_GRP_EXT_INFO_TBL_LOG YES YES 02/22/2013 15:58:42

GOEX_ADMIN IDX_TDCL_CONTRACT_NUM TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:42

GOEX_ADMIN IDX_TDCL_SETTLED_DATE TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:42

GOEX_ADMIN IDX_TDCL_ACC_NUM TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:41

GOEX_ADMIN IDX_TDCL_INSTRU_ID TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:42

5、索引监控的建议与弊端

a、选择数据库高峰期实施索引监控，以及尽可能使用较长的监控周期来判断索引是否被使用

b、可以对特定时间段实施多次监控以判断索引的使用频率(初略值)

c、索引监控在一定程度上耗用系统资源，一旦监控完毕后应即时关闭以避免其带来的额外开销

d、索引监控仅仅从索引的使用与否来描述索引使用，并未提供详细的索引使用频率，b点提到的方法也只是初略值

转载地址：https://blog.csdn.net/wll_1017/article/details/45699733 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：ORA-00600: 内部错误代码, 参数: [kkzucmvl_ChkMVLog: kkzlglnm], [], [], [], [], [], [], [], [], [], [], []

下一篇：收集统计信息

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章