Database Reference
In-Depth Information
SQL> ALTER INDEX dept_id_pk REBUILD;
Index altered.
SQL> SELECT * FROM v$object_usage WHERE table_name='DEPARTMENTS';
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
---------------- ----------- ---------- ---- ------------------- --------------
DEPT_ID_PK DEPARTMENTS NO YES 10/04/2007 17:21:54
DEPT_LOCATION_IX DEPARTMENTS YES NO 10/04/2007 17:21:55
This behavior is a bit surprising, since an index rebuild is not the kind of index usage a DBA
would be interested in. Other DDL statements, such as
ANALYZE
INDEX
index_name
VALIDATE
STRUCTURE
or
ANALYZE
TABLE
table_name
VALIDATE
STRUCTURE
CASCADE
have no influence on the
index status in
V$OBJECT_USAGE
, although they do access index segments.
Indexes Used by DML
Finding out which indexes were used by DML statements is what really counts. Several factors
make this more intricate than you might suspect. We haven't yet considered the case where
index monitoring on an index that was marked as used is switched off. By calling
MONITOR_
SCHEMA_INDEXES
with the parameter
MONITORING
set to
FALSE
, we switch off index monitoring for
all indexes in schema HR.
SQL> EXEC :success_counter:=site_sys.monitor_schema_indexes(monitoring=>false, -
> failed_counter=>:failed_counter);
FAILED_COUNTER
--------------
0
SUCCESS_COUNTER
---------------
17
Since the index rebuild already switched off monitoring on one of the indexes and the
function only considers indexes that do not yet have the desired status, the value of the variable
SUCCESS_COUNTER
is 17. Let's take a look at the contents of
V$OBJECT_USAGE
.
SQL> SELECT table_name, index_name, monitoring AS monitored,
used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS');
TABLE_NAME INDEX_NAME MONITORED USED START_MONITORING END_MONITORING
----------- ----------------- --------- ---- ------------------- -------------------
DEPARTMENTS DEPT_ID_PK NO YES 10/04/2007 17:21:54
DEPARTMENTS DEPT_LOCATION_IX NO NO 10/04/2007 17:21:55 10/04/2007 18:17:58
EMPLOYEES EMP_DEPARTMENT_IX NO YES 10/04/2007 17:21:55 10/04/2007 18:17:58
EMPLOYEES EMP_EMAIL_UK NO NO 10/04/2007 17:21:55 10/04/2007 18:17:58
…
