IND$, V$OBJECT_USAGE, and Index Monitoring - Secrets of the Oracle Database - page 45

Index Usage Monitoring Case Study
We will use the sample schema HR (see Oracle Database Sample Schemas manual) as our play-
ground for the case study. First, I will enable index usage monitoring on all indexes in schema
HR. For this purpose, the source code depot contains the function MONITOR_SCHEMA_INDEXES
(file monitor_schema_indexes.sql ), which may be used to switch index usage monitoring on all
indexes in a schema on or off. Before proceeding, I will acquaint you with this function.

Function MONITOR_SCHEMA_INDEXES
The syntax of function MONITOR_SCHEMA_INDEXES is as follows:

FUNCTION site_sys.monitor_schema_indexes (
ownname VARCHAR2 DEFAULT NULL,
failed_counter OUT NUMBER,
monitoring BOOLEAN DEFAULT TRUE
) RETURN INTEGER AUTHID CURRENT_USER;

Parameters

Parameter Description

ownname Schema name on which to operate. If NULL , the current schema is used.

failed_counter

Returns the number of times an ALTER INDEX statement failed due to
“ORA-00054 resource busy and acquire with NOWAIT specified.” This
happens when another session holds an incompatible lock on the base
table of an index, such as when a transaction on the table is open.

monitoring

Used to switch monitoring on ( TRUE ) or off ( FALSE ).

Usage Notes
The function returns the number of indexes that were successfully altered. If the value of
FAILED_COUNTER is greater than zero, it is best to wait until open transactions have completed
and to rerun the procedure until FAILED_COUNTER=0 is returned, i.e., no objects to be altered
remain.

Examples
Switch on index monitoring on all indexes in schema SH.

SQL> VARIABLE success_counter NUMBER
SQL> VARIABLE failed_counter NUMBER
SQL> EXEC :success_counter:=site_sys.monitor_schema_indexes(ownname=>'SH', -
> failed_counter=>:failed_counter);