Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at email@example.com. Please include your name and telephone number in your email.
This month’s question comes to us from Adam in Northern California: Over the years we’ve added a lot of indexes to our database schema. I suspect some of the indexes are not even being used at all. Is there a way to find out if an index is being used?
Roger Schrag of the Database Specialists team responds: Indexes can dramatically speed up queries and DML statements by enabling Oracle to efficiently find the desired rows without having to scan every row in a table. However, an index that sorts data in a way that is never useful or is redundant to another index may not provide any benefit.
You can tell Oracle to set a flag when a specific index gets used. This is called “monitoring” and is set at the index level. Take the following statement as an example:
ALTER INDEX my_index MONITORING USAGE;
If you now query v$object_usage, you will see an entry for this index. The entry will show that the index is being monitored for usage, that the index has not been used yet, and that monitoring began at such-and-such a time.
As soon as any query or DML statement uses the index, Oracle will update the entry in v$object_usage to show that the index has been used. You will not be able to tell how many times the index has been used or when it was last used, but you will be able to tell that it was used at least once.
At any time you can turn off monitoring of the index with the following:
ALTER INDEX my_index NOMONITORING USAGE;
Oracle will then update the entry in v$object_usage to show that the index is no longer being monitored and that monitoring ended at such-and-such a time. If you later turn monitoring back on for this index, Oracle will overwrite the existing entry, so you will no longer have a record of the last time you monitored the index and whether or not the index was used during that monitoring period.
You could quickly write a PL/SQL block that finds every index in your application schema and turns monitoring on. Then you could wait a period of time and run a similar code piece to turn monitoring off. At this point you would be able to query v$object_usage to see if any indexes did not get used during the monitoring period.
A user with DBA privileges can turn on and off monitoring of indexes in other schemas. However, the v$object_usage view will only show you information about the indexes in your current schema. (See Metalink document 160712.1 for information about creating a v$all_object_usage view to see monitoring information for all indexes in the database.)
By using Oracle’s index monitoring feature, it is pretty easy to determine if there are indexes in your application schema that are never used. Unused indexes take up space and slow down inserts, updates, and deletes. But before you go dropping indexes that you think are never used, make sure there isn’t some critical process that runs infrequently and needs these otherwise-unused indexes. Also, please note that the cost-based optimizer might use statistics from an index without using the index itself. Thus dropping an index can still cause execution plan change in statements that did not use the dropped index.
Database Specialists’ Senior Staff Consultant Terry Sutton returns as a featured speaker at the Hotsos Symposium, an event that hosts top Oracle performance analysts from around the world. The event takes place March 4-8 in Dallas, Texas.
Terry will give a presentation in which he will demonstrate how Oracle 10g tracing enhancements help DBAs diagnose performance problems and make applications run faster in connection pooled environments. Although the SQL trace facility has been available in Oracle for many years, it had previously not been as useful on systems using Oracle’s shared server architecture or application server connection pooling. For more information about the symposium event or Terry’s presentation, see https://portal.hotsos.com/events/SYM07.
Don’t miss the latest white paper from Roger Schrag of Database Specialists. What You Can Do When Your Database Runs out of Temp Space opens with a review of how Oracle manages sorting operations. It goes on to present two techniques a DBA can use to understand how space in the temporary tablespace is being used and how users are being impacted by a full temporary tablespace.
These techniques can help DBAs address chronic or intermittent shortages of temporary space. You’ll find the paper in the Resources section of our website at http://dbspecialists.wpengine.com/presentations.html#temp_space.
With the continued growth of Oracle on Linux, DBAs should take a fresh look at the website maintained by Werner Puschitz, (http://www.puschitz.com). The site contains lots of resources authored by Puschitz, including the following sampling:
Installing Oracle Databases on Linux