Introduction to index monitoring in Oracle 9i

Introduction

Both DBAs and developers like indexing. They can accelerate query and search, especially in a data warehouse environment, because the database will receive many ad-hoc requests. To avoid full table search, we usually create an index in each column that may be searched. However, indexes occupy many tablespaces. In many cases, indexes consume more storage space than the indexed tables. When inserting and deleting rows, the index also introduces additional overhead. Before Oracle9i, it is difficult to know whether an index is used. Therefore, many databases have many useless indexes. The purpose of this article is to introduce new features in Oracle9i to identify unused indexes.

Identifies unused Indexes

Oracle9i provides a new technology to monitor indexes to identify whether indexes are used. To start monitoring the use of an index, run the following command:

ALTER INDEX index_name MONITORING USAGE;

To stop monitoring an index, enter:

ALTER INDEX index_name NOMONITORING USAGE;

The v $ objec_usage view contains the usage information of index monitoring.

　

CREATE OR REPLACE VIEW SYS.V$OBJECT_USAGE (INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING)ASselect io.name, t.name,decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),ou.start_monitoring,ou.end_monitoringfrom sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ouwhere io.owner# = userenv('SCHEMAID')and i.obj# = ou.obj#and io.obj# = ou.obj#and t.obj# = i.bo#/COMMENT ON TABLE SYS.V$OBJECT_USAGE IS 'Record of index usage'/GRANT SELECT ON SYS.V$OBJECT_USAGE TO "PUBLIC"/

This view displays the index usage statistics collected by the database. The following describes the columns in the View:

INDEX_NAME: sys. obj $. name: TABLE_NAME: sys. in obj $ name, the table name MONITORING: YES (the index is being monitored), NO (the index is not monitored) USED: YES (the index has been USED ), NO (index not used) START_MONITORING: Start monitoring time END_MONITORING: End monitoring time

All indexes that have been used at least once can be monitored and displayed in this view. However, a user can only accept the index usage in its own mode. Oracle does not provide a view to receive indexes in all modes. To use indexes in all modes, Log On As a sys user and run the following script. Note: This is not a script provided by Oracle. V $ all_object_usage is a custom view. It contains one more column, that is, the index owner ):

$ cat all_object_usage.sqlCREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE(OWNER,INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING)ASselect u.name, io.name, t.name,decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),ou.start_monitoring,ou.end_monitoringfrom sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ uwhere i.obj# = ou.obj#and io.obj# = ou.obj#and t.obj# = i.bo#and io.owner# = u.user#/COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS 'Record of all index usage - developed by Daniel Liu'/GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"/CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE FOR SYS.V$ALL_OBJECT_USAGE /

Each time you use monitoring usage, the view is reset for a specific index. All previous usage information will be cleared and reset, And the next start time will be recorded. Each time you execute nomonitoring usage, no further monitoring will be performed; the end time during monitoring will be recorded. If you delete an index being monitored, the information about the index will be deleted from the V $ OBJECT_USAGE and V $ ALL_OBJECT_USAGE views.

Identifies all unused indexes in the database

This script will start monitoring all indexes:

start_index_monitoring.sh #!/bin/ksh# input parameter: 1: password# 2: SIDif (($#<1))thenecho "Please enter 'system' user password as the first parameter !"exit 0fiif (($#<2))thenecho "Please enter instance name as the second parameter!"exit 0fisqlplus -s < system/$1@$2set heading offset feed offset pagesize 200set linesize 100spool start_index_monitoring.sqlselect 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;' from dba_indexeswhere owner not in ('SYS','SYSTEM','OUTLN','AURORA\$JIS\$UTILITY\$');spool offexit!sqlplus -s < oracle/$1@$2@./start_index_monitoring.sqlexit!

This script will stop monitoring all indexes:

stop_index_monitoring.sh ##　#!/bin/ksh# input parameter: 1: password# 2: SIDif (($#<1))thenecho "Please enter 'system' user password as the first parameter !"exit 0fiif (($#<2))thenecho "Please enter instance name as the second parameter!"exit 0fisqlplus -s < system/$1@$2set heading offset feed offset pagesize 200set linesize 100spool stop_index_monitoring.sqlselect 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' NOMONITORING USAGE;'from dba_indexeswhere owner not in ('SYS','SYSTEM','OUTLN','AURORA/$JIS/$UTILITY/$');spool offexit!exitsqlplus -s < oracle/$1@$2@./stop_index_monitoring.sqlexit!

This script will generate a report for all unused indexes:

identify_unused_index.sh ##　#!/bin/ksh# input parameter: 1: password# 2: SIDif (($#<1))thenecho "Please enter 'system' user password as the first parameter !"exit 0fiif (($#<2))thenecho "Please enter instance name as the second parameter!"exit 0fisqlplus -s < system/$1@$2set feed offset pagesize 200set linesize 100ttitle center "Unused Indexes Report" skip 2spool unused_index.rptselect owner,index_name,table_name,usedfrom v\$all_object_usagewhere used = 'NO';spool offexit!

The following is an example of a report that is not indexed:

Unused Indexes ReportOWNER INDEX_NAME TABLE_NAME USEHR DEPT_ID_PK DEPARTMENTS NOHR DEPT_LOCATION_IX DEPARTMENTS NOHR EMP_DEPARTMENT_IX EMPLOYEES NOHR EMP_EMAIL_UK EMPLOYEES NOHR EMP_EMP_ID_PK EMPLOYEES NOHR EMP_JOB_IX EMPLOYEES NOHR EMP_MANAGER_IX EMPLOYEES NOHR EMP_NAME_IX EMPLOYEES NOHR JHIST_DEPARTMENT_IX JOB_HISTORY NOHR JHIST_EMPLOYEE_IX JOB_HISTORY NOHR JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY NOHR JHIST_JOB_IX JOB_HISTORY NOHR JOB_ID_PK JOBS NOHR LOC_CITY_IX LOCATIONS NOHR LOC_COUNTRY_IX LOCATIONS NOHR LOC_ID_PK LOCATIONS NOHR LOC_STATE_PROVINCE_IX LOCATIONS NOHR REG_ID_PK REGIONS NOOE INVENTORY_PK INVENTORIES NOOE INV_PRODUCT_IX INVENTORIES NOOE INV_WAREHOUSE_IX INVENTORIES NOOE ITEM_ORDER_IX ORDER_ITEMS NOOE ITEM_PRODUCT_IX ORDER_ITEMS NOOE ORDER_ITEMS_PK ORDER_ITEMS NOOE ORDER_ITEMS_UK ORDER_ITEMS NOOE ORDER_PK ORDERS NO

(