BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








Identifying Unused Indexes in Oracle9i

Oracle Tips by Burleson Consulting

One of the most serious causes of poor DML performance is the existence of unused indexes. Until Oracle9i, there was no way to identify those indexes that were not being used by SQL queries. This section describes the Oracle9i method that allows the Remote DBA to locate and delete unused indexes.

The approach is quite simple. Oracle9i has a tool that allows you to monitor index usage with an alter index command. You can then query and find those indexes that are unused and drop them from the database.

Note: This script only works in Oracle9i.

A Sample Index Monitoring Session

Let’s say that we want to know if our customer_last_name_idx index is being used by SQL queries. We can issue the following alter index command to turn on monitoring for the index.

SQL> alter index customer_last_name_idx monitoring usage;

Here is a script that will turn on monitoring of usage for all indexes in a system:

set pages 999;
set heading off;

spool run_monitor.sql

   'alter index '||owner||'.'||index_name||' monitoring usage;'
   Remote DBA_indexes
   owner not in ('SYS','SYSTEM','PERFSTAT')

spool off;


Next, we wait until a significant amount of SQL has executed on our database, and then query the new v$object_usage view:


Here we see that v$object_usage has a single column called used, which will be set to YES or NO. Sadly, this will not tell you how many times the index has been used, but this tool is useful for investigating unused indexes.

    INDEX_NAME               TABLE_NAME     MON USED
    ---------------         --------------- --- ----

Tuning Index Contention with Hidden Parameters

Oracle has numerous “hidden” parameters that are used to change the internal behavior of Oracle. As you may know, all hidden parameters begin with an underscore character. Whenever index contention is experienced (as evidenced by process waits), adjusting the following parameters may be helpful.

  •  _db_block_hash_buckets Defaults to 2x db_block_buffers but should be the
    nearest prime number to the value of 2x db_block_buffers.

  • _db_block_hash_latches Defaults to 1024 but 32,768 is a better value.

  • _kgl_latch_count Defaults to zero which is means 1 + number of CPUs. Lock contention can often be reduced by resetting this value to 2*CPUs +1.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.




Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter