Identifizierung ungenutzter Indices
Autor: Vilma Plum
eingetragen: Montag, 17. August 2009 um 08:05 Uhr (34/2009 Kalenderwoche)
geändert: Montag, 17. August 2009 um 08:05 Uhr (34/2009 Kalenderwoche)
Keywords: Index Nutzung performance Überwachung
Kategorien: DB: Oracle,
Text:
Ungenutzte Indices bedeuten unnötigen Verwaltungsaufwand und Platzverbrauch, deshalb ist es sinnvoll, sie zu löschen. Voraussetzung ist allerdings herauszufinden, welche Indices genutzt werden und welche nicht. Ab Version 9.0 ist dies relativ einfach möglich mit der Erweiterung des ALTER INDEX-Befehls:
• ALTER INDEX MONITORING USAGE;
• ALTER INDEX NOMONITORING USAGE;
und der neuen DD-View
• V$OBJECTUSAGE
In V$OBJECTUSAGE werden Indices eingetragen, deren Nutzung überwacht wird oder wurde; in der Spalte MONITORING wird angezeigt, ob der Index zur Zeit überwacht wird, und die Spalte USED gibt an, ob der Index seit dem (letzten) Einschalten der Überwachung genutzt wurde.
Mit der Option MONITORING USAGE wird die Überwachung der Nutzung eingeschaltet, mit NOMONITORING USAGE ausgeschaltet.
Beispiel:
Die im folgenden benutzte Ausgangstabelle BIGEMP ist eine Vervielfältigung der Daten aus SCOTT.EMP. Die Ausgabe der Spalten indexname und tablename wurde auf je 12 Zeichen formatiert.
ALTER TABLE bigemp ADD(nr NUMBER);
UPDATE bigemp SET nr = ROWNUM;
COMMIT;
ALTER TABLE bigemp
ADD CONSTRAINT pkbigemp PRIMARY KEY(nr);
CREATE INDEX idxempno ON bigemp(empno);
SQL> SELECT * FROM V$OBJECT_USAGE;
Es wurden keine Zeilen ausgewählt
ALTER INDEX pkbigemp MONITORING USAGE;
ALTER INDEX idxempno MONITORING USAGE;
SQL> SELECT * FROM V$OBJECT_USAGE;
INDEXNAME TABLENAME MON USE STARTMONITORING ENDMONITORING
IDXEMPNO BIGEMP YES NO 10/13/2003 12:10:00
PKBIGEMP BIGEMP YES NO 10/13/2003 12:08:11
SELECT * FROM bigemp WHERE nr = 7; (Ausgabe weggelassen)
SQL> SELECT * FROM V$OBJECT_USAGE;
INDEXNAME TABLENAME MON USE STARTMONITORING ENDMONITORING
IDXEMPNO BIGEMP YES NO 10/13/2003 12:10:00
PKBIGEMP BIGEMP YES YES 10/13/2003 12:08:11
ALTER INDEX IDXEMPNO NOMONITORING USAGE;
ALTER INDEX PKBIGEMP NOMONITORING USAGE;
SQL> SELECT * FROM V$OBJECT_USAGE;
INDEXNAME TABLENAME MON USE STARTMONITORING ENDMONITORING
IDXEMPNO BIGEMP NO NO 10/13/2003 12:10:00 10/13/2003 12:13:00
PKBIGEMP BIGEMP NO YES 10/13/2003 12:08:11 10/13/2003 12:13:21
Soll beispielsweise eine Applikation auf ungenutzte Indices hin überprüft werden, so bietet sich an, mit Hilfe eines Skripts für alle Indices die Überwachung einzuschalten, dann in einem repräsentativen Zeitraum und Umfang die Applikation zu nutzen und dann das Monitoring über ein weiteres Skript wieder auszuschalten. Die entsprechenden Skripten werden am einfachsten über Spool-Dateien erzeugt.
Beispiel:
SET FEEDBACK OFF HEADING OFF ECHO OFF
SET PAGESIZE 0
SPOOL monitoringein.sql
SELECT 'ALTER INDEX ' ||owner||'.'||indexname||' MONITORING USAGE;'
FROM ALLINDEXES WHERE owner = 'SCOTT';
SPOOL OFF
@monitoringein
Arbeiten in der Datenbank...
SPOOL monitoringaus.sql
SELECT 'ALTER INDEX ' ||owner||'.'||indexname||' NOMONITORING USAGE;'
FROM ALLINDEXES WHERE owner = 'SCOTT';
SPOOL OFF
@monitoringaus
SELECT indexname FROM v$objectusage WHERE used = 'NO';
INDEX_NAME
IDX_EMPNO