Grundlagen der SQL

Grundlagen der
SQL-Optimierung
Dr. Frank Haney
1
Inhalt







Phasen der SQL-Verarbeitung
SQL-Optimierung – Grundlagen
Der kostenbasierte Optimizer – Funktionsweise
Entscheidungsgrundlagen
Indexverwendung
Ausführungspläne
Automatismen
Dr. Frank Haney
2
Bearbeiten von Abfragen (SELECT) I
Parse
Bind
Execute
Fetch
Parse-Phase:
 Sucht im Shared Pool nach der Anweisung
 Syntaxprüfung
 Prüft Semantik und Privilegien
 Führt View-Definitionen und Unterabfragen zusammen (Merging)
 Sperrt die währen der Parse-Phase verwendeten Objekte
 Erzeugt und speichert den Ausführungsplan (Optimizer)
Anmerkung: Die erste Wertzuweisung von BIND-Variablen ist ab 9i dem
Optimizer schon bekannt! = Bind Peeking
Dr. Frank Haney
3
Bearbeiten von Abfragen (SELECT) II
Parse
Bind
Execute
Fetch
Bind-Phase:
 Durchsucht die Anweisung nach Bind-Variablen
 Weist einen Wert (neu) zu
Execute-Phase
 Ausführungsplan wird angewendet
 I/Os werden ausgeführt
Fetch-Phase
 Zeilen werden abgerufen
 Erforderliche Sortierungen werden ausgeführt
 Array Fetch-Mechanismus wird verwendet
Dr. Frank Haney
4
SQL-Optimierung – Grundlagen
Bestimmumgsstücke
 Prinzipien der Plangenerierung
 Kostenfunktionen der verschiedenen Basisoperationen
 Transformationsregeln für SQL-Anweisungen
 Kostenmodell (plattformabhängig!)
 Informationen über die Daten (Statistiken etc.)
 Suchstrategie nach dem effizientesten Plan
Prinzipien




Selektion so früh wie möglich
Zusammenfassung von Basisoperationen
Möglichst wenig Zwischenergebnisse speichern (Pipelining)
Keine irrelevanten Berechnungen ausführen
Dr. Frank Haney
5
Logische Optimierung
Übersetzung der SQL in einen unoptimierten Plan, dabei:




Sichtexpansion
Standardisierung (z.B. in konjunktive Normalform bringen)
Vereinfachung (Propagierung von Konstanten, Ermittlung
unerfüllbarer Prädikate, Idempotenzen)
Entschachtelung (Auflösung von Unterabfragen)
Algebraische Optimierung




Entfernen redundanter Operationen
Verbundreihenfolge (logisch, ohne Kosten)
Vorgruppierungen
Verschiebung von Selektion und Projektion möglichst weit in
Richtung der Blätter des Plans
Dr. Frank Haney
6
Physische Optimierung
Kostenbasierte Auswahl aus einer Menge von Plänen





Anwendung der Berechnungsalgorithmen
(Transformationsregeln + Kostenfunktionen)
Einbeziehung von Statistiken über die Daten und ihre
Verteilung
Einsatz eines gewichteten Kostenmodells
Verwendung von Suchstrategien
Vermeidung teurer Operationen
Probleme



Gewicht der Kosten u.U. plattformabhängig
Deterministische Suchstrategien führen u.U zu aufwendiger
Optimierung (Parse-Zeit)
Heuristische Suchstrategien finden eventuell nicht den
optimalen Plan (Nebenminima)
Dr. Frank Haney
7
Optimizer – Geschichte
Dr. Frank Haney
8
Optimizermodi
Dr. Frank Haney
9
Kostenbasierter Optimizer
Der CBO






Anweisungstransformation (z.B. Unterabfragen in Joins)
Auswahl der Join-Reihenfolge
Auswahl der Join-Methode
erzeugt auf der Grundlage der möglichen Zugriffspfade eine Menge von
Ausführungsplänen
schätzt die Selektivität, Kardinalität und Kosten (mutmaßlicher
Ressourcenverbrauch) jedes Plans auf der Grundlage von Statistiken.
Kostenfaktoren:
•
Anzahl der logischen Lesevorgänge (wichtigster Faktor)
•
CPU-Nutzung
•
Netzwerkübertragungen
vergleicht die Kosten und wählt den günstigsten Plan.
Dabei gilt:



Ein I/O erzeugt eine COST Einheit
CPU-Zeit wird in COST Einheiten ausgedrückt
SORT (TEMP) wird in COST Einheiten ausgedrückt
Dr. Frank Haney
10
Grenzen des kostenbasierten Optimizer
Probleme bekommt der CBO bei
 Korrelierten Spalten  Erweiterte Statistiken in Oracle 11g
 Blöcke (Tupel) sind bereits von anderen Statements in den Cache
geladen  evt. SQL Result Cache verwenden (Oracle 11g)




Bindevariablen für sehr ungleichmäßig verteilte Spaltenwerte 
Adaptive Cursor Sharing in Oracle 11g
Subquery Unnesting und View Merging: Funktioniert regelbasiert
ohne Vergleich der Kosten des Original-Statements
Permutationen der Join-Reihenfolge bei vielen Tabellen im Join
(lange Parsezeit vs. Verfehlen des optimalen Plans)
Mögliche Lösungen
• Guten Plan durch Optimizer Hints realisieren
• Stored Outlines
• SQL Profile (ab Oracle 10g)
Dr. Frank Haney
11
CBO – Funktionsweise
Dr. Frank Haney
12
Wichtige Abfragetransformationen












IN-Liste: mehrfaches OR mit Gleichheitszeichen
Unterabfragen: Join
LIKE ohne Wildcard: Gleichheitszeichen
a > ANY (b, c): a > b OR a > c
a > ALL (b, c): a > b AND a > c
a > ANY (select b from c): EXISTS (select b from c where a > b)
a BETWEEN b AND c: a >= b AND a <= c
NOT a = (unterabfrage): a != (unterabfrage)
Verschiedene Bedingungen mit OR: UNION ALL
DISTINCT, GROUP BY, JOIN: eventuell implizite Sortierung
Views: View Merging oder Predicate Pushing
a operator Konstante AND a=b (Transitivität): b operator Konstante
Dr. Frank Haney
13
Beispiel Abfragetransformationen I
Subquery Unnesting
Dr. Frank Haney
14
Beispiel Abfragetransformationen II
Auflösung Transitivität
Dr. Frank Haney
15
Beispiel Abfragetransformationen III
Dr. Frank Haney
16
Entscheidungsgrundlagen für den CBO
OBJEKT
SYSTEM
STATISTIKEN
HISTOGRAM
HINTS
INITIALISIERUNGSPARAMETER
NLS
RUNTIME INFORMATION
OPTIMIZER
ZUGRIFFSPFADE
STORED OUTLINES
DYNAMIC SAMPLING
ORACLE RELEASE
Dr. Frank Haney
SQL PROFILE
SQL PLAN BASELINES
17
Was ist ein guter Ausführungsplan?
– Die steuernde Tabelle hat den besten Filter.
– An den nächsten Schritt werden so wenig Zeilen wie
nötig übergeben.
– Die Join-Methode entspricht der Zahl der
zurückgegebenen Zeilen.
– Views werden effizient verwendet.
– Es gibt keine ungewollten kartesischen Produkte.
– Auf jede Tabelle wird effizient zugegriffen.
– Die Prädikate in der SQL-Anweisung und die
Anzahl der Zeilen in der Tabelle müssen geprüft
werden.
– Ein Full Table Scan bedeutet nicht Ineffizienz.
Dr. Frank Haney
18
Statistiken für den CBO sammeln
ANALYZE SQL-Befehl, berechnet oder schätzt Statistik, relativ schlecht für die
Performance, wird eventuell in der Zukunft vom CBO nicht mehr unterstützt.
(Für Klausel VALIDATE STRUCTURE aber notwendig.)
Syntax:
ANALYZE {INDEX|TABLE|CLUSTER} name {COMPUTE|ESTIMATE|DELETE}
STATISTICS [FOR {TABLE|COLUMNS (col_name, ...)|ALL
COLUMNS|ALL INDEXED COLUMNS|ALL INDEXES} [SIZE n]] [SAMPLE
n {ROWS|PERCENT}];
Views: USER_TABLES, USER_TAB_COLUMNS, USER_TAB_COL_STATISTICS,
USER_INDEXES, DBA_HISTOGRAMS
DBMS_STATS PL/SQL-Package, von Oracle empfohlen, besser für die
Performance, Statistiken können exportiert und importiert werden. (Stabilität
des Ausführungsplans)
Syntax:
DBMS_STATS.GATHER_DATABASE_STATS()
DBMS_STATS.EXPORT_SCHEMA_STATS()
Dr. Frank Haney
19
Was wird gesammelt?
Tabellen:




Zeilenzahl
Anzahl der Blöcke
Durchschnittliche Zeilenlänge
Migration und Verkettung
Spalten:



Anzahl unterschiedlicher Werte (Kardinalität)
Anzahl der NULL-Marken
Werteverteilung (Histogramme – wenn erforderlich)
Indizes:




Anzahl der Blätter (Leaf Blocks)
Zahl der Ebenen
Anzahl der Einträge pro Indexschlüssel
Clustering-Faktor
System:


I/O-Performance (Single und Multi Block Read)
CPU-Verwendung
Dr. Frank Haney
20
B*Baum-Indizes
Indexstruktur
Wurzel (root)
Zweig 2
Zweig 1
Blatt 1 (leaf)
Blatt 2
Blatt 3
Blatt 4
Blatt 5
Doppelte Verkettung
Indexeintrag
Indexzeilenheader
Dr. Frank Haney
Spaltenlänge
Spaltenwert
ROWID
21
Beispiele für Indexzugriff oder Full Table Scan
Index auf einer Spalte (fett):
... WHERE SUBSTR(name,1,6)='SCHULZ';
... WHERE name LIKE 'SCHULZ%';
... WHERE name LIKE '%UELLER';
... WHERE name IN ('SMITH', 'KING');
... WHERE TRUNC(einstellungsdatum)=TRUNC(sysdate-7);
... WHERE gehalt IS NULL;
... WHERE gehalt IS NOT NULL;
... WHERE gehalt < '2000';
... WHERE gehalt != 2000;
... WHERE gehalt > provision;
... WHERE gehalt + 3000 < 5000;
... WHERE name=COALESCE(&var, name);
... WHERE name || vorname = 'MUELLERGEHILFE';
Index über mehrere Spalten (name, abteilungs_nr):
... WHERE abteilungs_nr = 10;
... WHERE name LIKE ('KRA%');
Dr. Frank Haney
22
Beispiele NOT IN vs. NOT EXISTS
Gesucht sind Abteilungen, zu denen es keine Mitarbeiter gibt:
SELECT abteilungsname, abteilungs_nr
FROM abteilungen
WHERE abteilungs_nr NOT IN
(SELECT abteilungs_nr FROM mitarbeiter);
Oder?
SELECT abteilungsname, abteilungs_nr
FROM abteilungen
WHERE NOT EXISTS
(SELECT 1 FROM mitarbeiter
WHERE abteilungen.abteilungs_nr = mitarbeiter.abteilungs_nr);
Dr. Frank Haney
23
Tools für das Anzeigen der Ausführungspläne








EXPLAIN PLAN
SQL*Plus Autotrace
SQL Trace und TKPROF
V$SQL_PLAN
DBMS_XPLAN.DISPLAY_CURSOR
SQLDeveloper
Extended SQL-Tracing (Events 10046 und 10053)
SQLTXPLAIN
Dr. Frank Haney
24
Ausführungspläne
PDB1@ORCL> select * from mitarbeiter natural join abteilungen;
Execution Plan
---------------------------------------------------------Plan hash value: 4182503278
-------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
14 | 1680 |
6
(0)| 00:00:01
|* 1 | HASH JOIN
|
|
14 | 1680 |
6
(0)| 00:00:01
|
2 |
TABLE ACCESS FULL| ABTEILUNGEN |
4 |
120 |
3
(0)| 00:00:01
|
3 |
TABLE ACCESS FULL| MITARBEITER |
16 | 1440 |
3
(0)| 00:00:01
PDB1@ORCL> select * from mitarbeiter natural join abteilungen where abteilungs_nr=10;
Execution Plan
---------------------------------------------------------Plan hash value: 3200959053
----------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
3 |
360 |
5 (0) | 00:00:01 |
|
1 | NESTED LOOPS
|
|
3 |
360 |
5 (0) | 00:00:01 |
|
2 |
TABLE ACCESS BY INDEX ROWID| ABTEILUNGEN
|
1 |
30 |
2 (0) | 00:00:01 |
|* 3 |
INDEX UNIQUE SCAN
| ABT_PRIMARY_KEY |
1 |
|
1 (0) | 00:00:01 |
|* 4 |
TABLE ACCESS FULL
| MITARBEITER
|
3 |
270 |
3 (0) | 00:00:01 |
Dr. Frank Haney
25
Automatismen (Überblick)
–
–
–
–
–
–
–
–
Dynamic Sampling (9i , erweitert in 12c)
Automatic Database Diagnostic Monitor (10g)
Cardinality Feedback (11g, erweitert in 12c)
Adaptive Cursor Sharing (11g)
SQL Plan Baselines und Plan History (11g)
Automatic SQL-Tuning (11g)
Automatic Column Group Detection (12c)
Adaptive Query Optimization (12c)
• Adaptive Plans
• Adaptive Statistics
Dr. Frank Haney
26