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#
/
posted by Alex Gorbachev @ 23:45
2 comments
2 Comments:
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 :).
Post a Comment
<< Home