Re: Measuring index usage

From: Jantah <jantah_at_hot.mail.com>
Date: Mon, 25 Sep 2000 20:23:45 +0200
Message-ID: <ngvuss81e9i0ti8nvb17ecm7b09tobod03@4ax.com>

On Mon, 25 Sep 2000 15:38:17 +0200, Frank wrote:

Right, maybe I should have been a bit more specific. By 'used' I actually mean 'used for retreival'. Come to think of that, what are other uses of an index? What could be a good reason to have an index that is not used?

Never mind, I was hoping that there is a system view with something like an 'index scans' column, so I could display all indexes that are never used (instead of investingating all indexes seperately using trace files). Apparently there is no such view.

To be more specific (as promised), there is an index on three columns on a table, and I would like an index on the second column in this list. So, I can create a new index, or I can switch [1] the first two columns on the existing one. I prefer the last option (there will be no additional overhead when updating/inserting/deleting compared to the old situation). I would like to know if the current index is used for the first column, because that won't be possible after I switched the columns.

I suppose that you are suggesting to trace the main processes on the database, tkprof the trace files with explain=xx/xx and than see if the index pops up in the .prf files? Some labour involved there, and still a bit of trial and error, because I can never be sure all functionality is used (but then again, I'll notice soon enough when the performance plummits ;-)

And for your other remarks:

"There must be a reason for that index"
Our supplier delivers several programs to access the database, and we are not using all of them. There is no such thing as an "index set" per application, so the index might be there for no reason at all in our situation.
Beside that, I think that any database will need some tailoring on the indexes, based on the way the application is used, and the specific data for the customer.

"well, some developers"
This afternoon I ran a small trace on a very common user procedure. The file ends with:

     188  SQL statements in trace file.
     184  unique SQL statements in trace file.

Static SQL anyone?

Jan

[1] afaik, Oracle can only use an index on the first columns that are in the index. So, if an index is created on column1, column2 and column3 (in that order), Oracle can use it for column1, for column1 and column2 and for all three columns, but not for column2 alone.

>Not used?
>1) Sometimes indexes are not used, and for a good reason.
>2) Non used indexes only slow down upon inserts, updates, deletes.
> Not so for retrieval
>3) No, there is no view - but it's not all trial and error. Trace and
>explain plan
> are the keywords you are looking for. And, of course:
>4) the documentation that came with the system (you *do* have
>documentation?)
> There must be a reason for that index... (well, some developers....)

><jantah_at_my-deja.com> schreef in berichtnieuws 8qnivd$cj9$1_at_nnrp1.deja.com...
>> I would like to identify indexes that are not used to access tables (and
>> thus only slow the system down). Is there a system view or other method
>> to indetify these indexes, or is this a trial and error process?
>>
>> (Using Oracle 7.3.3)

-- 
Jan

jantah_at_hot.mail.com
...and you know what to do with "hot.mail", right?

Received on Mon Sep 25 2000 - 13:23:45 CDT