v$object_usage empty?
Yesterday, one of my collegues asked why he couldn’t see anything in v$object_monitoring view when he enabled monitoring of indexes. I was scratching my head trying to remeber the trick because I recalled that I’d had a similar problem and reason wasn’t obvious. I couldn’t help him at that time as my memory didn’t serve well at this one.
Today I’ve come across Kirti Deshpande’s post in Oracle-L that reminded me the issue. Apparently, v$object_usage contains information only about objects in the current user. This time I decided that it should get deeper into my mind so that next time I remeber it. I looked in the docs (Oracle Database Reference 10g Release 2) and figured out there is documentaion bug:
“You can use this view to monitor index usage. The view displays statistics about index usage gathered from the database. All indexes that have been used at least once can be monitored and displayed in this view.”
Nothing about objects of current user. I also checked in “Administrator’s Guide” and and there is no mentioning of this pecularity.
Kirti also gave a very nice advice to create improved version of v$object_usage. Excellent solution to simplify our lives when many schemas are involved. In fact, if you setup your environment properly, users owning the objects will be most probably locked so you can’t easilly login as that user. ALTER SESSION SET CURRENT_SCHEMA wouldn’t help because V$OBJECT_USAGE includes filter on userenv(’SCHEMAID’) which looks like is not affected by setting current_schema.
So thanks to Kirti here is the solution:
create or replace view V$ALL_OBJECT_USAGE (OWNER ,INDEX_NAME ,TABLE_NAME ,MONITORING ,USED ,START_MONITORING ,END_MONITORING ) as select u.name , 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.user$ u , 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# and u.user# = io.owner# /
Hi Alex,
to my mind the question is:
why do I monitor usage of indexes?
The answer is: because I want to find out, if I have indexes which I do not need because they atre never ued by teh application and just consume loads of space on disk.
Now who could be interested in this question =>
1. the dba who wants to reclaim disk space
2. the dba who wants to get rid of indexes which slow down the performance because they need to be maintained.
In both cases it is the dba and if she/he enables table monitoring he/she will be able to see the used indexes.
On hint:
do not drop an idex before the last report has been run. Maybe there is one report which is only run once a year or every n years which needs the index!
Hi Lutz,
Your logic is correct. But it’s a good security practice to lock accounts that own the objects and use separate application accounts having minimal set of DML/SELECT privileges on the object. If account is locked than you cannot login as this user. Of course, being DBA everything is possible but it’s a hassle. Especially, if database have several schema hosting the objects (very common in our environments) than you need to login as different user several time - again hassle.
Anyway, even if rationale behind having V$OBJECT_USAGE is valid than is should be at least documented and the view better called V$USER_OBJECT_USAGE.
Thanks for the hint. To generalize it - monitoring should be enabled for, at least, one full cycle of product life, be it day, week, or month. In addition, it’s a good idea to get in touch with development team and find out why the index was created in the first place (well, might not always be possible :).
Monitoring of indexes in 10g is quite useless, isn’t it? Because you scheduled your automatic statistics gathering with GATHER_STATS_JOB and because analyzing the table (DBMS_STATS) changes “Used” column on YES for all indexes, there is no easy way to track index usage for a month. Before each analyze you have to check if the index was used or not, and after analyze you should reset monitoring with ALTER INDEX my_index MONITORING USAGE;
How to automate something like this?
Darko,
I think the statement “Monitoring of indexes in 10g is quite useless” is too bold but you do raise a good point. I personally prefer to disable GATHER_STATS_JOB for several reasons and gather stats in controllable manner. If you want to automate what you propose, then PL/SQL is your friend but you know that already. I’m not aware of similar functionality out of the box.
Hi all,
I just tested running GATHER_STATS on a table and index stayed unused(NO didn’t change to YES). Am I missing something? I did use cascade=>true, so Oracle analyzed table and indexes using DBMS_STATS. I used 10.2.0.4 database
@Olga Yudin
Index usage is tracking whether an index been used by a user query. Stats gathering has nothing to do with index usage. Well, not directly.
@Alex Gorbachev
Sorry, but is not in contradiction with the previous posts that you answered to Olga Yudin? However, I tried and gathering statistics set (rightly) V$OBJECT_USAGE.USED = YES
If you apply patch 67698910 om 10.2.0.4 then dbms_stats (and analyze) operations will not modify the usage status during object monitoring. This patch is necessary to use object monitoring on 10g. It is fixed in 10.2.0.5.
Mark,
thanks for your information but I have not found the patch 67698910. Are you sure that is the right number?
However, the “index monitoring usage” can be dangerous, look at: http://richardfoote.wordpress.com/2008/09/12/index-monitoring-and-foreign-keys-caution-kid-a/