Oracle index monitoring)

Reasonable creation of strategic indexes for database tables can greatly improve query performance. But in fact, the index we create in daily life is not a strategic index. It is precisely because a large number of redundant or useless indexes consume a large amount of storage space, resulting in low DML performance. Oracle provides the index monitoring feature to preliminarily judge unused indexes. This document describes how to use Oracle index monitoring.

1. disadvantages of redundant Indexes

A large number of redundant and useless indexes lead to low performance of the entire database, consuming a lot of CPU and I/O overhead, the specific performance is as follows:

A. a large amount of storage space is consumed (Maintenance and Management of index segments)

B. added the DML completion time.

C. It takes a lot of time for Statistics (indexing) to be collected.

D. Structural verification time

F. The recovery time is increased.

2. single index monitoring

A. You can use the following command to monitor a single index:

Alter index <INDEX_NAME> monitoring usage;

B. Disable index monitoring

Alter index <INDEX_NAME> nomonitoring usage;

C. Observe the monitoring results (query the v $ object_usage view)

Select * from v $ object_usage

3. schema-level index monitoring (excluding SYS Users)

A. directly execute scripts to enable index monitoring.
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', 'sysmanc', '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. Disable index monitoring
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', 'sysmanc', '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. View index monitoring results
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,
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 #
AND u. name = decode (upper ('& input_owner'), 'all', u. name, upper ('& input_owner '));

1 2 Next Page