Monitoring
Index Oracle
9i Feature
:-
|
Database Version |
Oracle 9.x |
|
Article Revision Date |
21-OCT-2003 |
|
Web |
|
|
Author |
Sameer Wadhwa |
Monitoring an Index determine weather the particular index
is used optimally or not, so that you can drop un-necessary indexes.
Enable Monitoring By :
ALTER
INDEX <INDEXNAME> MONITORING USAGE ;
Disable Monitoring By:
ALTER INDEX
<INDEXNAME> NOMINTORING USAGE;
Database View to Monitor Index
V$OBJECT_USAGE
SQL>
desc v$object_usage
Name
----------------------------
INDEX_NAME /* Index name */
TABLE_NAME /* Table Name */
MONITORING /* Monitoring on YES/NO */
USED /* INDEX USED YES/NO */
START_MONITORING /* DATE AND TIME OF START MONITORING */
END_MONITORING /* DATE AND TIME OF END MONITORING */
Example:
Start
Monitoring
(1) ALTER TABLE SCOTT.EMP MONITORING USAGE;
Check Status
(2) SQL> select * from
V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
---------------
---------------- --- --- ------------------- --------------
EMPIDX2 EMP YES NO 04/17/2003 15:20:03
Note : USED “NO” indicate, index is
not used
(3) Run you application for a sufficient time to used the table and
associated indexes
check status
again
(4) SQL> select * from V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
---------------
---------------- --- --- ------------------- --------------
EMPIDX2 EMP YES YES 04/17/2003 15:20:03
Note: USED “YES” indicate your
application did you the index.
Switch off
monitoring
(5) ALTER TABLE SCOTT.EMP
NOMONITORING USAGE;
Check the
status
(6) SQL> select * from
V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------------
---------------- --- --- ------------------- --------------
EMPIDX2 EMP YES YES 04/17/2003 15:20:03 04/17/2003 35:30:03
Note
: END_MONITORING is populated with the time when you run the NOMONITORING USAGE
Important Points:
n Value is reset for a specific index ,
every time you specify monitoring usage.
n LOB and IOT cannot be monitored
ALTER
INDEX NGSDMS60.SYS_IL0000023423C00004$$ MONITORING USAGE
*
ERROR
at line 1:
ORA-22864:
cannot ALTER or DROP LOB indexes
ALTER
INDEX NGSDMS60.SYS_IOT_TOP_23552 MONITORING USAGE
*
ERROR
at line 1:
ORA-25176:
storage specification not permitted for primary key
Download Scripts
n
MonX.zip
MonX.zip contains two files
index_start_monitor.sql and Index_Stop_Monitor.sql
Copyright© Oracle Techniques
www.SamOraTech.com
Disclaimer:
The author does not
guarantee that this information is error-free.
If any errors are found, please report them
to author at SamOracle@Yahoo.com
Ask and Solve
Database Problems at OraTechSupportGroup
What do you
think about Oracle
Techniques?