Database Reference
In-Depth Information
application has been exercised. As a consequence, it is best to use the functionality in the early
stages of development and test instead of taking the risk to drop an index in a production data-
base that later turns out to be required for end-of-business-year processing.
A less intrusive way of finding used indexes, which does not cause a performance penalty
due to recursive SQL, is using Statspack at level 6 or higher (see Chapter 25). However, this
approach is significantly more risky, since Statspack samples SQL statements and their execu-
tion plans, such that you may not have any data on the usage of certain indexes, simply since
the Statspack snapshots were taken at a time when no execution plans indicating these indexes
were cached in the shared pool.
Source Code Depot
Table 4-1 lists this chapter's source files and their functionality.
Table 4-1.
Index Monitoring Source Code Depot
File Name
Functionality
monitor_schema_indexes.sql
Contains function
MONITOR_SCHEMA_INDEXES
for switching index
usage monitoring on all indexes in a schema on or off. Calls
the script
view_index_usage.sql
to create the view
INDEX_USAGE
,
since it is used by the function.
view_index_usage.sql
Contains the view
INDEX_USAGE
for accessing index usage infor-
mation for an entire database instead of merely for the current
schema as with
V$OBJECT_USAGE
.
