Tom You mean except invalidation of dependent objects there will be no further overhead like performance degradtion .Please can u further clarify it.
Thanx and Regards
Shameem

Tom,
I tried to use index monitoring on partitioned indexes but the v$object_usage view does not give me any rows:
In the example below ad_region_idx is a partitioned index on asset_detail_tbl (which ofcourse is partitioned).

SQL> select * from v$object_usage;                          
                                                            
no rows selected                                            
                                                            
SQL> alter index idw1.ad_region_idx monitoring usage;       
                                                            
Index altered.                                              
                                          
SQL> select distinct region from idw1.asset_detail_tbl;                   
                                                            
REGION   
-------------------------                           
APAC                                                        
JAPAN                                                    
SQL> set autot traceonly exp                                              
SQL> /                                                      
Execution Plan                                                            
----------------------------------------------------------                
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3249 Card=1963200 By    
          tes=27484800)                                                   
                                                                          
   1    0   PARTITION LIST (ALL)                                          
   2    1     SORT (UNIQUE) (Cost=3249 Card=1963200 Bytes=27484800)       
   3    2       PARTITION RANGE (ALL)                                     
   4    3         BITMAP INDEX (FAST FULL SCAN) OF 'AD_REGION_IDX'        
SQL> set autot off  
SQL>  select * from v$object_usage;       
no rows selected 

What am I missing? 
Thanks for your help 

what is missing is the test case...

(you are logged in as the owner of the index right??)

ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte@ORA9IR2> create bitmap index t_idx on t(x) local;

Index created.

ops$tkyte@ORA9IR2> insert into t select to_date( '13-mar-2003', 'dd-mon-yyyy')+mod(rownum,3), rownum, 'x' from all_objects;

30687 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx monitoring usage;

Index altered.

ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> select /*+ index( t t_idx ) */ y from t where x = 42;

Y
-------------------------
x


Execution Plan
----------------------------------------------------------
   0
SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=71 Bytes=1917)


   1    0
  PARTITION RANGE (ALL)


   2    1
    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T' (Cost=16 Card=71 Bytes=1917)


   3    2
      BITMAP CONVERSION (TO ROWIDS)


   4    3
        BITMAP INDEX (SINGLE VALUE) OF 'T_IDX'





ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE
------------------------------ ------------------------------ --- ---
START_MONITORING    END_MONITORING
------------------- -------------------
T_IDX                          T                              YES YES
10/20/2005 16:35:56
 

I was not logged in as the owner of the index. That was the problem. It is bit unusual that an oracle user with DBA role can not see this info but there must be a good reason for this.
Thank you.

Hi Tom,

I hope I am putting this question to a appropiate thread.

Once you start monitoring an index, you only going to see if it was used or not in v$object_usage. Is there a way, that you can find out the exact sql statements that used the index, provided that we have not re-started the Database during the period we monitor the index??

Thanks,

Kandy_Train