v$object_usage invisible index used

I had a number of indexes on a table I made invisible, they were large indexes on a table that seemed totally useless, many were duplicates and as they accounted a large amount of the disk I was keen to see them gone and get some space back.  The application developers were happy for me to give it a try when I explained how making an index invisible in Oracle 11.2 works.

Invisible is not the same as unusable.  An invisible index is totally maintained, you are simply telling the CBO not to use it, and if you find you hit a performance problem you can quickly make it visible again without having to rebuild it.

alter index schema_owner.my_index_name invisible;
alter index schema_owner.my_index_name visible;

Dropping the indexes of this size though would be a huge issue for me to recreate so I wanted to be absolutely sure they weren't being used before I did that, so I decided to monitor them for a while.  This is easy too:

alter index schema_owner.my_index_name monitoring usage;

Now have a look at V$object_usage (as the object owner - if you do it as any other user you won't get any data returned)

select * from v$object_usage where index_name='MY_INDEX_NAME';
MY_INDEX_NAME MY_TABLE_NAME  YES NO date_and_time_monitoring_started

The USED=NO is good, this means Oracle hasn't used my index. But every morning when I got in the used had become 'YES'.

No matter how hard I tried I could not catch the sql that was causing the field to be changed

The sql pool was showing that DBMS_STATS was using a use_invisible_indexes hint but surely Oracle would have factored that in. This was the proof they had:

Alter system flush shared pool;

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
zero rows returned

select index_name, index_type, visibility  from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL VISIBLE
alter index schema_owner.my_index_name invisible;

select index_name, index_type, visibility  from dba_indexes
 where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL INVISIBLE
alter index schema_owner.my_index_name nomonitoring usage;
alter index schema_owner.my_index_name monitoring usage;

(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
NO

select index_name, last_analyzed  from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME 29/07/2012 15:46:08
exec dbms_stats.gather_table_stats('SCHEMA_OWNER','MY_TABLE_NAME',cascade => TRUE);
PL/SQL procedure successfully completed.

select index_name, last_analyzed from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME 09/08/2012 12:44:11
(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
NO

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
1 row returned

select a.sql_text
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
select /*+  no_parallel_index(t, "MY_INDEX_NAME")  dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad  no_expand use_invisible_indexes index(t,"MY_INDEX_NAME") */ count(*) as nrw,count(distinct sys_op_lbid(36991,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "SCHEMA_OWNER"."MY_TABLE_NAME" t where ("OSUSER" is not null) and (TBL$OR$IDX$PART$NUM........etc


So Oracle is using the index when dbms_stats is run but v$object_usage is not updated.

I was stuck here for a long time, every day I reset monitoring, every night it was updated but there was nothing in the sql pool to give me an idea as to why.  In the hope that it was being pushed out of the sql area I set a cron job to run every 5 minutes over night that ran the select sql_text query if USED in v$object_usage had changed to yes. I could see the point it changed but still I was getting no sql other than that from dbms_stats.

The application developers were adamant they did not use hints in their code anywhere. 

So what on earth was using it?  Then a number of weeks after I had been pulling my hair out I got cc'd on an email from the schedulers to the developers about a job that was over-running and the sql output they attached from the job named my index.  In fact being aware that I was 'working' on the index I was blamed for making the job over run.

It turns out that every night as a part of the batch the application rebuilt every partition of every index belonging to this table.  They weren't checking whether the index was visible or not.  I wouldn't expect them to check for that to be fair, what I still cannot fathom though is why they were rebuilding every subpartition of a local index on a partitioned table every night.  Local indexes manage themselves really.  Drop the table partition, the index partition goes too.  I can only think it's a code hangover from pre local indexes, unless you want to tell me different.

So here's the proof it was an index rebuild causing my headache:

alter system flush shared pool;

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
zero rows returned


select index_name, index_type, visibility from dba_indexes where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL VISIBLE
alter index schema_owner.my_index_name invisible;

select index_name, index_type, visibility from dba_indexes
where table_name='MY_TABLE_NAME';
MY_INDEX_NAME NORMAL INVISIBLE
alter index schema_owner.my_index_name nomonitoring usage;
alter index schema_owner.my_index_name monitoring usage;

(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
NO

alter index schema_owner.my_index_name REBUILD SUBPARTITION my_subpartition_name;

(as schema owner)
select USED from v$object_usage where index_name='MY_INDEX_NAME';
YES

select count(*)
from v$sqlarea a, v$sql_plan p
where p.hash_value=a.hash_value
and p.address=a.address
and p.object_name='MY_INDEX_NAME';
zero rows returned



Oracle have logged an enhancement request for v$object_usage to include a column that reports the action that causes USED to be altered, this would have saved me a whole lot of head scratching.

*Update April 2015

Enhancement request 'ADD A COLUMN TO V$OBJECT_USAGE IDENTIFYING THE TYPE OF ACTION THAT USED THE INDEX' has been reported as unlikely to implement.
But a bug has been raise about the rebuild of the subpartitions triggering an entry into v$object_usage Bug 20951043 : MISLEADING MONITORING INFORMATION FOR INDEX REBUILD OPERATIONS


I am disappointed with Oracle, I don’t think the approach is the best way to deal with it for customers but at least it is being partially dealt with. I'm seriously unimpressed that it them 2 years to even get to the point of acknowledging it.