Oracle Invisible Indexes and Index Usability

August 6, 2009

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     30
 
SQL> 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.

» See All Articles by Columnist James Koopmann








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers