Performance by Example – Warum braucht man Indices?

Dieser Blog ist der Erste einer voraussichtlich langen Serie, in der ich allen Lesern (SQL) Performance auf Grund von einfachen, nachvollziehbaren Beispielen näher bringen werde.

Bei meiner Tätigkeit als Consultant sowie als Oracle University Trainer wurde ich des öfteren gefragt: „Warum braucht man eigentlich Indizes, das ist ja nur kompliziert und aufwändig!“. Bei den dabei entstandenen Diskussionen stellte sich dann meistens heraus, dass in der Datenbank nur einige wenige bis maximal einige 100 Datensätze in den Tabellen gespeichert waren (leider typische Datenbankgrößen im Anwendungsentwicklungsbereich). Erst mit Hilfe von einfachen Beispielen – wie in diesem Blog – konnte ich mein Gegenüber überzeugen, dass Indices nicht sinnlos sind.

Hinweis: Einige Befehle benötigen in der Oracle Datenbank DBA Privilegien. Man kann diese aber einfach auf andere Datenbanken und nicht priviligierte Benutzer abändern. Für SQL*Plus AUTOTRACE benötigt man entweder DBA Privilegien, die Rolle PLUSTRCE oder entsprechende Berechtigungen – siehe auch meinen Blog Berechtigungen für SQL Tuning.

Nehmen wir an, wir haben folgende Tabelle:

DROP TABLE MITARBEITER;
CREATE TABLE MITARBEITER 
   (MID      Number Primary Key,
    Vorname  varchar2(30),
    Nachname varchar2(30),
    eMail    varchar2(80));

Jetzt generieren wir noch einige Datensätze:

INSERT INTO MITARBEITER 
SELECT ROWNUM, COLUMN_NAME, TABLE_NAME, COLUMN_NAME || '.' || TABLE_NAME || '@' || OWNER || '.COM' 
  FROM ALL_TAB_COLUMNS
 WHERE OWNER='SYSTEM';
 
COMMIT;

Sucht man jetzt in der Tabelle nach einem bestimmten Nachnamen (TABLE_NAME), geht das ganz schnell:

SELECT count(*)
  FROM MITARBEITER 
 WHERE Nachname = 'OL$';

  COUNT(*)
----------
        14

Wenn man die Zeit stoppt, wird sie im Bereich von Millisekunden oder sogar weniger liegen – weil die Daten einerseits noch im Buffer Cache liegen und andererseits nur wenige Blöcke belegen.
Nur: was passiert, wenn die Tabelle wächst?

INSERT INTO MITARBEITER 
SELECT ROWNUM+1000000, dtc.COLUMN_NAME, dtc.TABLE_NAME, dtc.COLUMN_NAME || '.' || dtc.TABLE_NAME || '@' || dtc.OWNER || '.COM' 
  FROM ALL_TAB_COLUMNS dtc, ALL_TABLES dt
 WHERE ROWNUM <= 10000000;

COMMIT;

Schauen wir mal, wie groß die Tabelle jetzt ist:

SELECT BYTES/1024/1024 as MB
  FROM USER_SEGMENTS
 WHERE SEGMENT_NAME='MITARBEITER';

        MB
----------
       528

Wie schaut es jetzt mit dem SELECT von vorhin aus? Damit wir mehr Informationen bekommen, nutzen wir SQL*Plus AUTOTRACE. Nutzer von SQL Developer, TOAD oder ähnlichen Tools können diese Informationen auch ansehen. Die Statementlaufzeit wird im Tool angezeigt und beide Tools können einen Ausführungsplan für Statements anzeigen.

set autotrace on
set timing on

SELECT count(*)
  FROM MITARBEITER 
 WHERE Nachname = 'OL$';

  COUNT(*)
----------
      1386

Abgelaufen: 00:00:00.21

Ausfuhrungsplan
----------------------------------------------------------
Plan hash value: 1923829908

-----------------------------------------------------------------------...
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)|...
-----------------------------------------------------------------------...
|   0 | SELECT STATEMENT   |             |     1 |    62 | 18258   (1)|...
|   1 |  SORT AGGREGATE    |             |     1 |    62 |            |...
|*  2 |   TABLE ACCESS FULL| MITARBEITER |   739 | 45818 | 18258   (1)|...
-----------------------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NACHNAME"='OL$')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      66982  consistent gets
          0  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Das Ergebnis kommt immer noch sehr schnell – auf meinem Rechner in 0.21 Sekunden.
