 |
|
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
select
'alter index '||owner||'.'||index_name||' monitoring usage;'
from
Remote DBA_indexes
where
owner not in ('SYS','SYSTEM','PERFSTAT')
;
spool off;
@run_monitor
Next, we wait until a
significant amount of SQL has executed on our database, and then query
the new v$object_usage view:
select
index_name,
table_name,
mon,
used
from
v$object_usage;
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
--------------- --------------- --- ----
CUSTOMER_LAST_NAME_IDX CUSTOMER YES NO
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. |
 |
|
