Re: Utilisation ratio for an index

Hi,

Not directly. You can use v$object_usage, if you issue a command to monitor its usage. It won't tell you how often it has been used, but only if it has been used. One odd item is that it will only show up as used if issued by the owner of the index. See below (XP, 10.2.0.2)...

SQL> alter index rep.t0406_i monitoring usage;

Index altered.

SQL> select used from v$object_usage where index_name = 'T0406_I';

no rows selected

SQL> select /*+ index(t0406 t0406_i) */ * from rep.t0406 where c = 5000;

         C

      5000

SQL> select used from v$object_usage where index_name = 'T0406_I';

no rows selected

SQL> connect rep/rep
Connected.
SQL> select used from v$object_usage where index_name = 'T0406_I';

USE
---

NO

SQL> select /*+ index(t0406 t0406_i) */ * from rep.t0406 where c = 5000;

         C

      5000

SQL> select used from v$object_usage where index_name = 'T0406_I';

USE
---

YES Other than that, you can select from v$sql_plan where the opeartion uses the index, or trace a session that will be accessing the table columns on which you built the index, but that is kind of kludgy as well. The last thing of which I can think is to put it in its own tablespace, and then use v$file_io to see how many reads are performed against it.

Regards,

Steve Received on Fri Apr 07 2006 - 09:39:55 CDT