Re: how do i know if an index is being/ever used ?

From: Shari Bishop <sbishop_at_tasc.usda.gov>
Date: 1997/04/30
Message-ID: <3367A9CE.3D58@tasc.usda.gov>#1/1

Engenoil wrote:
>
> hello all,
> does oracle have the ability to determine whether or not an index is / has
> been used ?
> how do dba's know if they are maintaining 100's of useless indexes ? (we've
> inherited, heh-heh)
> thank you all in advance.
> daryl & bruno

There is a utility that allows DBAs to analyze each SQL statement and place the results into a table called 'PLAN_TABLE'. The utility (xplainpl.sql for version 6.0) is usually in /$ORACLE_HOME/rdbms/admin or something like that. The utility actually traces how the Oracle Optimizer would process the SQL statement (without actually executing it), thus allowing DBAs to see which indexes are being utilized, etc... To interpret the results you need another utility/script :

select lpad(' ',3*(level-1))||operation||                      
':'||options||' ('||object_name||')'||                         
' '||DECODE(id,0,'COST='||position) "Query Plan"                 
from plan_table                                       
CONNECT BY PRIOR id = parent_id                                        

START WITH id = 1

or

undefine 1                                                              
set echo off term on  feed on pause off ver off                        
host rm xpl.lst                                                      
spool xpl                                                               
select decode(id,0,' ', 
lpad(' ',2*level-1))||level||'.'||position||' '||                
operation||' '||options||' '||object_name||' '||               
object_type||' '||                                
decode(id,0,'Cost = '||position)" QUERY_PLAN"                 
from plan_table                                            
connect by prior id = parent_id                              
and statement_id = upper('&&1')                        
start with id = 1 and statement_id = upper('&&1');                
spool off                                                          
set term on                                                         
prompt                                                          
prompt Output from EXPLAIN PLAN is in file called "xpl.lst" . . .     
prompt                                                 
undefine 1           

You may want to get an Oracle Tuning book which explains how to read the results. If you are using version 7 then change the statement: start with id = 1 to 0.

I hope this helps.....Good luck !

Shari Bishop Received on Wed Apr 30 1997 - 00:00:00 CDT