Re: Utilisation ratio for an index

From: Micha³ Kuratczyk <kura_at_lj.pl>
Date: Fri, 07 Apr 2006 17:01:34 +0200
Message-ID: <e15ust$mn5$1@abg.com.pl>

stevedhoward_at_gmail.com wrote:
> One odd item is that it will only show up as used if issued by the owner
> of the index.

Define "odd". :->

Considering the definition of v$object_usage it would be odd if it wasn't working this way:

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#

Interesting article on the topic of index usage monitoring: http://www.jlcomp.demon.co.uk/index_usage.html

-- 
Michal Kuratczyk

Received on Fri Apr 07 2006 - 10:01:34 CDT