Index Usage Tracking in der Oracle Datenbank Ulrike Schwinn

Immer wieder erhalten wir Anfragen zum Thema Überprüfung der Indexnutzung in der Oracle Datenbank. Meist geht es darum herauszufinden, ob Indizes gänzlich ungenutzt sind oder selten verwendet werden. Wie wahrscheinlich jedem Oracle Anwender bewusst ist, können Indizes vorteilhaft für den Zugriff sein, auf der anderen Seite kann die Ausführung von DML Operation allerdings auch verlangsamt werden. Indizes müssen nämlich bei DML Operationen mitgepflegt werden.

Es gibt mehrere Möglichkeiten die Indexnutzung zu überwachen. Mittlerweile ist dazu schon einiges im Internet zu finden; auch wir haben in einigen älteren Blogeinträgen das Thema beschrieben (siehe auch Linksammlung unten). In Oracle Database 12c Release 2 ist nun eine weitere Methode hinzugefügt worden, die ohne Setup zur Verfügung steht und ganz einfach zu verwenden ist.

Möglichkeiten zum Index Tracking

Seit jeher gibt es verschiedene Methoden des Index Trackings. Jede Methode hat dabei ihre Vor- und Nachteile und ist mit unterschiedlichem Aufwand verbunden. Eine erste Idee um Indizes zu monitoren, bietet sicherlich die manuelle Methode - das Generieren bzw. Selektieren des Ausführungsplans. Hier kann man schnell erkennen, ob Indizes verwendet werden und welche Ausführungsstatistiken zugrunde liegen.

SQL> select distinct job_id from employees;
...
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  636qd26k255mx, child number 0
-------------------------------------
select distinct Job_id from employees

Plan hash value: 1812349206
--------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |       |       |     2 (100)|         |
|   1 |  SORT UNIQUE NOSORT|            |    19 |   171 |     2  (50)| 00:00:01|
|   2 |   INDEX FULL SCAN  | EMP_JOB_IX |   107 |   963 |     1   (0)| 00:00:01|
--------------------------------------------------------------------------------

Möchte man die Ausführung in Views monitoren, stehen die Views V$SQL_PLAN bzw DBA_HIST_SQL_PLAN zur Verfügung. Diese Methode kann allerdings recht aufwändig und zeitintensiv bei der Überprüfung vieler Indizes werden.

Einfacher geht es mit dem Advisory Framework - dem SQL Access Advisor, der im Linemode über das Package DBMS_ADVISOR oder intuitiv und einfach graphisch im Enterprise Manager nutzbar ist. Der SQL Access Advisor steht wie (fast) alle Advisories ohne Installation in der Datenbank zur Verfügung, erfordert aber die Lizenzierung des Oracle Database Tuning Packs. Die Anwendung des SQL Access Advisors funktioniert ganz einfach. Man benötigt nur einen Workload, den man über ein SQL Tuning Set (STS) zur Verfügung stellt. Das Ergebnis gibt dann beispielsweise an, welche Indizes erhalten bleiben sollen oder welche gelöscht werden können. Mehr Informationen dazu finden sich im Abschnitt unten - in der Linksammlung.

Darüberhinaus existiert seit jeher auch ein dediziertes Index Monitoring, das auch unter dem Namen Index Monitoring bekannt ist. Es steht in jeder Edition zur Verfügung, muss allerdings pro Index mit der speziellen Syntax MONITORING USAGE wie folgt aktiviert oder wieder deaktiviert werden.

alter index emp_job_ix monitoring usage;
alter index emp_job_ix nomonitoring usage; 

Mit V$OBJECT_USAGE lässt sich dann erschliessen, ob eine Index überhaupt einmal verwendet wurde. Wichtig zu wissen ist, dass Oracle während des Parsings diese Entscheidung fällt und einen Eintrag dazu tätigt. Folgendes Beispiel zeigt ein Ergebnis.

SQL> select * from v$object_usage;

INDEX_NAME           TABLE_NAME           MON USE START_MONITORING
-------------------- -------------------- --- --- -------------------
END_MONITORING
-------------------
EMP_JOB_IX           EMPLOYEES            YES YES 01/05/2017 12:14:14

