Monitor index usage

Question

Is there a way to find if a a particular Oracle index was ever used by Oracle when executing a query?

We have a function based index, which I suspect is not getting used by Oracle and hence some queries are running slow. How could I find out if any query run against the database is using this query?

Answer 1

If the question is : if there are any queries that ever use the index?

ALTER INDEX myindex MONITORING USAGE;

Wait a few days/months/years:

SELECT *
FROM   v$object_usage
WHERE index_name = 'MYINDEX';

http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes004.htm#i1006905

Answer 2

If you're using some sort of IDE (e.g. Oracle's SQL Developer, PL/SQL Developer from Allround Automations, Toad, etc) each one of them has some way to dump the plan for a statement - poke around in the menus and the on-line help.

If you can get into SQL*Plus (try typing "sql" at your friendly command line) you can turn autotrace on, execute your statement, and the plan should be printed. As in

SQL> set autotrace on
SQL> select * from dept where deptno = 40;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
   2    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This assumes that your friendly neighborhood DBA has performed the necessary incantations to enable this feature. If this hasn't been done, or you just want One More Way (tm) to do this, try something like the following, substituting the query you care about:

SQL> EXPLAIN PLAN FOR select * from dept where deptno = 40;
Explained.

SQL> set linesize 132

SQL> SELECT * FROM TABLE( dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2852011669

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=40)

14 rows selected.

Share and enjoy.