News zu Oracle

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

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 aktua­li­siert werden, belegen Platz im Buffer Cache, auf dem Storage der Datenbank und letzt­endlich auch im Backup. Daher ist es durchaus ratsam, sie von Zeit zu Zeit auf ihre Daseinsberechtigung hin zu prüfen. Denn ungenutzte Indizes bringen faktisch nur Nachteile mit sich. 

Für dieses Indexmonitoring kannst du als DBA auf den Befehl “alter index … monitoring usage” zurück­greifen. Ein mit “monitoring” markierter Index erscheint daraufhin in der v$object_usage View des Indexowners:

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

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 23 18:02:31 2023
Version 19.10.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


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    
------------------------- -------------------- --- --- -------------------
EMP_DEPARTMENT_IX         EMPLOYEES            YES NO  02/01/2023 12:00:20
Die Spalte MON(ITORING) zeigt an, dass der Index EMP_DEPARTMENT_IX gemonitort wird, entspre­chend der Information in USE(D) aller­dings 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   
--------------------- -------------------- --- --- -------------------
EMP_DEPARTMENT_IX     EMPLOYEES            YES YES 02/01/2023 12:00:20

Lässt du das Indexmonitoring nun über einen reprä­sen­ta­tiven Zeitraum laufen, erhältst du ein gutes Gefühl dafür, welche Indizes wahrscheinlich ohne Nutzen für die Datenbank sind und gedroppt werden können. 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/2023 12:00:20 02/01/2023 12:08:08

Das USED-Flag wird zurück­ge­setzt, wenn das Monitoring für den betref­fenden Index wieder aktiviert wird. Es gibt alter­nativ zum sofor­tigen Löschen (vermeintlich) ungenutzter Indizes noch die charmante Alternative einer Karenzzeit mit Nutzung des “alter index … invisible”. Damit wird der betref­fende Index für den Optimizer “unsichtbar” und damit nicht mehr genutzt. Der Index selbst wird aller­dings weiterhin aktuell gehalten. Zeigt die Praxis, dass der Index tatsächlich noch erfor­derlich ist, kann er mittels “alter index … visible” sofort wieder aktiviert werden, ohne dass du ihn neu aufbauen musst. Das Löschen eines Index dagegen sollte wohl überlegt sein, da seine Wiederherstellung im Falle großer Indizes oder auf stark frequen­tierten Tabellen in der Standard Edition nur schwer im laufenden Betrieb und in der Enterprise Edition (alter index … rebuild online) nur mit erheb­lichen Performanceeinbußen erfolgen kann.

Fazit

Indizes sind für die Leseperformance einer Datenbank elementar wichtig. Da ihre Haltung und Pflege auf der anderen Seite aber auch einen nennens­werten Aufwand generiert, gilt beim Einsatz von Indizes der Grundsatz: So viel wie nötig, so wenig wie möglich. Diesem Ziel kann man mit Index-Monitoring ein gutes Stück näher kommen.

Hier findest du weitere inter­es­sante Posts zum Thema Oracle aus unserem News und Insights Bereich.

icon-arrow_right_medium-violet-blue.svg

Share this article

Facebook 
Twitter 
LinkedIn 
XING 
WhatsApp 
Email