Der Nachteil dieser Methode ist offensichtlich: Man kann weder erkennen, wann oder gar wie häufig ein Index verwendet wurde. Nur ein einziger Eintrag mit Flag und Uhrzeit erscheint. Auch wird nicht ersichtlich wie die Indexstatistiken ausfallen. Zudem muss man alle Indizes manuell einschalten bzw. ausschalten, wenn man die erneute Nutzung evaluieren will. Eine Lösung dazu bietet jetzt Oracle Database 12c Release 2.

Index Usage Tracking mit Oracle Database 12.2

Im aktuellen Datenbankrelease (12.2) ist das Indexmonitoring nicht nur stark vereinfacht worden, sondern liefert darüberhinaus auch detaillierte Ergebnisse. Neue Data Dictionary Strukturen liefern dabei automatisch Informationen über den Gebrauch von Indizes in der Datenbank. Die ausgelieferten Informationen geben an, welche Indizes Verwendung finden und auch wie häufig ein Index genutzt wurde. Auf diese Weise lässt sich leicht herausfinden, ob die gewählte Indexstrategie die richtige ist oder ob es Indizes gibt, die wegfallen könnten. Kein Einschalten des Features ist erforderlich und es steht in allen Editionen zur Verfügung.

Betrachten wir die neue Funktion an einem Beispiel. Die neue V$-View V$INDEX_USAGE_INFO gibt allgemeine Informationen zum Indexmonitoring und zeigt an, wann der letzte "Flush" (Leerung) erfolgt ist. Dabei erfolgt ein Flush alle 15 Minuten. In meiner Umgebung nach dem Öffnen meiner PDB mit Namen PDB1 hat die V$View folgende aktuelle Werte.

SQL> select index_stats_enabled enabled, index_Stats_collection_type type, active_elem_count active,
            alloc_elem_count alloc, max_elem_count max, flush_count, last_flush_time last_flush, con_id 
     from v$index_usage_info;

ENABLED       TYPE ACTIVE ALLOC    MAX FLUSH_COUNT LAST_FLUSH           CON_ID
------- ---------- ------ ----- ------ ----------- -------------------- ------
      1	         1      0     0  30000           0                    0      3

Die ersten beiden Spalten weisen darauf hin, dass und wie das Feature eingeschaltet ist. Der Wert "1" in der Spalte INDEX_STATS_ENABLED steht dabei für "Enabled" (eingeschaltet). Bei INDEX_STATS_COLLECTION_TYPE gibt der Wert "1" an, dass für die Statistiken ein Sampling bei den Ausführungen verwendet wird. Dies bedeutet, dass nicht bei jeder Ausführung eines Statements, die Indexnutzung aufgezeichnet wird. Ändern lassen sich diese Werte nur über entsprechende Underscore Parameter, was nicht empfohlen ist bzw. nur mit Support oder Consulting Unterstützung von Oracle geschehen sollte. Die Spalte LAST_FLUSH_TIME gibt dabei an, wann der letzte Flush erfolgt ist.

Um ein wenig Last zu erzeugen, starte ich mit SQLcl und dem REPEAT Kommando eine Query an, die einen Index verwendet und 1000 Mal ausgeführt wird.

SQL> select distinct job_id from employees;

JOB_ID
----------
AC_ACCOUNT
...
ST_MAN

19 rows selected.
SQl> repeat 1000 1

Selektieren wir wieder V$INDEX_USAGE_INFO, können wir erkennen, dass in der Spalte ACTIVE_ELEM_COUNT der Wert "1" angezeigt wird. Zum letzten Mal erfolgte ein "Flush" dabei um 10:12 Uhr. Mittlerweile seit Öffnen der PDB sind auch schon 5 "Flushes" erfolgt.

SQL> alter session set nls_timestamp_format='dd.mm.yyyy hh24:mi';
Session altered.
 
SQL> select active_elem_count active, alloc_elem_count alloc, flush_count, last_flush_time last_flush, con_id  
     from v$index_usage_info;

ACTIVE       ALLOC FLUSH_COUNT LAST_FLUSH           CON_ID
------- ---------- ----------- -------------------- ------
      1          1           5 05.01.2017 12:37          3

