Within
the many development shops I’ve worked in, it has been my observation that many
of the decisions to create an index in production have not always been soundly
rooted in explain plan analysis or even looking at application code. More often
than not, someone just thinks a column or set of columns look good enough for
an index to be placed on them. This is a completely off-the-cuff reaction to
hunches and folklore. What often happens, ok always happens, is that there are
hundreds of indexes cluttering up databases that either never get use or are
detrimental to the execution performance of SQL. As DBAs, it is our
responsibility to clean up these unused or performance hogging indexes. But
where do we start? I’d like to propose two mechanisms within Oracle that could
help you in this endeavor.
There
are basically two scenarios that come into play here:
1.
We must determine if an index is
even being used. If an index is not being used then it is very easy to just
drop the index and we are done.
2.
In the case of an index being used,
or possibly creating an index that we think will be used, it is a little more
difficult to determine that index’s impact on database performance.
For
the first criteria (determining if an index is even used) we can easily set up
some monitoring of database indexes to tell us if the index in question is
being used. The key here is obviously to determine and monitor a large enough
period of time that is adequate to catch an index when it should be used. For instance,
you might want to monitor for an hour, day, week, or business quarter depending
on the table an index is associated with. If you perform end of quarter
processing you will have to set your monitoring period for a full quarter.
So
how do you go about monitoring an index usage? It is very easy and just
requires the use of the ALTER INDEX command with the MONITORING USAGE clause.
SQL> ALTER INDEX pk_emp MONITORING USAGE;
Index altered.
SQL> ALTER INDEX ix_emp_sal MONITORING USAGE;
Index altered.
Now
when you SELECT, UPDATE, DELETE (no INSERT) from a table where the index in
question is used for a lookup then the index will be flagged as being used in
the V$OBJECT_USAGE view.
SQL> select * from emp where empno = 7844;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —— ——— —- ——— —– —- ——
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30SQL> SELECT v.index_name, v.table_name,
v.monitoring, v.used,
start_monitoring, end_monitoring
FROM v$object_usage v, user_indexes u
WHERE v.index_name = u.index_name;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
———- ———- — — ——————- ——————-
PK_EMP EMP YES YES 04/28/2009 10:16:51
IX_EMP_SAL EMP YES NO 04/28/2009 10:17:01
Ok,
so that was easy. Clearly if the previous select statement was the only select
statement ever issued against the EMP table, we should probably drop the
IX_EMP_SAL index.
In
the case of an index being used, or possibly creating an index that we think
will be used, we have to be a little bit cleverer before we take drastic
actions such as dropping or creating. To help in these instances Oracle has given
us a new feature called INVISIBLE indexes that allow us to play around with
hiding indexes from applications so that they won’t be used but are still
maintained through INSERTs, UPDATEs, and DELETEs. To make an index invisible
you can either CREATE or ALTER the index INVISIBLE. For our IX_EMP_SAL index
above:
SQL> create index ix_emp_sal on emp(sal) INVISIBLE;
SQL> alter index ix_emp_sal INVISIBLE;
When an index is made
INVISIBLE applications running will no longer “see” them and will not use them
to satisfy any DML operation. An invisible index is not seen by the optimizer
and won’t be used for any execution plans unless there is a hint specified, the
session is set to use invisible indexes, or if the database is set to use all
invisible indexes. This provides DBAs with some fantastic opportunities. With
the use of a new init.ora parameter called optimizer_use_invisible_indexes you
can toggle session or system wide use of invisible indexes—giving you a unique
opportunity to test the effects of new indexes before completely moving into a
production environment or even flip on indexes during end of month or yearly
processing that would normally throw normal SQL off during any other time of
the year. Using an invisible index is as easy as setting this parameter or
adding a hint to your SQL (temporarily of course).
SQL> alter system set optimizer_use_invisible_indexetrue;
SQL> alter session set optimizer_use_invisible_indexetrue;
SQL> select * /*+ index (emp ix_ep_sal) */ ename from emp where sal=1500;
To make an index visible
again just issue the ALTER index statement with the VISIBLE key word:
SQL> alter index ix_emp_sal INVISIBLE;
In addition, if you want
to find out which indexes in your database are visible or invisible you can
just query the VISIBILITY column of the DBA_, USER_, or ALL_INDEXES views.
SQL>select index_name, visibility
from dba_indexes
where index_name=’IX_EMP_SAL’;
INDEX_NAME VISIBILIT
————– ———
IX_EMP_SAL INVISIBLE
One
word of caution with the new INVISIBLE index feature would to be attentive of
others creating or altering indexes invisible without your knowledge. If you
have older SQL for looking at index structures you may now need to add the check
of this VISIBILITY column or else you may not see the full picture of those
indexes that are being maintained by DML but not contributing to selectivity.
This could turn into a hidden nightmare for applications when performing a lot
of INSERTs , UPDATEs, or DELETEs.
Index
usability has always been somewhat of a black art—never knowing if dropping an
index we think isn’t needed will create performance problems down the road for
an application. Now with monitoring the usability of an index and the ability
to modify indexes as invisible, DBAs have at their fingertips the ability to at
least test and validate index usability a bit better.
