Re: Help with Tuning

Walt,

There is only one diffinitive way of checking on the value of indexes: using the explain plan/tkprof. By submitting each of the applications queries to an explain plan, you can see whether it is using the indexes or not. Chances are you have some redundant indexes.

However, some initial considerations.

1. Are you regularly analyzing the database. If you aren't, performance may suffer anyway. It's worth getting that sorted just to see if things improve.
2. If you have two indexes, one on columns a, b, c and another on a, b, then the second is not needed. (The redundant index must use the same columns as the other, in the same sequence starting from the first in the first index - this is true even where the index is on column a.)
3. If you have indexes on foreign keys, check whether these are really needed. Before 7.1.6 these were neede to prevent excessive table locking. If the parent table is rarely modified, then the chances are that these are not needed.

Some practical suggestions for an easy start.

Len

>Hello-
> I've recently inherited a very large and complex data model, and I
>need to begin evaluating the appropriateness of indexes. My main
>challenge is one of volume; this model has around 270 tables, and just
>under 700 indexes. It's the back-end for a software package which is
>designed to be customized on install, and could serve anywhere from a
>few dozen to tens of thousands of users.
> I'm hoping to avoid tuning based on complaints of poor performance.
> Does anyone have any suggestions or thoughts on evaluating this large
>a number of indexes?
> Thanks in advance.
>
>Walt Maguire
>
Received on Sat Nov 04 2000 - 11:05:30 CST