Re: index statistics

From: Dhana <dhanasekar.p_at_db.com>
Date: 28 Apr 2004 04:50:38 -0700
Message-ID: <b66d8bd5.0404280350.cf78151@posting.google.com>

Hi,

You have to monitor the index using
"alter index <index_name> monitoring usage;" and see the V$object_usage view.

Read the example below and look for "used" column. If the index was used
the column contains "YES".

In my first query, I used ename column in my where clause which is not part of index but in second one, I used empno column which is part of index.

SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> 
SQL> 
SQL> 

SQL>
SQL> select table_name,index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
DEPT                           PK_DEPT
EMP                            PK_EMP

SQL> select * from v$object_usage;

no rows selected

SQL> alter index PK_EMP monitoring usage;

Index altered.

SQL> select * from emp where ename='SCOTT';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL      
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000       
            20

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE

START_MONITORING END_MONITORING

------------------------------ ------------------------------ --- ---
------------------- ----------
PK_EMP                         EMP                            YES NO 

04/28/2004 19:43:42

SQL> select * from emp where empno = 7788;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL      
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7788 SCOTT      ANALYST         7566 19-APR-87       3000       
            20

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE

START_MONITORING END_MONITORING

------------------------------ ------------------------------ --- ---
------------------- ----------
PK_EMP                         EMP                            YES YES

04/28/2004 19:43:42

Rgds
Dhana
"Putz Ronald" <rputz_at_etm-ag.com> wrote in message news:<1083065125.475962_at_newsmaster-03.atnet.at>...
> Hy!
>
> Try:
> select * from all_indexes
>
> Adios
>
>
> "Heiko" <heiko_at_technologie-management.net> schrieb im Newsbeitrag
> news:7bc3b1a6.0404270125.5ac109b3_at_posting.google.com...
> > Hello,
> >
> > is there any way (v$-view) to get informaion about how often an index hast
> been
> > used since of starting the Database?
> >
> > Thanks for help
> > Heiko
Received on Wed Apr 28 2004 - 06:50:38 CDT