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.
