Oracle 索引监控（monitor index）

您的位置：
门户
>> 文章精选
>> 软件开发专栏
>> 数据库
>> 查看资讯

发表于：2013-6-07 10:02

作者：RobinsonCheng 来源：51Testing软件测试网采编

软件开发

数据库

Oracle

　　合理的为数据库表上创建战略性索引，可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间，导致DML性能低下。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用户）

　　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;

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'));

21/212 >

《2023软件测试行业现状调查报告》独家发布~

搜索风云榜

测试技术了解

2023测试行业调查报告

挣点稿费

AI与软件测试

文章资料精选