9iR2: log which statements use a particular index
- From: Ralph Graulich <graulich@xxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 31 Mar 2008 12:35:02 +0200 (MEST)
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@xxxxxxxx
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
----------------------------------------------------------------------
Other related posts:
