Re: Oracle Tools Wanted

From: Pete Johnson <P.Johnson_at_ste0426.x400.icl.co.uk>
Date: Wed, 11 Nov 1998 12:01:17 -0000
Message-ID: <36497abc.0@145.227.194.253>

>> which indexes have been used ??

One solution that will work in an OLTP situation is to put the database in trace mode - bump up the size of the largest allowable trace file - run a typical TP situation - probably best to focus function by function.

Then run TKPROF on the results. If you are using a rule based optimiser then the indexes that are used in this process will be detailed. If you are using a cost based optimiser then this isn't very reliable. BE AWARE - third party product usually have the index there for a reason so removing them doesn't seem a very good idea.

There are also scripts that you can run that will tell you how effective each index is - Oracle supply these and they are documented somewhere - don't ask me where. Received on Wed Nov 11 1998 - 06:01:17 CST