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. Run the script directly 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.sqlselect '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 '); spo Ol 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.sqlb, disable INDEX monitoring Robin @ szdb :~ /Dba_scripts/custom/SQL> More idx_monitor_off.sqlset heading off feedback off termout off echo off; Set pagesize 0; spool/tmp/un_mnt_idx.sqlselect '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 ', 'sysmance', '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_0612SELECT index_name, monitoring, used, start_monitoring, end_monitoring from V $ object_usage; ho Rm-RF/tmp/un_mnt_idx.sqlc, View index monitoring result 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 '));

4. Demonstrate index monitoring

A. single index monitoring --> demonstration environment Scott @ cnmmbo> select * from V $ version where rownum <2; banner -------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0-64bit production --> Create test table Scott @ cnmmbo> Create Table tb_emp as select * from EMP; --> Create an index for the test table Scott @ cnmmbo> Create index I _tb_emp_empno on tb_emp (empno); --> collect statistics Scott @ cnmmbo> exec dbms_stats.gather_table_stats ('Scott ', 'tb _ emp', cascade => true); --> View index information Scott @ cnmmbo> @ idx_infoenter value for owner: scottenter value for table_name: tb_emptable name index_name cl_nam cl_pos status idx_typ dscd certificate -------------------- -------- ----------------- ---- tb_emp quota empno 1 valid normal ASC --> View index usage --> the use column is no, it indicates that the index is not used. Scott @ cnmmbo> @ idx_usage_tb enter value for 1: tb_empenter value for 2: allenter value for 2: alltable name index_name use start_monitoring end_monitoring too many --- implements ad hoc query Scott @ cnmmbo> select empno, ename, job from tb_emp where empno = 7788; empno ename job ---------- ----------- 7788 Scott analyst --> check again that the use column is yesscott @ cnmmbo> @ idx_usage_tbenter value for 1: tb_empenter value for 2: allenter value for 2: alltable name index_name use start_monitoring end_monitoring reschedule --- pai----------------- tb_emp limit Yes 03/19/2013 17:43:49 --> disable INDEX monitoring Scott @ cnmmbo> alter Index Limit nomonitoring usage; index altered. b. Schema-level index monitoring --> switch to another database cnbo1scott @ cnmmbo> conn goex_admin/XXXXX @ cnbo1connected. --> The following query indicates that no table has enabled index monitoring goex_admin @ cnbo1> @ idx_usage; no rows selected --> enable index monitoring goex_admin @ cnbo1> @ mongomon use start_monitoring end_monitoring metrics --- begin monitoring pk_aah Yes No 03/19/2013 17: 48: Pushed Yes No 03/19/2013 17: 48: 32pk_goaat Yes No 03/19/2013 17: 48: 32pk_goaactl Yes No 03/19/2013 17:48:32 ....................... --> disable INDEX monitoring goex_admin @ cnbo1> @ mongomon use start_monitoring reschedule --- zookeeper pk_goarl no 03/19/2013 17:48:30 03/19/2013 17: 50: Disabled no 03/19/2013 17:48:30 03/19/2013 17: 50: 02idx_goaqu2 no 03/19/2013 17:48:30 03/19/2013 17:50:02 --> connect to the original DB, view the usage of the index monitoring that has been enabled goex_admin @ cnbo1> conn Scott/tiger@cnmmboConnected.goex _ admin @ cnmmbo> @ idx_usageenter value for input_owner: goex_adminenter value for input_owner: using index_name table name mon use start_monitoring when creating too many tables --- just -------------- Scott comment tb_emp No Yes 03/19/2013 17:43:49 03/19/2013 17: 46: 04goex_admin Comment comment yes 02/22/2013 15: 58: 42goex_admin then trade_client_tbl yes 02/22/2013 15: 58: 42goex_admin then trade_client_tbl yes 02/22/2013 15: 58: 42goex_admin then trade_client_tbl yes 02/22/2013 15: 58: 41goex_admin then yes 02/22/2013 15:58:42

5. Recommendations and drawbacks of index monitoring
A. Select the database peak period to implement index monitoring, and use a long monitoring period as much as possible to determine whether the index is used
B. You can perform multiple monitoring for a specific period of time to determine the index usage frequency (initial value)
C. index monitoring consumes system resources to a certain extent. Once the monitoring is completed, it should be immediately disabled to avoid additional overhead.
D. index monitoring only describes the use of indexes and does not provide detailed index usage frequency. The method mentioned at point B is just a preliminary value.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)