Monitoring Index Usage in Oracle Databases

With the plethora of database-centric applications available today, and with the performance problems they can generate, it can be a worthy effort to determine which vendor-created indexes are and are not being used. This is especially helpful if you’re working closely with the application vendor to improve their product.

Of course one way to do this is to set event 10046 at level 8 or 12 and let the trace files fly so they can be analyzed later for which indexes are being used by the application queries. And that could be a long and tedious process. One would think there is a better way to accomplish this.

There is.

Oh, I suppose you’d like to know this better way … it’s really rather simple:

Let Oracle do the work for you.

So let’s see how we tell Oracle to do this task for us so our job is much easier.

Oracle has provided a mechanism (since at least Oracle 8.1.6) to monitor an index for usage using

alter index <index_name> monitoring usage;

The results of that monitoring are found in the V$OBJECT_USAGE view, in a column, strangely enough, named USED. This isn’t a long, boring thesis on how, when, where, who and why the index in question was used, only that it either is or is not used. The ‘window’ spans the time period starting with the execution of the above-listed command and ends when the following is issued:

alter index <index_name> nomonitoring usage;

The data remains in the V$OBJECT_USAGE view until another monitoring ‘window’ is started at which point it is replaced.

So, let’s see an example of how this works. We’ll use the EMP table from the SCOTT/TIGER demonstration schema:

 

SQL>

SQL> —

SQL> — Create an index on the EMPNO column

SQL> — of the EMP table

SQL> —

SQL> create index emp_eno_idx

  2  on emp(empno);

 

Index created.

 

SQL>

SQL> —

SQL> — Let’s monitor the index to see if

SQL> — it’s being used

SQL> —

SQL> alter index emp_eno_idx monitoring usage;

 

Index altered.

 

SQL>

SQL> —

SQL> — Now, let’s run some queries

SQL> —

SQL> — First, let’s get everything from the

SQL> — EMP table

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

      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> —

SQL> — Obviously the index hasn’t yet been

SQL> — used

SQL> —

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

 

INDEX_NAME                     TABLE_NAME                     USE

—————————— —————————— —

EMP_ENO_IDX                    EMP                            NO

1 row selected.

 

SQL>

SQL> —

SQL> — So let’s run a qualified query and

SQL> — see if things change

SQL> —

SQL> — Since the DEPTNO column isn’t indexed

SQL> — the monitored index still shouldn’t be

SQL> — used

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> —

SQL> — And we see it isn’t

SQL> —

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

 

INDEX_NAME                     TABLE_NAME                     USE

—————————— —————————— —

EMP_ENO_IDX                    EMP                            NO

1 row selected.

 

SQL>

SQL> —

SQL> — Yet another qualified query, this time

SQL> — using the indexed column

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

1 row selected.

 

SQL> —

SQL> — We see the index is now being used, or at

SQL> — least it was for that last query

SQL> —

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

 

INDEX_NAME                     TABLE_NAME                     USE

—————————— —————————— —

EMP_ENO_IDX                    EMP                            YES

1 row selected.

 

SQL>

SQL> —

SQL> — We’ll try another query using that column

SQL> —

SQL> — Let’s set autotrace on to see if the index

SQL> — is being used in this example

SQL> —

SQL> set autotrace on

SQL> select * From emp where empno is null;

no rows selected

Execution Plan

———————————————————-

Plan hash value: 3712041407

—————————————————————————

 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time

—————————————————————————

   0  SELECT STATEMENT              1     87      0   (0)     

*  1   FILTER                                                 

   2    TABLE ACCESS FULL EMP      14   1218      3   (0) 00:00:01

—————————————————————————

Predicate Information (identified by operation id):

—————————————————

   1 – filter(NULL IS NOT NULL)

Note

—–

   – dynamic sampling used for this statement

 

Statistics

———————————————————-

          4  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

        353  bytes sent via SQL*Net to client

        239  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

 

SQL> set autotrace off

SQL>

SQL> —

SQL> — Since the index has been marked as used

SQL> — it remains in the USED state for this

SQL> — monitoring window even though the last

SQL> — query didn’t use the index at all

SQL> —

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

 

INDEX_NAME                     TABLE_NAME                     USE

—————————— —————————— —

EMP_ENO_IDX                    EMP                            YES

1 row selected.

 

SQL>

SQL> —

SQL> — Turn off the usage monitoring

SQL> —

SQL> alter index emp_eno_idx nomonitoring usage;

Index altered.

SQL>

SQL> —

SQL> — And the last generated data remains

SQL> —

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

 

INDEX_NAME                     TABLE_NAME                     USE

—————————— —————————— —

EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>

Letting Oracle monitor index usage is much easier than traipsing through piles of event 10046 trace files looking for index scans. I’m happy they’ve provided such a tool. But you may run across an index which is used but won’t be marked as such in V$OBJECT_USAGE (this is a rare occurrence but it can happen). How can this be? Oracle can use the statistics from the index in determining the best query plan, and when those statistics are gone (as when the index has been dropped) performance can suffer; the optimizer generates a decision tree when each query is hard parsed, and missing index statistics may direct the optimizer down a path it might not have taken when the statistics existed.

Oracle, in one respect, is correct in that the index in question hasn’t been read, but it did use the statistics to perform path elimination. So before heading straight for the ‘drop index’ command, it would be prudent to verify the index in question really isn’t being used in any way — this is why we have test systems, correct? Dropping the index on a test database, then verifying that no performance degradation occurs is, in my mind, a good idea. If — after the tests indicate an index may truly be unused — performance problems arise because that index is missing, it can be recreated to restore the application to its original lustre.

Some suggest that simply setting an index to UNUSABLE would provide the same conditions as dropping it, but disabling an index in that fashion doesn’t remove the statistics generated on that index, and if a query or process is using those statistics but is not actually accessing the index, the same conditions don’t exist and one could be led into a false sense of security that the index in question is truly unused. Yes, actual access to the index is not allowed, but since the index wasn’t being read to begin with (only the statistics were used by the CBO for cost calculations), I can’t accept that the same run-time conditions exist. Eventually the statistics will be outdated and no longer will be used but it could take a week, a month or longer for this to occur (depending upon system activity). For those DBAs in a hurry (and, face it, sometimes management IS in a hurry for results), setting an index to UNUSABLE may not be a valid course of action to discover whether it’s actually used or not.

Of course database administration cannot be ruled by rash acts, and relying upon a sole source of information (such as V$OBJECT_USAGE) can result in problems down the line. So, careful attention to detail is necessary, especially when managing the existence (or not) of an index [or table, or view, or …]. I like to follow a few simple rules:

1. Test, test, test.

2. Keep testing.

3. Never do anything you can’t undo.

Keeping to that methodology usually ensures I’m not in trouble later. And it keeps the end users happier.

I like happy end users.

 

David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning.

He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a ‘cascading’ set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles