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(*)
----------
14Wenn 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
----------
528Wie 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 processedDas 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 processedDie 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 processedDie 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
- Videoaufzeichnung eines Vortrags zum Thema: Oracle Datenbank – das Geheimnis der richtigen Indizierung
- In diesem Workshop wir das Thema Indizierung ausführlich besprochen: Oracle SQL Tuning Workshop
- Artikel: Berechtigungen für SQL Tuning