Wir sehen aber im Execution Plan, dass Oracle die ganze Tabelle MITARBEITER – also die ca. 528MB – durchsucht hat. In der Statistik finden wir, dass dafür 66982 consistent gets (= Blockzugriffe im Buffer Cache) benötigt wurden.

Was, wenn die Tabelle nicht in´s Buffer Cache passt oder nach einem Restart der Instanz nichts im Buffer Cache ist? Dann müssten die 528MB gelesen werden, was sicher etwas länger dauert!

Das kann man als DBA leicht simulieren. Der nächste Befehl wirft alle Blöcke aus dem Buffer Cache (bitte nicht auf Produktionssystemen ausführen!):

alter system flush buffer_cache;

Das kann etwas dauern, wenn der Datenbank Writer noch nicht geschriebene Blöcke auf die Disk schreiben muss. Bitte nicht auf produktiven Systemen durchführen, da danach alle Statement auf Grund des nötigen Lesens von der Disk deutlich langsamer werden!
Jetzt das SELECT wiederholen – schauen wir, was da herauskommt:

SELECT count(*)
  FROM MITARBEITER 
 WHERE Nachname = 'OL$';
 
 Abgelaufen: 00:00:20.62

Ausfuhrungsplan
----------------------------------------------------------
Plan hash value: 1923829908

-----------------------------------------------------------------------...
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)|...
-----------------------------------------------------------------------...
|   0 | SELECT STATEMENT   |             |     1 |    62 | 18258   (1)|...
|   1 |  SORT AGGREGATE    |             |     1 |    62 |            |...
|*  2 |   TABLE ACCESS FULL| MITARBEITER |   739 | 45818 | 18258   (1)|...
-----------------------------------------------------------------------...

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NACHNAME"='OL$')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      66982  consistent gets
      66966  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Die Laufzeit liegt bei meinem Rechner plötzlich nicht mehr bei 0.xx Sekunden, sondern bei 20.62 Sekunden, das ist 100 mal langsamer. In den Statistiken sieht man, dass die consistent gets gleich geblieben sind – die Datenmenge hat sich ja nicht geändert. Neu ist jedoch, dass Oracle 66966 physical reads gemacht hat – spricht die ganze Tabelle (plus Metadaten, Tabellen Header,…) gelesen hat.

Wir sehen somit, dass die Laufzeit mit steigender Anzahl von Datensätzen entsprechend länger wird, weil irgendwann nicht mehr alle Daten im Buffer Cache Platz haben werden.

Wie ändert sich das Ganze, wenn man einen Index nutzt?

CREATE INDEX MITARBEITER_IDX1 ON MITARBEITER(Nachname);

SELECT count(*)
  FROM MITARBEITER 
 WHERE Nachname = 'OL$';

  COUNT(*)
----------
      1386

Abgelaufen: 00:00:00.01

Ausfuhrungsplan
----------------------------------------------------------
Plan hash value: 2866874892

---------------------------------------------------------------------------...
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)|...
---------------------------------------------------------------------------...
|   0 | SELECT STATEMENT  |                  |     1 |    62 |     2   (0)|...
|   1 |  SORT AGGREGATE   |                  |     1 |    62 |            |...
|*  2 |   INDEX RANGE SCAN| MITARBEITER_IDX1 |  1386 | 85932 |     2   (0)|...
---------------------------------------------------------------------------...


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NACHNAME"='OL$')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistiken
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        101  consistent gets
          5  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Die Laufzeit ist jetzt wieder bei fast 0. Im Execution Plan sieht man, dass der Index MITARBEITER_IDX1 mittels INDEX RANGE SCAN gelesen wird. Das bedeutet, dass nur ein kleiner Teil des Indizes – alle Mitarbeiter mit dem Nachname = OL$ – gelesen werden müssen. Dafür mussten nur noch 101 consistent gets gemacht werden.

Zusammenfassung

Ja, auf Tabellen mit wenigen Datensätzen wird man mit und ohne Index kaum eine Laufzeit feststellen können. Wenn die Datenmenge aber steigt, ändert sich das deutlich. Spätestens wenn die Tabellen mehrere GB groß werden, steigt die Laufzeit merklich an.

Weiterführende Informationen

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

This site uses Akismet to reduce spam. Learn how your comment data is processed.