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,inde x_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_MONITORIN G 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_MONITORIN G END_MONITORING
------------------------------ ------------------------------ --- ---
------------------- ----------
PK_EMP EMP YES YES
04/28/2004 19:43:42
Rgds
Dhana
"Putz Ronald" <rp***@etm-ag.com> wrote in message news:<10******* ********@newsma ster-03.atnet.at>...
Hy!
Try:
select * from all_indexes
Adios
"Heiko" <he***@technolo gie-management.net> schrieb im Newsbeitrag
news:7b******** *************** ***@posting.goo gle.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