Posted by Every …. single …. time….
Why does index monitoring make me scratch my head and charge off to google so many times…Well, I’m over it, so time to put it on my blog (even though its already on many other places) so I do not get caught out anymore 🙂
It always starts like this:
- Turn on index monitoring on some indexes
- Wait…
- Come back later, and get ready to check on my results
- Then this happens…
SQL> select * from v$object_usage;
no rows selected
And I panic…Did I run the commands on the wrong database ? Did they not run properly ? Do I need to flush a pool ? etc etc etc
And after plenty of wasted minutes…I end up digging up the source for the v$object_usage view
create or replace force view sys.v$object_usage
(
index_name
,table_name
,monitoring
,used
,start_monitoring
,end_monitoring
) as
select io.name
,t.name
,decode(bitand(i.flags
,65536)
,0, 'NO'
,'YES')
,decode(bitand(ou.flags
,1)
,0, 'NO'
,'YES')
,ou.start_monitoring
,ou.end_monitoring
from sys.obj$ io
,sys.obj$ t
,sys.ind$ i
,sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;
And there it is…you can only see rows for the schema you are currently in….Grrrr.
So from now on, I’m gonna to always create a new view
create or replace view sys.an_object_usage_definition_that_works as
select io.name index_name
,t.name table_name
,decode(bitand(i.flags
,65536)
,0, 'NO'
,'YES') monitoring
,decode(bitand(ou.flags
,1)
,0, 'NO'
,'YES') used
,ou.start_monitoring
,ou.end_monitoring
from sys.obj$ io
,sys.obj$ t
,sys.ind$ i
,sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;
but I have to wait until we can have 37 character long object names 🙂
Learning is not a spectator sport 
So now the first think you find on google will be your own blog entry.
So does “EXEC dbms_stats.flush_database_monitoring_info” have any effect on this?
Just for the record – in 12c the view v$object_usage is deprecated. 😦 There is already a task on my ToDo list, to adapt some scripts, to use USER_OBJECT_USAGE and DBA_OBJECT_USAGE instead of v$object_usage …
an_object_usage_definition_that_works – and what about owner? 🙂
This is covered off under MOS note 160712.1.