9iR2: log which statements use a particular index

From: Ralph Graulich <graulich_at_reiff.de>
Date: Mon, 31 Mar 2008 12:35:02 +0200 (MEST)
Message-ID: <Pine.LNX.4.61.0803311223370.1003@fatima.ih.reiff.de>

Hi,

while trying to reduce the amount of indexes created on some large tables in an OLTP system, I try to figure out which indexes get rarely and what the statements look like that use those indexes.

I know I can monitor index usage, know about v$object_usage and v$sql_plan. What I am trying to accomplish is picking for example five to ten indexes that I know or assume of they get rarely used (but may be very important for particular queries anyway) and log all the statements that use those indexes in some way - to decide wether to drop the index, rearrange it or rearrange the query to use one of the other more frequently used indexes instead.

I thought about setting up a job that regulary checks v$sql_plan (or its base tables) for those indexes and log the statements into a table, as one should not use triggers on system tables etc. - but before reinventing the wheel: Is there a more clever or standard way (avoiding the "best practice" buzzword) to do this?

Best regards
... Ralph Graulich ...

---

reiff verlag kg                                   fon +49 781 5044003
Datenbankadministration                           fax +49 781 50483509
Marlener Strasse 9                              mail graulich_at_reiff.de
77656 Offenburg                                 * PGP-Key on request *


----------------------------------------------------------------------

Reiff Verlag KG, Offenburg, Amtsgericht Freiburg, HRA 471350 Komplementäre: Peter Reiff und Schwarzwaldverlag GmbH,

                  Offenburg (HRB 470298)

Geschäftsführer: Peter Reiff

---

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 31 2008 - 05:35:02 CDT