Re: index statistics
Date: 28 Apr 2004 04:50:38 -0700
Message-ID: <b66d8bd5.0404280350.cf78151_at_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 USESTART_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- ---------- PK_EMP EMP YES NO04/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 USESTART_MONITORING END_MONITORING
------------------------------ ------------------------------ --- --- ------------------- ---------- PK_EMP EMP YES YES04/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 - 13:50:38 CEST