SQL Tuning Optimizer

Inhalt
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
Ø
SQL-Optimierung – Grundlagen
Optimizermodi
Optimizer – Syntax
CBO – Funktionsweise
Entscheidungsgrundlagen
Zugriffspfade
Initialisierungsparameter
Statistiken für den Optimizer
Dynamic Sampling
Histogramme
Optimizer Hints
Planstabilität – Stored Outlines
Dr. Frank Haney
1
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
2
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
3
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
4
Optimizer – Geschichte
Dr. Frank Haney
5
Optimizer – 8
Allgemeine Optimizer Features ab Oracle 8
Ø Support für User Defined Data Types
Ø Index fast full scan
Ab Oracle 8.0 ausgenommen Oracle 8.1.5
Ø Consideration of bitmap access paths for tables with only B-tree
indexes
Ø Complex view merging
Ø Push-join predicate
Ø Ordered nested loop costing
Dr. Frank Haney
6
Optimizer – 8i
Allgemein ab Oracle 8i
Ø Support für partitionierte Objekte
Ø Support für partitioning pruning bei Joins
Ø Support für Query Re-Write (Oracle 8.1.6)
Ø Support für Function Based Index
Ø Improved outer join cardinality calculation
Ø Improved verification of NULLs inclusion in B-tree indexes
Ab Oracle 8.1.6
Ø Random distribution method for left of nested loops
Ø Type-dependent selectivity estimates
Ø Setting of optimizer mode for user recursive SQL
Ø Improved average row length calculation
Ø Partition pruning based on subquery predicates
Ab Oracle 8.1.7
Ø Common sub expression elimination
Ø Use statistics of a column imbedded in some selected functions such as TO_CHAR to
compute selectivity
Ø Improved partition statistics aggregation
Dr. Frank Haney
7
Optimizer – 9i
Allgemein ab Oracle 9i
ØLaufende Verbesserung bei MVIEW und Query Re-Write
ØNeue Partitioning Möglichkeiten
ØAb Oracle 9.2 werden CONNECT BY Queries anders
aufgelöst
ØVerbesserung bei Inline Views (Inline Views everywhere)
Ab Oracle 9iR2
ØPeeking at user-defined bind variables
ØIndex joins
ØSubquery unnesting
ØDynamic sampling
Dr. Frank Haney
8
Optimizer – 10g
Allgemeines
ØNeue Partitioning Möglichkeiten
ØSQL_PROFILES
Ab Oracle 10gR1
ØQuery re-write enables
ØSkip unusable indexes
ØAutomatically compute index statistics as part of
creation
ØCost-based query transformations
Ab Oracle 10gR2
ØAllow re-writes with multiple MVs and/or base tables
Dr. Frank Haney
9
Optimizer – 11g
Allgemeines
ØResult Caches
ØNeue Partitioning Möglichkeiten
ØIndex Hidden für Optimizer
ØPending Optimizer Statistiken
ØSQL_PATCHES
Ab Oracle 11g Release 1
ØEnhanced Bind Peeking (Adaptives Cursor Sharing)
ØUse extended statistics to estimate selectivity
ØPartition pruning using join filtering
ØGroup by placement optimization
Ab Oracle 11g Release 2
ØNative Full Outer Join
ØPartition Pruning mit Unusable Index Partitions
Dr. Frank Haney
10
Optimizermodi
Dr. Frank Haney
11
Optimizer - Syntax
Regelbasiert (RBO): Wählt den Ausführungsplan auf der Basis der durch
die Syntax gegebenen Zugangspfade und einer Rangfolge fester
Regeln. (In Oracle 10g nicht mehr unterstützt! Standard ist
ALL_ROWS.)
Kostenbasiert (CBO): Wählt den Ausführungsplan durch die Auswertung
von Statistiken über die beteiligten Datenbankobjekte.
Wahl des Optimizers:
Instanzebene:
OPTIMIZER_MODE = {CHOOSE|RULE |FIRST_ROWS[_n]|ALL_ROWS}
Sessionebene:
ALTER SESSION SET OPTIMIZER_MODE = {CHOOSE|RULE
|FIRST_ROWS[_n]|ALL_ROWS};
Anweisungsebene (Optimizer Hints):
SELECT /*+ [CHOOSE|RULE |FIRST_ROWS[_n]|ALL_ROWS] */ ... FROM
...
Dr. Frank Haney
12
Regelbasierter Optimizer
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
Single Row by ROWID (ROWID = konstant)
Single Row by Cluster Join
Single Row by Hash Cluster Key with Unique or Primary Key
Single Row by Unique or Primary Key
Clustered Join
Hash Cluster Key
Index Cluster Key
Composite Index
Single Column Indexes
Bounded Range Search on Indexed Columns
Unbounded Range Search on Indexed Columns
MAX or MIN of Indexed Columns
ORDER BY on Indexed Columns
Full Table Scan – Prinzip des RBO: FTS soll vermieden werden
Dr. Frank Haney
13
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
14
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
15
Welcher Optimizer?
Der regelbasierte Optimizer wird verwendet, wenn
Ø OPTIMIZER_MODE = RULE oder CHOOSE1
Es gibt Elemente, die den CBO erzwingen2, z.B.
Ø Alle Hints außer /*+ RULE */ und /*+ CHOOSE */ 1
Ø Joins mit ANSI-Syntax
Ø Index Organized Tables (IOT)
Ø Partitionierte Tabellen
Ø Reversed Key Indizes
Ø Funktionsbasierte Indizes
Ø Domain Indizes (Oracle Text und Intermedia)
Ø Bitmap Indizes
Ø Parallele Abfragen und DML
Ø SAMPLE-Klausel (Data Warehouse)
Ø Query Rewrite (Materialized Views)
1
2
Desupportet in 10g, Default jetzt ALL_ROWS
Wenn keine Statistiken vorhanden sind, nimmt der CBO Default-Werte
Hinweis: Bei Verwendung des CBO ist CHOOSE gleichbedeutend mit ALL_ROWS.
Dr. Frank Haney
16
CBO – Funktionsweise
Dr. Frank Haney
17
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: implizite Sortierung
Views: View Merging oder Predicate Pushing
a operator Konstante AND a=b (Transitivität): b operator Konstante
Dr. Frank Haney
18
Beispiel Abfragetransformationen I
Subquery Unnesting
Dr. Frank Haney
19
Beispiel Abfragetransformationen III
Auflösung Transitivität
Dr. Frank Haney
20
Beispiel Abfragetransformationen
Dr. Frank Haney
21
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
22
Wichtige Zugriffspfade
Ø
Ø
Ø
Full Table Scan: Alle Blöcke unter der HWM werden geprüft, ob
sie Daten enthalten, die der WHERE-Klausel entsprechen
Rowid Scan: Die Rowid enthält Datendatei, Block und Stelle im
Block, an der die Zeile zu finden ist.
Index Scan: Liefert die Rowid der gesuchten Zeilen durch
•
•
•
•
•
Index Unique Scan: Rückgabe einer ROWID (= auf Unique Index)
Index Range Scan: Rückgabe eines Zeilenbereichs (<,>,BETWEEN,
LIKE, ORDER BY)
Index Skip Scan: Zusammengesetzter Index wird logisch geteilt.
Führende Spalte braucht nicht referenziert zu werden. (Neu in Oracle
9i)
Index Fast Full Scan: Nur indizierte Spalten werden referenziert,
kein Tabellenzugriff nötig.
Index Join: Wenn alle in der Abfrage referenzierten Spalten indiziert
sind, ist kein Tabellenzugriff nötig.
Dr. Frank Haney
23
Initialisierungsparameter (Auswahl)
CURSOR_SHARING
EXACT, SIMILAR, FORCE
DB _FILE_MULTIBLOCK _READ _COUNT
Max. I/O-Größe des Systems
HASH_AREA _SIZE
2* SORT_AREA_SIZE
OPTIMIZER_FEATURES _ENABLED
Release-abhängiger Dachparameter
OPTIMIZER_INDEX _CACHING
0
OPTIMIZER_INDEX _COST _ADJ
100
OPTIMIZER_MODE
ALL_ROWS, FIRST_ROWS
OPTIMIZER_SECURE_VIEW_MERGING
TRUE, FALSE
PGA_AGGREGAT_TARGET
10 MB oder 20%der SGA
QUERY_REWRITE_ENABLED
TRUE, FALSE, FORCE
SORT_AREA_SIZE
65536 Byte
STAR_TRANSFORMATION_ENABLED
FALSE, TRUE
STATISTICS_LEVEL
BASIC, TYPICAL, ALL
Dr. Frank Haney
24
Hints für den CBO
Hints
Ø spezifizieren Ziele für den CBO
Ø definieren Zugriffspfade
Ø legen die Join-Reihenfolge fest
Ø spezifizieren eine Join-Methode
Ø steuern die Parallelausführung
Ø beeinflussen die Query Transformation
FIRST_ROWS
INDEX
ORDERED
USE_NL
PARALLEL
NO_MERGE
Syntax
{SELECT|UPDATE|DELETE|INSERT} /*+ hint */ ...
Hints werden ignoriert
Ø bei anderen als DML-Statements
Ø bei falscher Schreibweise, richtige im gleichen Statement werden
berücksichtigt
Ø wenn sie sich widersprechen, andere werden verwendet
Dr. Frank Haney
25
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()
Anmerkung: Ab Oracle 9i verwendet der CBO auch Systemstatistiken!
Dr. Frank Haney
26
Was wird gesammelt?
Tabellen:
Ø Zeilenzahl
Ø Anzahl der Blöcke
Ø Durchschnittliche Zeilenlänge
Ø Migration und Verkettung
Spalten:
Ø Anzahl unterschiedlicher Werte (Kardinalität)
Ø Anzahl der NULL-Werte
Ø Werteverteilung (Histogramme – wenn erforderlich)
Indizes:
Ø Anzahl der Blätter (Leaf Blocks)
Ø Zahl der Ebenen
System:
Ø I/O-Performance
Ø CPU-Verwendung
Dr. Frank Haney
27
Statistiksammeln überwachen
Überwachungsmodus einschalten: (Es wird aufgezeichnet, wieviel
DML auf den Tabellen stattfindet. In 10g immer an!)
Auf Datenbank- und Schemaebene:
DBMS_STATS.ALTER_DATABASE_TAB_MONITORING();
DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING();
Auf Tabellenebene mit der Klausel MONITORING von CREATE oder
ALTER TABLE
Die Prozedur DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
schreibt Informationen des Monitoring aus der SGA ins Data
Dictionary, in die View:
USER_TAB_MODIFICATIONS
Veraltete Statistiken erneuern (Statistiken gelten als veraltet, wenn
10% aller Zeilen einer Tabelle geändert wurden.):
Optionen GATHER STALE oder GATHER AUTO von
DBMS_STATS.GATHER_TABLE_STATS() etc.
Ab 10g gibt es automatischen Job zum Sammeln der Statistiken.
Dr. Frank Haney
28
Dynamische Stichproben
Dynamic Sampling, verfügbar ab Oracle 9i, bedeutet:
Stichproben, mit denen die Statistiken von Segmenten geschätzt werden
können, werden zur Laufzeit erstellt.
Wird eingestellt mit dem Parameter
OPTIMIZER_DYNAMIC_SAMPLING = 0 ... 10
Bei 0 ist die Funktion ausgeschaltet, 10 repräsentiert den höchsten Grad
dynamischer Stichproben.
Standardwert 2 (in 9i 1) bedeutet, Stichproben werden ausgeführt, wenn
• die Abfrage auf mehrere Tabellen zugreift.
• keine Tabellenstatistiken und kein Index verfügbar ist.
• der Optimizer einen Full Table Scan wählen würde.
Dynamic Sampling empfiehlt sich nur, wenn die Verbesserung des
Ausführungsplans den Overhead durch die Stichprobenerhebung
deutlich überwiegt.
Dr. Frank Haney
29
Histogramme I
Problem: Der CBO geht von einer Gleichverteilung der Daten über den
Wertebereich aus. Das ist häufig nicht der Fall. Der CBO verschätzt
sich dann beim Vergleich der Kosten von Indexzugriff und Full
Table Scan (Skewness-Problem).
Lösung: Histogramme
Syntax: ANALYZE TABLE ... FOR COLUMNS
View: DBA_TAB_HISTOGRAMS
Prinzip: Spaltenwerte werden in Buckets unterteilt, die in der Höhe
ausgeglichen werden. Standard sind 75 Buckets.
Beispiel: Verteilung der Werte 1 bis 100 auf 10 Buckets
Daten bezüglich der Werte gleichverteilt
10
20
30
40
50
60
70
80
90
100
5
5
10
10
20
35
60
100
ungleich verteilt
5
Dr. Frank Haney
5
30
Histogramme II
Hinweis: Wenn die Kardinalität der Spalte kleiner als die Zahl der
Buckets ist, wird kein höhenbasiertes, sondern ein wertebasiertes
Histogramm erstellt. Der Optimizer unterscheidet zwischen
häufigen und seltenen Spaltenwerten. Häufige Werte treten
mehrfach als Bucket-Endpunkt auf, seltene einmal oder gar nicht.
Histogramme sinnlos:
•
•
•
•
Spalte wird nicht in der WHERE-Klausel referenziert.
Spalte ist eindeutig und wird mit = verwendet.
Die Spaltendaten sind gleichmäßig verteilt. (Skewness gering)
Spalte wir nur mittels Bindevariable verwendet. (Bind Peeking)
Stichprobengöße für Histogramme: Mindestens 100 Zeilen pro Bucket.
Methoden:
•
•
•
REPEAT: Nur für Spalten, die bereits Histogramme haben
AUTO: Entscheidung auf der Basis von Verwendung und Verteilung
SKEWONLY: Entscheidung nur auf Grundlage der Verteilung
Dr. Frank Haney
31
Planstabilität (Stored Outlines)
Identische Ausführungspläne werden erreicht durch
Ø Photographisch gleiche SQL
Ø Hints
Ø Bindevariablen statt Literale
Ø Stabilität der Statistiken
Ø Im Dictionary gespeicherte Ausführungspläne (stored outlines)
Ø SQL Profile (SQL Tuning Advisor)
Stored Outlines erzeugen:
Init.ora: Parameter CREATE_STORED_OUTLINES = [TRUE|kategorie]
Session: ALTER SESSION SET CREATE_STORED_OUTLINES = [TRUE|kategorie];
Explizit: CREATE OR REPLACE OUTLINE name FOR CATEGORY kategorie ON
SQL-Anweisung;
Stored Outlines verwenden:
Init.ora: Parameter USE_STORED_OUTLINES = [TRUE|kategorie]
Session: ALTER SESSION SET USE_STORED_OUTLINES = [TRUE|kategorie];
Stored Outlines verwalten:
Schema OUTLN, Package OUTLN_PCK, Views DBA_OUTLINES, DBA_OUTLINE_HINTS
Dr. Frank Haney
32
Logik der Erstellung von Ausführungsplänen
1.
2.
3.
4.
5.
6.
Ist die SQL im Shared Pool? Ja/Nein
Ø Ja
→2
Ø Nein
→3
Liegt die gleiche Outlinekategorie vor? Ja/Nein
Ø Ja
→5
Ø Nein
→3
Gibt es im Data Dictionary eine übereinstimmende Outline? Ja/Nein
Ø Ja
→4
Ø Nein
→6
Die Outline wird integriert und der Plan erstellt. Danach 5.
Der Outline-Plan wird ausgeführt.
Normale SQL-Ausführung
Dr. Frank Haney
33
Outlines bearbeiten
Outlines sind öffentliche Objekte. Private Outlines unterstützen in Oracle 9i
die Bearbeitung:
Ø Die Outline wird in einen temporären Speicherbereich geklont.
Ø Der Benutzer bearbeitet in seiner Session die Outline.
Ø Danach wird die Outline zur allgemeinen Verwendung veröffentlicht.
Ablauf:
1. Bearbeitungstabellen erstellen: DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES
2. Klonen der Outline: CREATE PRIVATE OUTLINE p_out1 FROM out1;
3. Bearbeiten der Outline in OL$HINTS oder mit dem OEM
4. Resynchronisieren mit CREATE PRIVATE OUTLINE p_out1 FROM p_out1;
5. Testen der Änderungen: Parameter USE_PRIVATE_OUTLINE=TRUE
6. Veröffentlichen der Outline: CREATE OUTLINE out1 FROM PRIVATE p_out1;
7. Private Outlines deaktivieren: USE_PRIVATE_OUTLINE=FALSE
Dr. Frank Haney
34