Oracle hat mit 12cR2 die beiden Views eingeführt, damit der DBA die Nutzung von Indizes besser analysieren kann. In diesem Artikel wollen wir die Nutzung der Views – es gibt keine vergleichbaren ALL oder USER Views – und ihre Limitationen unter die Lupe nehmen.
Der Grund für die Auseinandersetzung mit diesem Thema ist ein alter Bekannter: Jeder nicht genutzte Index macht DML auf die entsprechenden Tabellen deutlich langsamer, weil die Indizes unabhängig von deren Nutzung gepflegt werden müssen. Daher ist es ein Teil des Applikationstunings, nicht genutzte Indizes zu identifizieren und zu löschen.
Kleiner Rückblick – was war vor Oracle 12cR2?
Beginnend mit Oracle 10g war es möglich einen Index zu MONITOREN:
ALTER INDEX {MONITORING | NOMONITORING} USAGE;Ob der Index genutzt wurde, konnte man in der Spalte USED der View V$OBJECT_USAGE feststellen. Diese hat allerdings lediglich YES oder NO enthalten – nicht wirklich hilfreich. Somit wurde Index MONITORING sehr selten eingesetzt.
Aktualität von DBA_INDEX_USAGE
Bevor wir uns den Inhalt von DBA_INDEX_USAGE im Detail ansehen, müssen wir wissen, wie aktuell und vollständig die Informationen darin sind. Da DBA Views in der Regel Informationen aus dem Data Dictionary ausgeben, sind diese nicht ganz aktuell. Oracle aktualisiert die View alle 15 Minuten (flush durch Hintergrundprozesse). Wann der letzte Flush stattgefunden hat, findet man in der View V$INDEX_USAGE_INFO.
V$INDEX_USAGE_INFO: Spalten und deren Inhalt
In der View V$INDEX_USAGE_INFO kann man nachvollziehen wann die DBA_INDEX_USAGE letztmalig aktualisiert wurde und welche Einstellungen für die Index Usage Statistik aktuell bestehen.
| Spalte | Inhalt |
| INDEX_STATS_ENABLED | Info ob Index Usage Statistiken aktuell ENABLED (=1) sind. Kontrolliert mittels „_iut_enable“ |
| INDEX_STATS_COLLECTION_TYPE | Detailgrad der Statistiksammlung (ALL=0, SAMPLED=1 ist der DEFAULT). Kontrolliert mittels „_iut_stat_collection_type“ |
| ACTIVE_ELEM_COUNT | Anzahl der aktiven Indizes seit dem letzten Flush |
| ALLOC_ELEM_COUNT | Anzahl der Indexeinträge |
| MAX_ELEM_COUNT | Maximale Anzahl von Indizes die gleichzeitig aktiv getrackted werden können. Kontrolliert mittels „_iut_max_entries“ |
| FLUSH_COUNT | Anzahl der erfolgreichen Flushes seit Instance-Start |
| TOTAL_FLUSH_DURATION | Dauer der Flush Laufzeiten |
| LAST_FLUSH_TIME | Zeitpunkt des letzten Flushes |
| STATUS_MSG | Error Meldung, sofern der Flush nicht erfolgreich durchgeführt werden konnte. |
| CON_ID | Multitenant Container ID |
Die aktuellen Einstellungen der undokumentierten Instanzparameter, welche die Index Usage beeinflussen:
col Parameter for A40
col "Default Value" for a16
col "Session Value" for a16
col "Instance Value" for a16
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE')
AS IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',
3,'IMMEDIATE','FALSE') AS IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '_iut%';Parameter Default Session Instance IS_SE IS_SYSTEM
------------------------- ------- -------- -------- ----- ---------
_iut_enable TRUE TRUE TRUE FALSE IMMEDIATE
_iut_max_entries TRUE 30000 30000 FALSE IMMEDIATE
_iut_stat_collection_type TRUE SAMPLED SAMPLED TRUE IMMEDIATEDamit diese Funktionalität nicht zu viel Overhead und Last generiert, gibt es zwei Einstellungen, deren Default möglicherweise nicht ausreichend ist:
- „_iut_max_entries“
- „_iut_stat_collection_type“
„_iut_max_entries“ beschränkt die Anzahl der Index Nutzungen auf maximal 30.000 innerhalb des 15 Minutenintervalls. Wenn man jetzt sehr viele Indizes und eine große Anzahl von kurzlaufenden Statements nutzt, kann diese Grenze schon nach wenigen Sekunden erreicht sein. Dadurch können Zugriffe auf seltener genutzte Indizes nicht erkannt werden. Ob man diese Grenze hinaufsetzen muss, kann man mit folgender Query überwachen:
select ALLOC_ELEM_COUNT, MAX_ELEM_COUNT, LAST_FLUSH_TIME
from V$INDEX_USAGE_INFO;
ALLOC_ELEM_COUNT MAX_ELEM_COUNT LAST_FLUSH_TIME
---------------- -------------- ----------------------
0 30000 26.10.22 14:23:37,226Kommt der Wert von ALLOC_ELEM_COUNT den MAX_ELEM_COUNT nahe, oder sind die Werte sogar gleich, sollte man überlegen „_iut_max_entries“ höher zu setzen.
Weit interessanter ist jedoch der Parameter „_iut_stat_collection_type“. Mit der Einstellung SAMPLED werden nicht alle Benutzer-Statements (Oracle interne Statements werden in den meisten Fällen nicht berücksichtigt) für die Statistik herangezogen. Viele kurzlaufende Statements mit nur einer Execution fallen damit unter den Tisch. Auch die Nutzung von PK/UK/FK Constraint Indizes für die Sicherstellung der referenziellen Integrität werden nicht berücksichtigt.
Ändert man die Einstellung auf ALL (=0), erhöht das den Overhead durch Index Usage Statistik. Daher sollte man diese nur für einen Zeitraum aktivieren, den man genau beachten möchte (zB: Monats oder Jahresabschluss).
In der Regel ist folgende Abfrage von V$INDEX_USAGE_INFO relevant:
SET LINESIZE 120 TAB OFF
COLUMN last_flush_time FORMAT A30
COLUMN module FORMAT A10
COLUMN action FORMAT A50
SELECT decode(index_stats_enabled,1,'ENABLED',0,'DISABLED')
AS IDX_STAT_STATUS,
decode(index_stats_collection_type,1,'SAMPLED',0,'ALL')
AS COLL_TYPE,
active_elem_count,
last_flush_time
FROM v$index_usage_info;
IDX_STAT COLL_TY ACTIVE_ELEM_COUNT LAST_FLUSH_TIME
-------- ------- ----------------- ---------------------
ENABLED SAMPLED 0 26.10.22 14:38:37,530 Manuelles Flush der DBA_INDEX_USAGE
In der Praxis ist es nicht wichtig, dass alle Informationen auch wirklich in DBA_INDEX_USAGE aktuell sind. Wenn man sich mit dem Thema auseinander setzt, möchte man aber nicht regelmäßig 15 Minuten warten, bis der nächste Flush erfolgt ist. Leider hat Oracle bisher (bis inkl. Oracle 19c) keine offizielle FLUSH Funktion zur Verfügung gestellt. Daher gibt es einen kleinen Trick, wie man das FLUSH sofort veranlassen kann.
Der MMON erkennt, dass ein FLUSH der Index Usage Statistik erfolgen soll und delegiert diese Aufgabe an einen seiner MMON_SLAVES. Das kann man mit Hilfe von SQLPLUS und ORADEBUG auch manuell erzwingen.
Ermitteln der PIDs eines MMON_SLAVE Prozesses
select s.module, s.action, p.pid
from v$session s, v$process p
where s.paddr=p.addr and s.program LIKE '%(M0%'
order by s.program;
MODULE ACTION PID
---------- ----------------------------- ----
MMON_SLAVE Automatic Report Flush 44
MMON_SLAVE KDILM background CLeaNup 62
MMON_SLAVE Automatic Report Flush 68
MMON_SLAVE Intensive AutoTask Dispatcher 48Flush der Index Usage veranlassen
In einer neuen SQLPLUS Session (AS SYSDBA) anmelden und SQLPLUS mit diesem Prozess verbinden (wir nehmen einfach den ersten in der Liste).
oradebug setorapid 44
Oracle pid: 44, Unix process pid: 9833, image: oracle@oel7.intern.dbmasters.at (M000)Jetzt kann man dem MMON_SLAVE eine Aufgabe übermitteln:
oradebug call keiut_flush_all
Function returned 60089650Wichtig: In dieser SQLPLUS Session bitte nicht arbeiten, sondern nur die MMON Flush Aufrufe durchführen! Als letztes überprüfen wann das letzte Flush erfolgt ist:
select systimestamp, LAST_FLUSH_TIME from v$index_usage_info;
SYSTIMESTAMP LAST_FLUSH_TIME
------------------------------- ---------------------
26.10.22 14:56:38,221816 +02:00 26.10.22 14:56:34,639Wie man sieht, ist der letzte Flush vor einigen Sekunden erfolgt.
Hinweis: Wenn die MMON_SLAVE Prozesse längere Zeit Idle sind, werden diese gestoppt und bei Bedarf neu gestartet. Dadurch kann sich die PID im laufenden Betrieb ändern!
Beispiel-Tabellen und Indizes
Damit wir die Indexnutzung überprüfen können, verwenden wir ein eigenes Schema mit einigen Tabellen und Indizes. Für die Tabelle DIU_MASTER greifen wir auf DBA_TABLES zurück und für DIU_DETAIL entsprechende Inhalte aus DBA_TAB_COLUMNS, damit wir eine Master / Detail Beziehung mit entsprechenden Constraints abbilden können.
DROP TABLE SYSTEM.DIU_MASTER PURGE;
DROP TABLE SYSTEM.DIU_DETAIL PURGE;
CREATE TABLE SYSTEM.DIU_MASTER TABLESPACE SYSAUX AS SELECT TABLE_NAME, TABLESPACE_NAME, STATUS, NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER='SYS';
CREATE INDEX SYSTEM.DIU_MASTER_PK_IDX ON SYSTEM.DIU_MASTER (TABLE_NAME) TABLESPACE SYSAUX;
ALTER TABLE SYSTEM.DIU_MASTER ADD CONSTRAINT DIU_MASTER_PK PRIMARY KEY (TABLE_NAME);
CREATE INDEX SYSTEM.DIU_MASTER_LA_IDX ON SYSTEM.DIU_MASTER (LAST_ANALYZED);
CREATE INDEX SYSTEM.DIU_MASTER_MULTI_IDX ON SYSTEM.DIU_MASTER (STATUS, NUM_ROWS, BLOCKS);
CREATE TABLE SYSTEM.DIU_DETAIL TABLESPACE SYSAUX AS SELECT ROWNUM as DETAIL_ID, TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUM_DISTINCT, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER='SYS' AND TABLE_NAME IN (SELECT TABLE_NAME FROM SYSTEM.DIU_MASTER);
CREATE INDEX SYSTEM.DIU_DETAIL_PK_IDX ON SYSTEM.DIU_DETAIL (DETAIL_ID) TABLESPACE SYSAUX;
ALTER TABLE SYSTEM.DIU_DETAIL ADD CONSTRAINT DIU_DETAIL_PK PRIMARY KEY (DETAIL_ID);
CREATE INDEX SYSTEM.DIU_DETAIL_FK_IDX ON SYSTEM.DIU_DETAIL (TABLE_NAME) TABLESPACE SYSAUX;
ALTER TABLE SYSTEM.DIU_DETAIL ADD CONSTRAINT DIU_DETAIL_FK FOREIGN KEY (TABLE_NAME) REFERENCES SYSTEM.DIU_MASTER (TABLE_NAME);
CREATE INDEX SYSTEM.DIU_DETAIL_CN_IDX ON SYSTEM.DIU_DETAIL (COLUMN_NAME) TABLESPACE SYSAUX;
CREATE INDEX SYSTEM.DIU_DETAIL_LA_IDX ON SYSTEM.DIU_DETAIL (LAST_ANALYZED) TABLESPACE SYSAUX;
CREATE INDEX SYSTEM.DIU_DETAIL_MULTI_IDX ON SYSTEM.DIU_DETAIL (DATA_TYPE, NUM_DISTINCT) TABLESPACE SYSAUX;Verifizieren, wann Nutzungen gemeldet werden
Hinweis: bitte vor jeder Query auf DBA_INDEX_USAGE immer ein manuelles Flush veranlassen!
select NAME, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, TOTAL_ROWS_RETURNED,
LAST_USED
from DBA_INDEX_USAGE
where NAME LIKE 'DIU%' and owner='SYSTEM';
Es wurden keine Zeilen ausgewahlt.Somit steht fest: Das Erzeugen der Indizes führt noch nicht zu einer Nutzung.
Probieren wir einmal, was bei einem INSERT passiert, wenn wir den PK Constraint verletzen:
INSERT INTO SYSTEM.DIU_MASTER
SELECT TABLE_NAME, TABLESPACE_NAME, STATUS, NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER='SYS' and rownum <=3;
*
FEHLER in Zeile 1:
ORA-00001: Unique Constraint (SYSTEM.DIU_MASTER_PK) verletzt
COMMIT;Hinweis: Flush nicht vergessen!
select NAME, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, TOTAL_ROWS_RETURNED,
LAST_USED
from DBA_INDEX_USAGE
where NAME LIKE 'DIU%' and owner='SYSTEM';
Es wurden keine Zeilen ausgewahlt.Auch wenn wir das jetzt mehrfach wiederholen, wird es am Ergebnis nichts ändern.
Probieren wir, ob ein Zugriff mittels Index überhaupt erkannt wird:
SET AUTOTRACE ON
SELECT TABLE_NAME FROM SYSTEM.DIU_MASTER WHERE TABLE_NAME LIKE 'USER%';
TABLE_NAME
------------------------------
USER$
USERAUTH$
USER_ASTATUS_MAP
USER_CERTS$
USER_EDITIONING$
USER_HISTORY$
USER_PRIVILEGE_MAP
7 Zeilen ausgewahlt.
Ausfuhrungsplan
-----------------------------
Plan hash value: 1158262194
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 462 | 1 (0)|
|* 1 | INDEX RANGE SCAN| DIU_MASTER_PK_IDX | 7 | 462 | 1 (0)|
---------------------------------------------------------------------------
...Wurde dieser Zugriff über den Index vom SAMPLING erkannt?
SET AUTOTRACE OFF
select ACTIVE_ELEM_COUNT, ALLOC_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;
ACTIVE_ELEM_COUNT ALLOC_ELEM_COUNT LAST_FLUSH_TIME
----------------- ---------------- ----------------------
0 0 26.10.22 15:08:37,946Leider nein! Also werden wir hier etwas nachhelfen, indem wir in unserer SESSION von SAMPLED auf ALL umstellen:
ALTER SESSION SET "_iut_stat_collection_type" = ALL;
SET AUTOTRACE ON
SELECT TABLE_NAME FROM SYSTEM.DIU_MASTER WHERE TABLE_NAME LIKE 'USER%';
TABLE_NAME
------------------------------
USER$
USERAUTH$
USER_ASTATUS_MAP
USER_CERTS$
USER_EDITIONING$
USER_HISTORY$
USER_PRIVILEGE_MAP
7 Zeilen ausgewahlt.
Ausfuhrungsplan
----------------------------
Plan hash value: 1158262194
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 462 | 1 (0)|
|* 1 | INDEX RANGE SCAN| DIU_MASTER_PK_IDX | 7 | 462 | 1 (0)|
---------------------------------------------------------------------------
...
SET AUTOTRACE OFF
select ACTIVE_ELEM_COUNT, ALLOC_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;
ACTIVE_ELEM_COUNT ALLOC_ELEM_COUNT LAST_FLUSH_TIME
----------------- ---------------- ----------------------
1 1 26.10.22 15:11:15,026Jetzt wurde das Statement und der Indexzugriff offensichtlich erkannt. Einmal manuelles FLUSH und schauen wir nach:
select NAME, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, TOTAL_ROWS_RETURNED, LAST_USED from DBA_INDEX_USAGE where NAME LIKE 'DIU%' and owner='SYSTEM';
NAME TOTAL_ACCESS TOTAL_EXEC TOTAL_ROWS_RETURNED LAST_USED
----------------- ------------ ---------- ------------------- ----------
DIU_MASTER_PK_IDX 1 1 7 26.10.2022 Perfekt, genau was wir sehen wollen. Nachdem wir jetzt Daten bekommen, müssen wir uns die View DBA_INDEX_USAGE genauer ansehen.
Spalten der View DBA_INDEX_USAGE
| Spalte | Bedeutung |
| OBJECT_ID | Object_ID (aus DBA_OBJECTS) des Index |
| NAME | Name des Index |
| OWNER | Owner des Index |
| TOTAL_ACCESS_COUNT | Anzahl wie oft ein Zugriff auf den Index festgestellt wurde |
| TOTAL_EXEC_COUNT | Anzahl der Statement Executions, die diesen Index genutzt haben |
| TOTAL_ROWS_RETURNED | Anzahl der Zeilen, die dabei über den Index gelesen wurden |
| BUCKET_0_ACCESS_COUNT | Wie oft ist KEIN Index-Eintrag gefunden worden. (no rows selected) |
| BUCKET_1_ACCESS_COUNT | Wie oft ist EIN Index-Eintrag gefunden worden. |
| BUCKET_2_10_ACCESS_COUNT | Wie oft sind 2-10 Index-Einträge gelesen worden. |
| BUCKET_2_10_ROWS_RETURNED | |
| BUCKET_11_100_ACCESS_COUNT | Wie oft sind 11-100 Index-Einträge gelesen worden. |
| BUCKET_11_100_ROWS_RETURNED | Wie viele Index-Einträge sind beim Lesen von 11-100 Einträgen in Summe ausgegeben worden. |
| BUCKET_101_1000_ACCESS_COUNT | Wie oft sind 101-1000 Index-Einträge gelesen worden. |
| BUCKET_101_1000_ROWS_RETURNED | Wie viele Index-Einträge sind beim Lesen von 101-1000 Einträgen in Summe ausgegeben worden. |
| BUCKET_1000_PLUS_ACCESS_COUNT | Wie oft sind 1000+ Index-Einträge gelesen worden. |
| BUCKET_1000_PLUS_ROWS_RETURNED | Wie viele Index-Einträge sind beim Lesen von 1000+ Einträgen in Summe ausgegeben worden. |
| LAST_USED | Wann wurde die letzte Nutzung des Index festgestellt. |
Effizient sind Indizes vor allem dann, wenn die Anzahl der über den Index gelesenen Werte gering ist. Ein Index Full Scan und ein Index Fast Full Scan sind zwar oft immer noch effizienter als ein Full Table Scan, der Performancevorteil ist dann aber oft schon überschaubar.
Test #1: Constraint Index Nutzung und deren Auswirkung auf die Index Usage Statistik
Ein FK Constraint stellt sicher, dass man Daten in der Master Tabelle nicht einfach löschen kann, solange diese in der Detail Tabelle noch referenziert werden.
DELETE FROM SYSTEM.DIU_MASTER WHERE TABLE_NAME='USER$';
*
FEHLER in Zeile 1:
ORA-02292: Integritats-Constraint (SYSTEM.DIU_DETAIL_FK) verletzt - untergeordneter Datensatz gefunden
select ACTIVE_ELEM_COUNT, ALLOC_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;
ACTIVE_ELEM_COUNT ALLOC_ELEM_COUNT LAST_FLUSH_TIME
----------------- ---------------- ----------------------
0 0 26.10.22 15:23:38,755 Trotz „_iut_stat_collection_type“ = ALL wird die Nutzung des Indizes SYSTEM.DIU_DETAIL_FK_IDX nicht erkannt. Das gleiche gilt auch für PK und UK Constraint Indizes, wenn diese nur für den Constraint-Validierung benötigt werden.
Abgesehen von der Sicherstellung der referenziellen Integrität werden PK/UK/FK Indizes bei verschiedenen Statements genutzt, ohne dass dies bemerkt wird – es erscheint auch nicht in Execution Plänen. Der CBO kann auf Grund des Vorhandenseins von PK/UK Indizes in manchen Situationen effektivere Zugriffspläne erzeugen. Es ist daher sinnvoll, diese Indizes von jeglichen
Überlegungen, ob diese benötigt werden, grundsätzlich auszunehmen.
Test #2: Applikationsbetrieb
Simulieren wir jetzt einen Applikationsbetrieb und lassen wir einige Statements laufen, um die Auswirkung auf die Index Usage zu beobachten.
SELECT MIN(LAST_ANALYZED) FROM SYSTEM.DIU_MASTER;
SELECT M.TABLE_NAME, D.TABLE_NAME, M.NUM_ROWS, D.NUM_DISTINCT
FROM SYSTEM.DIU_MASTER M JOIN SYSTEM.DIU_DETAIL D ON (M.TABLE_NAME=D.TABLE_NAME)
WHERE DATA_TYPE = 'TIMESTAMP(9)';
SELECT EXTRACT(YEAR FROM LAST_ANALYZED) AS JAHR, COUNT(*) AS ANZAHL
FROM SYSTEM.DIU_DETAIL
GROUP BY EXTRACT(YEAR FROM LAST_ANALYZED);
SELECT COUNT(*)
FROM SYSTEM.DIU_DETAIL
WHERE COLUMN_NAME LIKE 'A%';
select ACTIVE_ELEM_COUNT, ALLOC_ELEM_COUNT, LAST_FLUSH_TIME from v$index_usage_info;
ACTIVE_ELEM_COUNT ALLOC_ELEM_COUNT LAST_FLUSH_TIME
----------------- ---------------- ---------------------
4 4 26.10.22 15:38:40,570 Hinweis! Manuellen Flush durchführen.
select NAME, TOTAL_ACCESS_COUNT, TOTAL_EXEC_COUNT, TOTAL_ROWS_RETURNED, LAST_USED from DBA_INDEX_USAGE where NAME LIKE 'DIU%' and owner='SYSTEM';
NAME TOTAL_ACCESS TOTAL_EXEC TOTAL_ROWS LAST_USED
-------------------- ------------ ---------- ---------- ----------
DIU_DETAIL_CN_IDX 1 1 717 26.10.2022
DIU_DETAIL_MULTI_IDX 1 1 3 26.10.2022
DIU_MASTER_LA_IDX 1 1 1 26.10.2022
DIU_MASTER_PK_IDX 5 3 17 26.10.2022Wie erwartet sehen wir hier die Nutzung der Indizes. Aber das sind ja nicht alle Indizes, die wir angelegt haben. Verifizieren wir einmal, welche Indizes in dieser View nicht aufscheinen:
SELECT INDEX_NAME
from DBA_INDEXES
where INDEX_NAME LIKE 'DIU%' and owner='SYSTEM'
MINUS
SELECT NAME
from DBA_INDEX_USAGE
where NAME LIKE 'DIU%' and owner='SYSTEM';
INDEX_NAME
------------------------------
DIU_DETAIL_FK_IDX
DIU_DETAIL_LA_IDX
DIU_DETAIL_PK_IDX
DIU_MASTER_MULTI_IDXAufgrund der Index-Namen wissen wir, dass die Indizes DIU_DETAIL_PK_IDX und DIU_DETAIL_FK_IDX für Constraints benötigt werden. Es bleiben noch die beiden Indizes DIU_DETAIL_LA_IDX und DIU_MASTER_MULTI_IDX. Diese wurden seit Ihrer Erstellung noch nicht als genutzt identifiziert und sind somit Kandidaten für die Evaluierung ob man diese Löschen kann bzw. soll.
Nicht genutzte Indizes sicher entfernen
Wenn man einen nicht als genutzt erkannten Index einfach löscht, kann das nachteilige Auswirkungen auf die Applikation haben. Wie wir schon gezeigt haben, werden Constraint-Indizes nicht als genutzt erkannt, wenn diese nur zur Validierung des Constraints verwendet werden. Im Falle von PK und UK Indizes ist das einfach zu verifizieren, bei FK Indizes wird es komplizierter: Der CBO kann potentiell jeden Index, der alle FK Spalten enthält, zur Verifizierung des FK Constraints heranziehen. Dabei ist es egal, ob die Spalten in der gleichen Reihenfolge wie im Constraint vorhanden oder ob noch andere Spalten im Index enthalten sind. Auch andere Indizes – beispielweise LOB, Advanced Queuing, Domain, etc. Indizes – darf man nicht (bzw. kann man nicht) einfach droppen. Indizes, die zu Oracle Data Dictionary Schemas gehören, sind selbstverständlich auch Tabu.
Die folgende Query berücksichtigt die meisten dieser Punkte (ausgenommen FK Indizes). Da das SYSTEM Schema ebenfalls zu den Oracle Data Dictionary Schemas zählt, werden wir unsere Indizes hier nicht sehen:
with orasupplied as
(SELECT OWNER FROM DBA_LOGSTDBY_SKIP
WHERE STATEMENT_OPT = 'INTERNAL SCHEMA'
union all
select username from dba_users
where username LIKE 'APEX%'
union all
select username from dba_users
where username IN ('PERFSTAT'))
select i.owner, i.table_name, i.Index_name, c.CONSTRAINT_TYPE,
nvl( to_char(iu.LAST_USED,'yyyy-mm-dd/hh24:mi:ss'),
decode( nvl(c.CONSTRAINT_TYPE,'X'),'X','never used',
'PK/UK constraint')) as last_used
from dba_indexes i left outer join dba_constraints c
on (i.owner=c.index_owner and i.index_name=c.index_name)
left outer join dba_index_usage iu
on (iu.owner=i.owner and iu.name = i.index_name)
where i.owner not in (select owner from orasupplied)
and i.index_name not LIKE 'AQ$%' /* ignore AQ Indizes */
and i.index_name not LIKE 'DR$%' /* ignore Text Indizes */
and i.index_type in ( 'NORMAL', 'FUNCTION-BASED NORMAL')
and c.CONSTRAINT_TYPE is null
and iu.last_used is null
order by i.owner, i.table_name, i.index_name;
So könnte das Ergebnis aussehen:
OWNER TABLE_NAME INDEX_NAME C LAST_USED
------ ----------- ----------------------- - -------------------
SCOTT DETAIL DETAIL_TEST_IDX2 never used
SCOTT EMP EMP_DEPTNO_IDX never used
SH CUSTOMERS DBM_CUST_COUNTRIES_FK never used
SH FAULT FAULT_STATUS never used
SH PRODUCTS PRODUCTS_PROD_CAT_IX never used
SH PRODUCTS PRODUCTS_PROD_SUBCAT_IX never used
SH TESTTAB1_FK TESTTAB1_FK_MATCHING never used
SH TESTTAB1_FK TESTTAB1_FK_POSTFIX never used
SH TESTTAB1_FK TESTTAB1_FK_PREFIX never usedJa, die Schemas SCOTT und SH sind auch von Oracle, allerdings gehören diese nicht zum Data Dictionary. Deren Indizes darf man sehr wohl eliminieren. Jetzt muss man nur noch jene Indizes, die potentiell für FK-Constraints genutzt werden, herausfiltern – diese wollen wir ja auf alle Fälle behalten.
Wenn man vorsichtig sein möchte, sollte man diese Indizes zuerst für eine gewisse Zeit auf INVISIBLE setzen, damit diese vom CBO nicht mehr in Execution Plänen verwendet werden:
ALTER INDEX SCOTT.DETAIL_TEST_IDX2 invisible;
ALTER INDEX SCOTT.EMP_DEPTNO_IDX invisible;Das kann man jederzeit in DBA_INDEXES verifizieren:
select OWNER, INDEX_NAME, VISIBILITY
from dba_indexes
where OWNER='SCOTT';
OWNER INDEX_NAME VISIBILIT
-------- ------------------- ---------
SCOTT DETAIL_PK VISIBLE
SCOTT DETAIL_TEST_IDX2 INVISIBLE
SCOTT EMP_DEPTNO_IDX INVISIBLE
SCOTT MASTER_PK VISIBLE
SCOTT PK_DEPT VISIBLE
SCOTT PK_EMP VISIBLE
SCOTT SYS_C0012414 VISIBLEMeldet jemand, dass Statements langsamer sind, muss man verifizieren ob diese auf Tabellen zugreifen, wo aktuell INVISIBLE Indizes existieren. Ist das der Fall, kann man den/die entsprechenden Indizes einfach wieder sichtbar (für den CBO nutzbar) machen:
ALTER INDEX SCOTT.EMP_DEPTNO_IDX visible;Gibt es seitens der Applikation keinerlei Beanstandungen, kann man sich sicher sein, dass die Indizes nicht genutzt werden:
DROP INDEX SCOTT.DETAIL_TEST_IDX2;Alternativ zum DROP dieser INVISIBLE Indizes kann man sie auch zusätzlich auf UNUSABLE setzen. Für das Monitoring, ob ein benötigter Index unabsichtlich UNUSABLE geworden ist, muss man in diesem Fall auch noch die Spalte VISIBILITY berücksichtigen.
ALTER INDEX SCOTT.DETAIL_TEST_IDX2 UNUSABLE;Beispiel für eine Monitoring-Query (Kandidaten für Index Rebuild):
select OWNER, INDEX_NAME, VISIBILITY, STATUS
from DBA_INDEXES
where VISIBILITY='VISIBLE' and STATUS='UNUSABLE';Zusammenfassung
Das Festellen, ob Indizes von der Applikation benutzt werden, war nie leichter als jetzt. In vielen Applikationen haben sich über die Zeit Indizes angesammelt, die aktuell nicht mehr benötigt werden. Um den Support des Applikationsherstellers nicht zu verlieren, sollten solche Maßnahmen im Vorfeld mit diesem abgeklärt werden.
Referenzen
- Flush von Index Usage
- How To Query And Change The Oracle Hidden Parameters In Oracle 10g and Later (Doc ID 315631.1)
- Index Usage Tracking (DBA_INDEX_USAGE, V$INDEX_USAGE_INFO) in Oracle Database 12c Release 2 (12.2)