On Apr 17, 4:38*am, Andreas Mosmann <mosm...@expires-30-04-2008.news-
group.org> wrote:
> Hi,
>
> Is there a way to find out what indexes are used(useful)/unused(useless)
> while the last few days/weeks?
> If possible so tell me a way via data dictionary without using any
> external tools.
>
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

Let Oracle do that for you:

SQL> create index emp_eno_idx
2 on emp(empno);

Index created.

SQL>
SQL> alter index emp_eno_idx monitoring usage;

Index altered.

SQL>
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 09-DEC-82
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83
1100 20

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7900 JAMES CLERK 7698 03-DEC-81
950 30
7902 FORD ANALYST 7566 03-DEC-81
3000 20
7934 MILLER CLERK 7782 23-JAN-82
1300 10

14 rows selected.

SQL>
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME TABLE_NAME USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX EMP NO

SQL>
SQL> select * from emp where deptno = 30;

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7900 JAMES CLERK 7698 03-DEC-81
950 30

6 rows selected.

SQL>
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME TABLE_NAME USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX EMP NO

SQL>
SQL> select * from emp where empno < 7400;

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20

SQL>
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME TABLE_NAME USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX EMP YES

SQL>

Using the 'alter index <indexname> monitoring usage;' directive
Oracle will keep track of whether or not an index is actually used and
will do so until you issue an 'alter index <indexname> nomonitoring
usage;' command. A simple query of V$OBJECT_USAGE will reveal which
indexes are and are not used. After an 'alter index <indexname>
nomonitoring usage;' directive is issued the collected data remains in
the V$OBJECT_USAGE view; it isn't removed for the objects in question
until another 'alter index ... monitoring usage;' command is called:

SQL> alter index emp_eno_idx nomonitoring usage;

Index altered.

SQL> select index_name, table_name,used from v$object_usage;

INDEX_NAME TABLE_NAME USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX EMP YES

SQL>


David Fitzjarrell