Nach ca 15. Minuten betrachten wir das Ergebnis in der neuen Data Dictionary View DBA_INDEX_USAGE. Uns interessiert im Moment nur der Owner HR.

SQL> select * from dba_index_usage where owner='HR';

 OBJECT_ID NAME            OWNER           TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT
---------- --------------- --------------- ------------------ ----------------
TOTAL_ROWS_RETURNED BUCKET_0_ACCESS_COUNT BUCKET_1_ACCESS_COUNT
------------------- --------------------- ---------------------
BUCKET_2_10_ACCESS_COUNT BUCKET_2_10_ROWS_RETURNED BUCKET_11_100_ACCESS_COUNT
------------------------ ------------------------- --------------------------
BUCKET_11_100_ROWS_RETURNED BUCKET_101_1000_ACCESS_COUNT
--------------------------- ----------------------------
BUCKET_101_1000_ROWS_RETURNED BUCKET_1000_PLUS_ACCESS_COUNT
----------------------------- -----------------------------
BUCKET_1000_PLUS_ROWS_RETURNED LAST_USED
------------------------------ ---------
     88289 EMP_JOB_IX      HR                             388              388
              41516                     0                     0
                       0                         0                          0
                          0                          388
                        41516                             0
                             0 05-JAN-17

Wie man erkennen kann, ist der Index EMP_JOB_IX mittlerweile 388 Mal verwendet worden, das bedeutet 41516 Zeilen sind durch den Index im Zugriff - also 107 pro Ausführung. Weiter oben im Ausführungsplan haben wir das gleiche Ergebnis erhalten. Spalten, die mit BUCKET_ beginnen wie BUCKET_101_1000_ACCESS_COUNT oder BUCKET_1000_PLUS_ACCESS_COUNT helfen zusätzlich bei einer Klassifizierung der Verwendung.

Führen wir das Experiment mit weiteren Abfragen fort, können wir erkennen, dass weitere Einträge erfolgen. Zusätzlich wird angezeigt, wann ein Index zum letzten Mal und wie häufig dieser verwendet wurde. Dies sind sicherlich wichtige Kriterien bei der Entscheidung über eine gute Indexstrategie.

SQL> select object_id, name, total_access_count, total_rows_returned, last_used
     from dba_index_usage where owner='HR';

 OBJECT_ID NAME        TOTAL_ACCESS_COUNT TOTAL_ROWS_RETURNED LAST_USED
---------- ----------- ------------------ ------------------- ----------------
     88289 EMP_JOB_IX                 454               48578 05.01.2017 10:42
     88296 LOC_CITY_IX                143                3289 05.01.2017 13:57

Fazit

Das neue Feature Index Usage Tracking ist einfach zu bedienen und bietet einen detaillierten Überblick über die Indexnutzung. Es muss nicht eingeschaltet werden und steht automatisch in allen Editionen zur Verfügung. Allerdings wird vorausgesetzt, die Data Dictionary View DBA_INDEX_USAGE und die V$- View V$INDEX_USAGE_INFO selektieren zu können.

Da die Funktion ausführungsbasierend arbeitet, kann man leicht erkennen, wann ein Index zum letzten Mal im Zugriff war, wie viele Zeile der Index nutzte (Güte des Index) und wie häufig dieser verwendet wurde. Beachten sollte man, dass das Feature aus Performancegründen mit einem Sampling Algorithmus konfiguriert ist. Das heisst, nicht jede Ausführung wird automatisch berücksichtigt.

Da die Verwendung sehr einfach ist und keinen zusätzlichen Overhead beinhaltet, empfehle ich, dieses Index Tracking bei jeder Tuning Massnahme einzuplanen.

Weitere Informationen

• Blogeintrag: Indizes in der Datenbank: Monitoring und Komprimierung (Index Key Compression)
• Blogeintrag: Indexdesign mit SQL Access Advisor analysieren
• Handbuch: Database Reference
• Video: Ask Tom Answer Team (Connor McDonald and Chris Saxon) on Oracle Database 12c Release 2 New Features

 

Zurück zum Anfang des Artikels

Zurück zur Community-Seite