Re: HowTo find out used (useful) and unused (usesless) indexes?
Date: Thu, 17 Apr 2008 05:45:26 -0700 (PDT)
Message-ID: <7065fe4a-9068-4326-970d-9e1640c133a3@a70g2000hsh.googlegroups.com>
On Apr 17, 4:38 am, Andreas Mosmann <mosm..._at_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 Received on Thu Apr 17 2008 - 07:45:26 CDT