Re: Help ...on Index column problem

Absolutly true!

Don't forget the great resource Oracle makes available for this kind of analysis: the V$SQLAREA view.

For every SQL statement in the cache, this view reveals: the number of times this statement has executed, the number of buffer gets,
the number of disk reads,
the number of rows processed,
and whether ot not Sort Happened.

CPU usage is proportional to buffer gets.

High buffer gets per row processed indicates the use of a Bad Index (not selective for the actual where clause). Explain Plan these cases and you will see.

High rows per execution with low disk reads per execution indicates good buffer hit ratio; BUT, why do you keep reading the same rows over and over?

Johnny wrote:

> Choosing indexes is a time consuming processes. You must ask yourself
> why you would index.
>
> 1. To implement unique constaints.
> 2. To increase read performance
> 3. To increase over all performance.
> 4. To speed up sorts
> 5 ...
>
> The technique I use is access path analisys. Basicaly it comes down to.
>
> Examine every DML with a where condition. Take into account the
> execution frequency and the count of the participating tables. The
> decide if an index would be benificial given the reasons why you would
> be indexing in the first place.
>
> As a rule of thumb; primary, seconday and foreign keys are always
> good candidates.
>
> Johnny Verhoeven
> agichen_at_my-dejanews.com wrote:
> >
> > Hi,seniors,
> > I have a problem about index.
> > How can I find that which column should be indexed to get better
> > performance ?
> > Is there any method available to find out all tables' column
> > that should be index or not ??
> >
> > junior in Oracle.
> > Rgds,
> > Agi
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Apr 14 1999 - 19:49:16 CDT