Re: Measuring index usage

In article <ngvuss81e9i0ti8nvb17ecm7b09tobod03_at_4ax.com>,   Jantah <jantah_at_hot.mail.com> wrote:
> 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?
>

Since you mention a supplier and a set of access programs you are likely treading on thin ice wanting to eliminate "unused" indexes. In many cases modification of the underlying database structure is grounds for warranty and support agreement invalidation. Without specific written consent from the supplier/software vendor you could eliminate any and all support for the application suite, whether or not you are using all of the supplied programs.

> 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.

The indexing from the software vendor reflects the vendor's choice based upon the overall query set used to access the data. That you are not using all of the supplied programs does not give you carte blanche to change indexing at will. I wouldn't touch the configuration if I were you.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.