News:
Index-Monitoring – Aufspüren und Droppen von ungenutzten Indizes in Datenbanken

Für das Indexmonitoring kann der Befehl "alter index ... monitoring usage" genutzt werden.

Icon Unternehmen

In komplexen Datenbanksystemen existieren häufig eine Vielzahl von Indizes. Teilweise nehmen sie mehr Platz in Anspruch als die eigentlich indizierten Daten selbst. Indizes müssen bei jeder DML-Operation aktualisiert werden, belegen Platz im Buffer Cache, auf dem Storage der Datenbank und letztendlich auch im Backup. Daher ist es durchaus interessant, von Zeit zu Zeit die Menge der vorhandenen Indizes darauf hin zu überprüfen, ob sie auch tatsächlich benutzt werden. Denn ungenutzte Indizes bringen faktisch nur Nachteile mit sich. Für dieses Indexmonitoring kann der DBA auf den Befehl "alter index ... monitoring usage" zurückgreifen. Ein mit "monitoring" markierter Index erscheint daraufhin in der v$object_usage View des Indexowners:

[oracle@dr-02(db11gR2 orcl2) ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 1 12:00:14 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

SQL> ALTER INDEX HR.EMP_DEPARTMENT_IX MONITORING USAGE;

Index altered.

SQL> connect hr/hr
Connected.
SQL> select * from v$object_usage;

INDEX_NAME                TABLE_NAME           MON USE START_MONITORING    END_MONITORING
------------------------- -------------------- --- --- ------------------- -------------------
EMP_DEPARTMENT_IX         EMPLOYEES            YES NO  02/01/2012 12:00:20

Die Spalte MON(ITORING) zeigt an, dass der Index EMP_DEPARTMENT_IX gemonitort wird, entsprechend der Information in USE(D) allerdings bislang noch nicht verwendet wurde. Greift nun eine Abfrage auf den Index zu, ändert sich das USED-Flag entsprechend:

SQL> select last_name from employees where department_id=110;

LAST_NAME
-------------------------
Higgins
Gietz

SQL> select * from v$object_usage;

INDEX_NAME            TABLE_NAME           MON USE START_MONITORING    END_MONITORING
--------------------- -------------------- --- --- ------------------- -------------------
EMP_DEPARTMENT_IX     EMPLOYEES            YES YES 02/01/2012 12:00:20

Lässt man das Indexmonitoring nun über einen repräsentativen Zeitraum laufen, erhält man ein gutes Gefühl dafür, welche Indizes wahrscheinlich ohne Nutzen für die Datenbank sind und gedroppt werden könnten. Das Monitoring übersteht im Übrigen auch den Neustart der Datenbank. Es muss also am Ende des gewünschten Monitoringzeitraumes explizit wieder abgeschaltet werden:

SQL> ALTER INDEX HR.EMP_DEPARTMENT_IX NOMONITORING USAGE;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME            TABLE_NAME           MON USE START_MONITORING    END_MONITORING
--------------------- -------------------- --- --- ------------------- -------------------
EMP_DEPARTMENT_IX     EMPLOYEES            NO  YES 02/01/2012 12:00:20 02/01/2012 12:08:08

Das USED-Flag wird zurückgesetzt, wenn das Monitoring für den betreffenden Index wieder aktiviert wird. Nutzer der Oracle Database Version 11g oder 11gR2 haben alternativ zum sofortigen Löschen vermeintlich ungenutzter Indizes noch die charmante Alternative einer Karenzzeit mit Nutzung des "alter index ... invisible". Damit wird der betreffende Index für den Optimizer "unsichtbar" und damit nicht mehr genutzt. Der Index selbst wird allerdings weiterhin aktuell gehalten. War das "Entfernen" des Index tatsächlich eine Fehlentscheidung, kann er mittels "alter index ... visible" sofort wieder aktiviert werden, ohne ihn neu aufbauen zu müssen. In Version 10gR2 oder darunter sollte das Löschen eines Index dagegen wohl überlegt sein, da hier ein Index-Rebuild erforderlich wird, der im Falle großer Indizes in der Standard Edition nur schwer im laufenden Betrieb und in der Enterprise Edition (alter index ... rebuild online) nur mit erheblichen Performanceeinbußen erfolgen kann.