Locating unnecessary indexes

Locating un-used and rarely use indexes is an important DBA task.

Unnecessary indexes cause DML overhead and consume disk space while providing no benefit to your SQL.

Itís not uncommon for infrequently-used indexes to consume more resources than they save when they are used.  For highly active tables, each and every DML statement requires Oracle to keep the index current, and you must carefully weigh the relative saving of the index on the infrequent SQL against the continuous overhead of maintaining the index.

While the cost of the disk required to store the index is usually negligible.  Infrequently-used indexes do not impose a heavy overhead for static tables that are rarely updated, but they can wreak havoc on volatile table, causing a measurable amount of operational overhead for very little benefit.
Remember, indexes always add overhead to DML operations, and it may often be a good practice for weekly and monthly jobs to drop the index and consider one of these alternative methods:

1. Create the index, run the job and then drop the index.

2. Allow the infrequent job to choose another plan that does not involve the index.

The below method can be used to find infrequently-used indexes.


In Oracle 10g and beyond, you can accurately count the number of times that an index was invoked by using the STATSPACK or AWR tables.

Read below the techniques I use for locating un-used indexes:



And now for something completely different

Computer science is full of narcissists, people whose ego knows no bounds.
Read my notes below on how to spot an office narcissist:


Try Ion for Oracle database trends!

Using the principles of decision support systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle. Ion quickly isolates the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release. Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics is the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access. To try Ion for free, see here:



Need a Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal settings that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.


Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author. Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training. Just call 800-766-1884 for details, and check out our on-site Oracle training catalog at the following link:



There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.

If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.

Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. See here for details and a full qualifications list: