MORE_INFO
--------------------------------------------------------------------
In this particular scenario, Undo Advisor indicates that there is enough space allocated in the
undo tablespace to handle the types and volumes of queries run against this database.
Index Usage
Although indexes provide a tremendous benefit by speeding up queries, they can have an impact
on space usage in the database. If an index is not being used at all, the space occupied by an
index can be better used elsewhere; if we don??™t need the index, we also can save processing time
for insert, update, and delete operations that have an impact on the index. Index usage can be
monitored with the dynamic performance view V$OBJECT_USAGE. In our HR schema, we
suspect that the index on the JOB_ID column of the EMPLOYEES table is not being used. We
turn on monitoring for this index as follows:
SQL> alter index hr.emp_job_ix monitoring usage;
Index altered.
We take a quick look at the V$OBJECT_USAGE view to make sure this index is being monitored:
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING
--------------- --------------- --- ---- -------------------
EMP_JOB_IX EMPLOYEES YES NO 07/24/2007 10:04:55
190 Oracle Database 11g DBA Handbook
The column USED will tell us if this index is accessed to satisfy a query. After a full day of
typical user activity, we check V$OBJECT_USAGE again and then turn off monitoring:
SQL> alter index hr.
Pages:
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345