Re: Tracing use of indexes

Hi Jeroen,

With the little background you provided I can only give general tips. I don't know whether you have the source or not. You will need to look in whatever the optimizer included in the statement parser is considered the best way to process your statements. From sqlplus 3.3 onwards you can issue the statement
set autotrace on explain and after every statement you will get a description of the access path. The plan_table must be either in your schema, or created under sys or system, and a public synonym on it. The plan_table is created with the script

<ORACLE_HOME>/rdbms/admin/utlxplan.
The optimizer has two modes of operation: rule based and cost based. First is based on general rules, second is based on estimations and computation of how much io it will cost. To use the second way there need to be statistics in the dictionary. The mos efficient procedure to get statistics for all objects in your schema is

execute dbms_utility.analyze_schema('<schema name>', 'COMPUTE')

Some general hints:
Eight indexes usually is a bad idea. They probably will not be used ALL. Usually you should have an index on the primary key and indexes on all foreign keys and that should be sufficient. Oracle will determine the selectivity of the index. Generally speaking do not index columns with very few keys ('M'/'F' situations), selectivity will be low. Hth

Sybrand Bakker, Oracle DBA
postbus_at_sybrandb.demon.nl

jeroen wrote:

> Hello,
>
> I hope sonebody can help with the following probleem. I have a well
> used table with 8 indexes defined on that. I would like to know if all
> are being used and if i know a way i would like to check all indexes.
> Does somebody know how to find out, tools ?
> Is auditing an option and if so how ?
> Thanks in advance,
>
> Jeroen
Received on Thu Aug 06 1998 - 13:57:06 CDT