SQL-Tuning

Tuning
Johannes Ahrends
Technical Director Presales Consulting
Quest Software GmbH
Copyright © 2006 Quest Software
Agenda
•
•
•
•
•
Was ist Tuning?
Beispiele aus der Praxis
Der Optimizer
Kleine Historie
Tuningmöglichkeiten
– Statistiken
– Zugriffsmethoden
– Hints
• Tuningwerkzeuge
• Datenbank-Parameter
• Vorausschauende Programmierung
1
Was ist Tuning?
Tuning ist die Summe aller
Ma
ßnahmen, welche die
Maßnahmen,
Optimierung der Antwortzeiten und
die Verbesserung der
Skalierbarkeit zum Ziele haben.
(Dr. Günter Unbescheid)
2
Tuningpotential
• Klassische Anforderung:
– 90% des Optimierungspotential liegt in den SQL-Befehlen
HW
Network
DB Changes
Indexes
90%
60%
SQL Statements
Quelle: Forrester Research
3
Übliche Beschwerden (1)
• Ein Anwender beschwert sich über die schlechten
Antwortzeiten der Applikation
• Grund?
–
–
–
–
Schlecht geschlafen
Schlechtes Wetter
Streit mit dem (Ehe)-Partner
Fehlerhafte Bedienung
• Lösung?
– Andere Anwender fragen (schwierig bei schlechtem Wetter)
– Nachsehen, ob in der Datenbank alles in Ordnung ist
– Tägliche oder stündliche Vergleichsmessungen
4
Übliche Beschwerden (2)
• Die Anwender beschweren sich über die schlechten
Antwortzeiten der Applikation
• Grund?
– Neue Anwendung
– Falsche Statistiken
– Anwendungen „kommen sich in die Quere“
• Lösung?
–
–
–
–
Anwendungen testen
Statistiken „einfrieren“
Anwendungen entkoppeln
Alarmierung bei zu langen Batchläufen
5
Übliche Beschwerden (3)
• Nach der Umstellung auf ein neues Release ist die
Anwendung wesentlich langsamer
• Grund?
– Anwender wollen die neue Anwendung nicht (torpedieren)
– Falsche Statistiken
– Falsche Parametrierung
• Lösung?
– Anwender schulen
– Neue Statistiken erstellen
– Datenbank / Instanzparameter überprüfen
6
Ein paar Praxisfälle (1)
• Problem:
– Nach Umstellung einer Datenbank von Version 8i auf Version 9i
beschweren sich die Anwender (1500!) darüber, dass die
Performance immer wieder einbricht (unkalkulierbar).
• Grund:
– In Version 9i wurden u.A. zwei neue Parameter eingeführt:
pga_aggregate_target und workarea_size_policy
– Default:
• pga_aggregate_target = 32 MB
• workarea_size_policy = AUTO Î sort_area_size ignoriert
• Lösung:
• pga_aggregate_target = 1000 MB
7
Ein paar Praxisfälle (2)
• Problem:
– Eine Anwendung scheint immer wieder zu hängen
• Grund:
– Aufgrund mangelndem Oracle Knowhow wurde die Datenbank
(Oraclei8i) mit Default-Einstellungen installiert
– Default für Redolog-Dateien (bis Oracle8i) Î 3 x 512 KB
• Lösung:
– Drei neue Redolog-Dateien mit je 50 MB
8
Ein paar Praxisfälle (3)
• Problem:
– Eine einfache Abfrage dauert ca. 5 Minuten
SELECT DISTINCT produktname,
warenwelt,
gruppe
FROM
produktinformationen
9
Grund: View auf View auf View auf …
10
Regelbasierter Optimizer (RBO)
• Grundsätzlich
– Untersucht so viele Pläne wie Tabellen in der Abfrage vorkommen
– Jeder Plan korrespondiert mit einer Tabelle am Anfang der Liste
– Nachfolgende Tabellen werden entsprechend der günstigsten
Zugriffsmethode angeordnet Î Join-Reihenfolge
– Bei jedem Join wählt der Optimizer zwischen Nested-Loop und
Sort-Merge
– Der (nach verschiedenen Kriterien) beste Plan wird behalten
– Bei gleicher Effizienz wird der Plan gewählt, der mit der letzten
Tabelle der FROM-Klausel beginnt
11
Kostenbasierter Optimizer (CBO)
• Grundsätzlich
– Analyse für n Permutationen der in der Abfrage vorkommenden
Tabellen durchgeführt
• n wird über den Initialisierungsparameter
optimizer_max_permutations gesetzt (Default 2000)
– Für jede Permutation werden verschiedene Pläne analysiert
(Zugriffs- und Join-Methoden unter Berücksichtigung aller hints),
und der mit den geringsten Kosten wird behalten
– Schließlich wird die kostengünstigste Permutation (und ihr Plan)
genutzt
• Die Kosten
– basieren auf der Anzahl logischer Reads, verfügbarem Speicher,
Prozessorauslastung
– werden aufgrund der Statistiken, die für die verschiedenen
Objekte verfügbar sind, kalkuliert
12
RBO oder CBO
• RBO
– Wird nicht von Oracle empfohlen
(kein Support mehr ab Oracle 10g)
– Neue Technologien (Bitmap, Hash Join, Partioning, …) werden
nicht angewendet
– Erlaubt keine Hints auf Statement-Ebene
• CBO
– von Oracle empfohlen
– Je besser die Statistiken, desto besser der Ausführungsplan!
13
Eine kleine Historie
•
•
•
•
•
•
Oracle 6:
Oracle 7:
Oracle 8:
Oracle8i:
Oracle9i:
Oracle 10g:
„Erste Tuningerfolge“
„Cost-Based-Optimizer“
„Statistiken fälschen“
„Function-Based Index“
„Systemstatistiken“
„Automatisches Tuning“
14
Oracle 6: Erste Tuningerfolge
• SQL:
– Rule-Based Optimizer -14 definierte Regeln
– Optimale Ergebnisse nur durch optimale Programmierung
• Wichtige Entwicklungen:
–
–
–
–
Extents
Tablespaces mit mehreren Datafiles
Row-level-locking
PL/SQL Programmierung (Procedural Option)
• Parameter:
– row_level_locking
– db_block_buffer
– log_buffer
• Datenbankgröße: < 1 GB
15
Oracle 7: Cost Based Optimizer
• SQL:
– Data-Warehouse Anwendungen bedingen den „intelligenten
Optimierer“
– Objektstatistiken als Bewertungsgrundlage
– Hohe Zeiten für Parsing (Permutationen)
• Wichtige Entwicklungen:
– Shared-Pool für die Wiederverwendung von Ausführungsplänen
– Bitmapped-Index
– Parallel Query
• Parameter:
– optimizer_mode
– shared_pool_size
• Datenbankgröße: 1 – 10 GB
16
Oracle 8: Statistiken fälschen
• SQL:
– Statistiken können angepasst und gespeichert werden
– Parallelisierung durch Partitionierung
• Wichtige Entwicklungen:
–
–
–
–
Partitionierung
Index Organized Tables
Reverse Key Indexing
dbms_stats Package
• Parameter:
– optimizer_max_permutation
– db_file_multiblock_read_count
• Datenbankgröße: 10 - 100 GB
17
Oracle8i: Function-Based Index
• SQL:
– Zwischenspeicherung von Ergebnissen in temporäre Tabellen
– Anwender können in Ressourcengruppen aufgeteilt werden
• Wichtige Entwicklungen:
–
–
–
–
–
–
Locally Managed Tablespaces
Online Index Reorganistion
Query Rewrite
Function Based Index
Plan Stability
Temporäre Tabellen
• Parameter:
– optimizer_index_cost_adj
– cursor_sharing
• Datenbankgröße: 10 - 100 GB
18
Oracle9i: Systemstatistiken
• SQL:
– Bessere Ressourcennutzung durch Systemstatistiken
– Reproduzierbare Ergebnisse durch Flashback Query
• Wichtige Entwicklungen:
–
–
–
–
–
–
Oracle Real Application Clusters
Automatic Segment Space Management
Multiple Blocksize
UNDO-Tablespaces
Native PL/SQL Compilation
Index Simulation
• Parameter:
– sga_max_size
– pga_aggregate_target
• Datenbankgröße: 100 – 2.000 GB
19
Oracle 10g: Automatisches Tuning
• SQL:
– Automatische Anpassung der Ressourcen für Buffer-Cache und
Shared Pool
– Automatisches Monitoring aller Objekte für optimale Statistiken
• Wichtige Entwicklungen:
–
–
–
–
–
–
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Automatic Storage Management (ASM)
Online Shrink
Automatic Memory Management
Active Session History Report (ASH)
• Parameter:
– sga_target
• Datenbankgröße: 100 – 20.000 GB
20
Was bietet das größte Tuningpotential?
•
V6
–
–
–
–
–
•
Umschreiben der Anwendung / Befehl
Indizierung
I/O Verteilung
Datenbank / Instanz Parametrierung
Reorganisation
V7
– Statistiken
– Hints
•
V9
– Real Application Clusters
– Systemstatistiken
– Unterschiedliche Blocksizes
•
V10
– feingranulare Statistiken
21
Erstellung von Statistiken
• Befehl ANALYZE
–
–
–
–
Mit Oracle7 eingeführt
Analysiert Tabellen und Indizes
Keine Systemstatistiken
Füllt Spalten chain_cnt, avg_space, avg_row_len
• Package dbms_utility
– analyze_schema
– analyze_database
– Führt den Befehl ANALYZE für mehrere Objekte aus
• Package dbms_stats
– Analysiert Objekt, Schema, Datenbank oder System
22
DBMS_STATS Package
• Erweiterte Analyse-Möglichkeiten für Partitionen,
Subpartitionen, geclusterte Tabellen, lokale und
globale Indizes
• Statistik-Gewinnung seriell oder parallel
• Nur Statistik-Werte für CBO werden ermittelt,
chained_rows, average_space, unused_blocks
werden nicht gepflegt
23
Automatische Statistiken
• Über DBMS_STATS-Package
DBMS_STATS.GATHER_SCHEMA_STATS('JAHR',
DBMS_STATS.GATHER_SCHEMA_STATS('JAHR',
options
options =>
=> 'GATHER
'GATHER AUTO');
AUTO');
DBMS_STATS.GATHER_DATABASE_STATS(
DBMS_STATS.GATHER_DATABASE_STATS(
options
options =>
=> 'GATHER
'GATHER AUTO');
AUTO');
• Erstellt neue Statistiken für Objekte, die sich um mehr
als 10% geändert haben
• Voraussetzung:
– Option MONITORING für Tabellen gesetzt
– dbms_stats.alter_schema_table_monitoring('SCOTT')
– dbms_stats.alter_database_table_monitoring(TRUE)
24
Gespeicherte Statistiken
• Statistiken können über DBMS_STATS gespeichert,
historisiert, ausgewertet und zurückgesetzt werden
• Prozeduren für Export, Import, Set der Statistiken
• Statistiken können somit „gefälscht“ werden, um das
Verhalten des CBO in einer Testdatenbank mit
Datenmengen aus der Produktion simulieren zu
können
25
Plan Stability
• Ausführungspläne werden als sog. Stored outlines
gespeichert
• Konservieren von Plänen, um stabile
Ausführungszeiten zu garantieren
• Stored Outlines können zwischen Systemen
transportiert werden
• Unterschiedliche „Kategorien“ von Stored Outlines
möglich
• User OUTLN mit 2 Tabellen OL$ und OL$HINTS
• Parameter
– QUERY_REWRITE_ENABLED = TRUE
26
Zugriffsmethoden
• Oracle ermöglicht den Zugriff auf Tabellendaten auf
drei Arten
– Lesen der gesamten Tabelle (Full Table Scan)
• Zugriff über Index
–
–
–
–
–
B*Tree Index
Bitmapped Index
Function-Based Index
Bitmapped Join Index
Index Organized Table (IOT)
• Zugriff über die direkte Angabe einer Row-ID
27
Full Table Scan
• Die gesamte Tabelle wird durch sogenannte multiblock-reads gescannt, deren Größe (in Blöcken) durch
den Parameter DB_FILE_MULTIBLOCK_READ_COUNT
festgelegt wird
• Alle Blöcke unterhalb der HWM = High Water Mark
werden gelesen
• Der Parameter beeinflusst den Cost Based Optimizer
28
B*Tree Index
SQL> SELECT * FROM PERSONEN WHERE PERSNR = 4711;
A
AA
PERSNR
PERSNR
BLOCK
BLOCK
11
–– 3000
3000
3001
3001 –– 8000
8000
……
AA
BB
Root-Block
B
PERSNR
PERSNR
BLOCK
BLOCK
PERSNR
PERSNR
BLOCK
BLOCK
11
100
100
……
AA
AA
AB
AB
3001
3001
4650
4650
……
BA
BA ……
BX
BX
––
––
100
100
230
230
––
––
3192
3192
4798
4798
Branch-Block
BX
PERSNR
PERSNR
ROWID
ROWID
PERSNR
PERSNR
ROWID
ROWID
11
22
……
b.aaa.10
b.aaa.10
b.ae1.03
b.ae1.03
4653
4653
……
4711
4711
b.aaa.03
b.aaa.03
Leaf-Block
b.abc.06
b.abc.06
PERSNR
PERSNR
VORNAME
VORNAME
NACHNAME
NACHNAME …
…
11
147
147
4711
4711
Hans
Hans
Klaus
Klaus
Trude
Trude
Meier
Meier
Schmidt
Schmidt
Herr
Herr
Table-Block
29
B*Tree Index
• Balanced Tree Index-Organisation
• Der Index wird, beginnend am Ursprung, blockweise
gescannt, dann erfolgt der Zugriff auf die
Tabellenblöcke
• Characteristika
– Speicherintensiv
– Speichert keine NULL-Werte
• Gute Performance bei
– Selektiven Auswahlbedingungen
– Zeilen mit gleichen Schlüsselwerten, die so weit wie möglich in
gleichen Tabellenblöcken gruppiert sind
30
B*Tree Index
• Effizient für Daten mit hoher Kardinalität.
– Beispiel: Ein Index auf einer date-Spalte
• Länge = 24 Byte Î 8Byte (Länge des Datentyps) + 10 Byte
(Länge der Rowid) + 6 Byte (Kontrollinformation)
• Bei einer Blockgröße (DB_BLOCK_SIZE) von 8 Kbyte ergibt
sich verfügbarer Raum von 8192 bytes pro Block –
Headergröße (zwischen 100 and 200 Bytes – im Beispiel 192
Bytes angenommen) = 8000 Bytes, zu 75% gefüllt = 6000
Bytes
• Ein Block kann also 6000 / 24 = 250 Schlüssel beinhalten.
• Ein Baum mit einer Tiefe von 3 kann demzufolge 250 x 250 x
250 = 15.625 Millionen Schlüssel verarbeiten.
• Um einen von 15 Millionen Werten zu ermitteln, genügen 3 I/OOperationen im Index plus eine I/O-Operation pro Zeile in der
Tabelle
31
B*Tree Index
• Ineffizient für:
– Einen Index auf der Primärschlüsselspalte einer kleinen Tabelle
(z.B. 10 Zeilen)
– Einen Index auf einer Spalte mit wenigen unterschiedlichen
Werten = niedrige Kardinalität (z.B. Geschlecht)
– Einen Index auf einer Spalte, in der oft Funktionen verwendet
werden (z.B. upper(lastname))
– Ein effizientes I/O-System mit Parallelisierungsmöglichkeiten
(z.B. db_file_multiblock_read_count hoch)
• In diesen Fällen sollte der Optimizer keine Indizes
verwenden
32
B*Tree Indexregeln
• Selektivität der Abfragen gewährleisten, die den
Indexschlüssel verwenden: weniger als 5 bis 15% der
Zeilen einer Tabelle als Ergebnis (Abhängig von der
Datenverteilung in der Tabelle)
• Spaltenreihenfolge bei Verwendung
zusammengesetzter Indizes beachten
• Keine Indizes bei kleinen Tabellen (außer uniqueIndizes)
• Für unique-Indizes PRIMARY KEY-oder UNIQUE
Constraints verwenden
• Abfragen für die Verwendung von Indizes optimieren
• DML-Performanceeinflüsse beachten
33
Bitmapped Index
• Jeder Schlüsselwert wird als Bitmap-Segment
gespeichert; der Zugriff auf Segmente erfolgt durch
einen B-Tree-Index
• Der Index wird wie ein B-Tree gescannt, anschließend
wird die Bitmap in ROWID's konvertiert
• Characteristika
– Weniger speicheraufwändig
– Speichert NULL-Werte
– Starker Einfluss auf DML-Operationen
• Effizient bei
– Kombinierten, nicht sehr selektiven Auswahlbedingungen
– Schnelle AND- und OR-Kombinationen über mehrere Indizes
34
Bitmapped Index
• Anwendungsregeln
– Mehr bei Abfragesystemen (OLAP, Datawarehouse) als bei
Transaktionssystemen (OLTP)
– Indexspalten mit geringer Kardinalität werden bei mehreren
Bedingungen durch AND und OR kombiniert
– Starker Performanceinfluss auf DML-Operationen
• Anlegen:
SQL> CREATE BITMAP INDEX name ON (column_list) ;
• Spezielle Parameter
– CREATE_BITMAP_AREA_SIZE:
– BITMAP_MERGE_AREA_SIZE:
Default-Wert = 8MB = OK
Default-Wert = 1MB = OK
35
Function Based Index
• Ein B-Tree Index, auf das Ergebnis einer Funktion
• Erlaubt die effiziente Verarbeitung von SQLStatements einschließlich der Verwendung von
Funktionen in der WHERE-Klausel
• Voraussetzungen
– QUERY_REWRITE_ENABLED = TRUE
(auf Instanz- oder Sitzungsebene)
– Statistiken für die Tabelle und den Index
– Costbased Optimizer
• Verhalten wie B*Tree Index
• Index kann bei Änderung der Funktion unusable
werden
36
Index Organized Table (IOT)
• Die gesamte Tabelle wird im B-Tree Index des
Primärschlüssels gespeichert
• Zugriff erfolgt wie beim B-Tree Index, jedoch ohne
Sekundärstruktur
• Characteristika
– Keine ROWID
– Zusätzliche Indizes sind möglich
– Geringerer Platzbedarf
– Overflow-area
• Effizient bei
– Zugriff mit Bereichs- oder Gleichheitsbegrenzenden Eigenschaften
des Primärschlüssels
37
Index Organized Table (IOT)
• Anwendungsregeln
– Eher für Tabellen geeignet, auf die allein über den Primärschlüssel
zugegriffen wird
– Eher für Tabellen geeignet, deren Zeilengröße geringer ist als die
halbe Blockgröße
38
Reverse Key Indexes
• Ein B-Tree-Index mit umgekehrter Anordnung der
Bytes in jeder Schlüsselspalte
• Index wird wie ein normaler B-Tree gescannt
• Characteristika
– Benachbarte Werte liegen im Index weit auseinander
• Effizient bei
– Index auf Primärschlüssel oder Datumsfelder, da hier in der Regel
immer ans Ende eingefügt wird
• Nachteile
– Keine Range-Scans möglich
• Anlegen
–
CREATE INDEX name ON (columns_list) REVERSE;
39
Bitmapped Join Index
• Speichert die Rowid's einer Tabelle (fact table) mit der
Spalteninformation einer anderen Tabelle (dimension
table)
• Wird hauptsächlich in Data Warehouses verwendet
CREATE
CREATE BITMAP
BITMAP INDEX
INDEX b_join_vertrieb
b_join_vertrieb ON
ON
DWH_KUNDEN(m.m_nachname)
DWH_KUNDEN(m.m_nachname)
FROM
FROM dwh_kunden
dwh_kunden k,
k, mitarbeiter
mitarbeiter mm
WHERE
WHERE k.vertrieb
k.vertrieb == m.m_nr
m.m_nr
COMPUTE
COMPUTE STATISTICS;
STATISTICS;
40
Handhabung von Indizes
• Beim Anlegen von Indizes zu beachten:
– Große SORT_AREA_SIZE
– Möglichkeiten der Parallelisierung nutzen
(Vorsicht! SORT_AREA_SIZE gilt für jeden Prozess)
– nologging-Option nutzen
– Speicherung in separatem Tablespace (IO-Verteilung)
• Administration:
– Tiefe eines Indexbaumes beobachten
SQL>
SQL> SELECT
SELECT index_name,
index_name, num_rows,
num_rows, blevel
blevel
FROM
FROM dba_indexes
dba_indexes
WHERE
WHERE blevel
blevel >> 2;
2;
41
Beispiel
SELECT
SELECT **
FROM
FROM auftraege
auftraege
WHERE
WHERE to_char(aufdatum,'DD.MM.YYYY')
to_char(aufdatum,'DD.MM.YYYY') == '05.10.2006'
'05.10.2006'
Indizierung?
CREATE
CREATE INDEX
INDEX idx_aufdatum
idx_aufdatum
ON
auftraege
ON
auftraege (to_char(aufdatum,'DD.MM.YYYY'));
(to_char(aufdatum,'DD.MM.YYYY'));
Bessere Lösung?
SELECT
SELECT **
FROM
FROM auftraege
auftraege
WHERE
WHERE aufdatum
aufdatum >=
>= to_date('05.10.2006',
to_date('05.10.2006', 'DD.MM.YYYY')
'DD.MM.YYYY')
AND
aufdatum
AND
aufdatum << to_date('05.10.2006',
to_date('05.10.2006', 'DD.MM.YYYY')
'DD.MM.YYYY') ++ 11
42
Zugriffsmethoden
• Full Table Scan
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
ort
WHERE
ort == 'Köln';
'Köln';
Execution
Execution Plan
Plan
------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 TABLE
TABLE ACCESS
ACCESS (FULL)
(FULL) OF
OF 'PERSONEN'
'PERSONEN'
43
Zugriffsmethoden
• ROWID bekannt
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
rowid
WHERE
rowid == 'AAALWYAAGAAAJiiAAA';
'AAALWYAAGAAAJiiAAA';
Execution
Execution Plan
Plan
------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 TABLE
TABLE ACCESS
ACCESS (BY
(BY USER
USER ROWID)
ROWID) OF
OF 'PERSONEN'
'PERSONEN'
44
Zugriffsmethoden
• Unique B-Tree-Index
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
persnr
WHERE persnr == 4711;
4711;
Execution
Execution Plan
Plan
------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 TABLE
TABLE ACCESS
ACCESS (BY
(BY INDEX
INDEX ROWID)
ROWID) OF
OF 'PERSONEN'
'PERSONEN'
22
11
INDEX
INDEX (UNIQUE
(UNIQUE SCAN)
SCAN) OF
OF 'PK_PERSONEN'
'PK_PERSONEN'
(UNIQUE)
(UNIQUE)
Eindeutiger B-Tree-Index PK_PERSONEN
auf PERSONEN (persid)
45
Zugriffsmethoden
• Wertebereich oder Non-Unique B-Tree-Index
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
nachname
WHERE
nachname == 'Ahrends';
'Ahrends';
Execution
Execution Plan
Plan
--------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 TABLE
TABLE ACCESS
ACCESS (BY
(BY INDEX
INDEX ROWID)
ROWID) OF
OF 'PERSONEN'
'PERSONEN'
22
11
INDEX
INDEX (RANGE
(RANGE SCAN)
SCAN) OF
OF 'IDX_NAME'
'IDX_NAME'
(NON-UNIQUE)
(NON-UNIQUE)
Einfacher B-Tree-Index IDX_NAME
auf PERSONEN (nachname)
46
Zugriffsmethoden
• B-Tree-Index ohne Zugriff auf die Tabelle
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
WHERE nachname
nachname == 'Ahrends';
'Ahrends';
Execution
Execution Plan
Plan
--------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 INDEX
INDEX (RANGE
(RANGE SCAN)
SCAN) OF
OF 'IDX_NAME'
'IDX_NAME'
B-Tree-Index IDX_NAME
auf PERSONEN (nachname, vorname)
47
Zugriffsmethoden
• Zugriff durch vollständigen B-Tree-Scan
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
vorname
WHERE
vorname == 'Johannes';
'Johannes';
Execution
Execution Plan
Plan
------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 INDEX
INDEX (FAST
(FAST FULL
FULL SCAN)
SCAN) OF
OF 'IDX_NAME'
'IDX_NAME'
(NON-UNIQUE)
(NON-UNIQUE)
B-Tree-Index IDX_NAME
auf PERSONEN (nachname, vorname)
48
Zugriffsmethoden
• Zugriff durch Bitmap-Index auf einem einzelnen Wert
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
FROM personen
personen
WHERE
WHERE ort
ort == 'Köln';
'Köln';
Execution
Execution Plan
Plan
------------------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 TABLE
TABLE ACCESS
ACCESS (BY
(BY INDEX
INDEX ROWID)
ROWID) OF
OF 'PERSONEN'
'PERSONEN'
22
11
BITMAP
BITMAP CONVERSION
CONVERSION (TO
(TO ROWIDS)
ROWIDS)
33
22
BITMAP
BITMAP INDEX
INDEX (SINGLE
(SINGLE VALUE)
VALUE) OF
OF 'BIDX_ORT'
'BIDX_ORT'
Bitmap-Index BIDX_ORT auf PERSONEN (ort)
49
Zugriffsmethoden
• Zugriff durch Bitmap-Index auf mehreren Werten
SELECT
SELECT vorname,
vorname, nachname
nachname
FROM
personen
FROM
personen
WHERE
ort
WHERE ort == 'Köln'
'Köln' AND
AND nachname
nachname == 'Ahrends';
'Ahrends';
Execution
Execution Plan
Plan
----------------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
11
00 TABLE
TABLE ACCESS
ACCESS (BY
(BY INDEX
INDEX ROWID)
ROWID) OF
OF 'PERSONEN'
'PERSONEN'
22
11
BITMAP
BITMAP CONVERSION
CONVERSION (TO
(TO ROWIDS)
ROWIDS)
33
22
BITMAP
AND
BITMAP AND
44
33
BITMAP
BITMAP INDEX
INDEX (SINGLE
(SINGLE VALUE)
VALUE) OF
OF 'BI_ORT'
'BI_ORT'
55
33
BITMAP
INDEX
(SINGLE
VALUE)
OF
'BI_NAME'
BITMAP INDEX (SINGLE VALUE) OF 'BI_NAME'
Bitmap-Index BI_ORT auf PERSONEN (ort)
Bitmap-Index BI_NAME auf PERSONEN (nachname)
50
Hints
• Optimizer-Anweisungen für ein Statement
– Änderung des Optimizer-Modus' für die Dauer der Abfrage
(gilt für RBO und CBO)
– präzise Anweisungen an den Optimizer zur Ausführung eines
Statements (gilt nur für CBO)
• Für SELECT, UPDATE, DELETE oder Subqueries
• Syntax
– Kommentar gefolgt von einem Pluszeichen (+)
SELECT /*+ … */ deviceid FROM device;
– Können kombiniert werden
– Tabellen-Alias muss im Hint verwendet werden
• Ein fehlerhafter Hint wird ignoriert
51
Wichtige Hints - Zugriff
• /*+ FULL(table) */
– Zugriff über Full Table Scan
• /*+ INDEX(table [index, …]) */
– Zugriff über Index
• /*+ APPEND */
– Direct-Path Inserts möglich (Nologging-Option)
Standard bei paralleler Verarbeitung
52
Wichtige Hints - Join
• /*+ ORDERED */
– Join der Tabellen in der Reihenfolge der FROM-Klausel
• /*+ USE_NL(table1 [,table2,…]) */
– Nested-Loop-Join (verschachtelte Schleifen)
– Angegeben werden die Tabellen, die an eine bereits existierende
Ergebnissmenge angehängt werden sollen
• /*+ USE_MERGE(table1 [table2, …]) */
– Tabellen werden nach Join-Kriterium sortiert und dann verglichen
(Einstellung sort_area_size wichtig!)
• /*+ DRIVING_SITE(table) */
– Ausführung auf einer bestimmten Seite (in verteilten
Umgebungen)
53
Beispiel
54
Welches sind die wichtigsten Tools?
•
V6
–
–
–
–
–
–
•
Explain Plan
tkprof
bstat / estat
V$SQLAREA / V$SQLTEXT
SQL*Plus autotrace + timing
nst-Skripte
V7
– Oracle Enterprise Manager
•
V8
– Statspack
•
V9
– V$SQL / v$SQLPLAN
•
V10
–
–
–
–
ADDM
AWR
ASH
Anwendungstracing (dbms_monitor)
55
Explainplan
SQL>
SQL>
SELECT
SELECT aufnr
aufnr
FROM
positionen
FROM
positionen
WHERE
aufnr
WHERE aufnr not
not in
in (SELECT
(SELECT aufnr
aufnr FROM
FROM auftraegestorno);
auftraegestorno);
Execution
Execution Plan
Plan
------------------------------------------------------------------------------------------------------------------00
SELECT
SELECT STATEMENT
STATEMENT Optimizer=CHOOSE
Optimizer=CHOOSE
(Cost=740
(Cost=740 Card=49442
Card=49442 Bytes=395536)
Bytes=395536)
11
00 NESTED
LOOPS
(ANTI)
NESTED LOOPS (ANTI)
(Cost=740
(Cost=740 Card=49442
Card=49442 Bytes=395536)
Bytes=395536)
22
11
INDEX
INDEX (FAST
(FAST FULL
FULL SCAN)
SCAN) OF
OF 'PK_AUFTRAGSNR'
'PK_AUFTRAGSNR' (UNIQUE)
(UNIQUE)
(Cost=75
(Cost=75 Card=149639
Card=149639 Bytes=598556)
Bytes=598556)
33
11
INDEX
(UNIQUE
SCAN)
OF
'PK_AUFTRAEGE'
INDEX (UNIQUE SCAN) OF 'PK_AUFTRAEGE' (UNIQUE)
(UNIQUE)
(Cost=1
Card=100197
Bytes=400788)
(Cost=1 Card=100197 Bytes=400788)
• Die Anzeige kann je nach verwendetem Tool leicht
variieren, die Schlüsselwörter bleiben jedoch die
selben
56
Einen Ausführungsplan verstehen
• Beim kostenbasierten Optimizer sind zusätzliche
Informationen verfügbar
(nur AUTOTRACE oder EXPLAIN PLAN)
– COST: geschätzte Kosten (Information vom Optimizer)
– CARD (CARDINALITY): Anzahl der geschätzen Zeilen
– BYTES: Anzahl der geschätzen Bytes
57
Anwendungstracing
• Tracing von Anwendungen
– Erstellt eine Tracedatei im Verzeichnis user_dump_dest
ALTER
ALTER SESSION
SESSION SET
SET SQL_TRACE
SQL_TRACE == TRUE;
TRUE;
oder
oder
SID
SID und
und SERIAL#
SERIAL# aus
ausv$session
v$session ermitteln
ermittelnund
unddann:
dann:
execute
execute
dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);
dbms_system.set_sql_trace_in_session(SID,SERIAL#,TRUE);
• Tracedateien anschließend mit tkprof bearbeiten:
tkprof
tkprof <tracename>
<tracename> <outputdatei>
<outputdatei>
explain=<benutzer>/<kennwort>
explain=<benutzer>/<kennwort> [sys=no]
[sys=no]
[sort=<sortierung>]
[sort=<sortierung>]
58
SQL*Plus Autotrace
• Ausführung von Befehlen aus SQL*Plus
– Voraussetzung: PLAN-Table muss existieren
SQL> @?/rdbms/admin/utlxplan
– Nur möglich, wenn keine Bindevariablen verwendet werden
SET
SET AUTOTRACE
AUTOTRACE ON;
ON;
SET
SET AUTOTRACE
AUTOTRACE TRACEONLY;
TRACEONLY;
• Erstellung eines Ausführungsplans, ohne ein
Statement tatsächlich auszuführen
EXPLAIN
EXPLAIN PLAN
PLAN
[[ SET
SET STATEMENT_ID
STATEMENT_ID == 'name'
'name' ]]
[[ INTO
INTO <MYPLANTABLE>
<MYPLANTABLE> ]]
FOR
<statement>;
FOR
<statement>;
59
SQL-Tuning - V$SQL_PLAN
• Enthält für noch im Cache befindliche Cursor den
Ausführungsplan
• Spart EXPLAIN PLAN Kommando bzw.
entsprechendes Werkzeug
• Größere Sicherheit für den „richtigen“ Plan
60
SQL-Tuning - V$SQL_PLAN_STATISTICS
• Enthält für jedes Element des Ausführungsplans
(V$SQL_PLAN) zugehörige Statistiken (DISK_READS,
BUFFER_GETS, EXECUTIONS)
• Erstmals Kosten für Teilpläne!
• STATISTICS_LEVEL = ALL
SELECT
SELECT
FROM
FROM
a.*,
a.*, b.*
b.*
v$sql_plan
v$sql_plan a,
a,
v$sql_plan_statistics
v$sql_plan_statistics bb
WHERE
WHERE a.id
a.id == b.operation_id
b.operation_id
AND
a.address
AND
a.address == b.address
b.address
AND
a.address
AND
a.address == '78425FBC'
'78425FBC'
61
Statspack und AWR
• Schema perfstat mit einem Satz von StatistikTabellen
• Fügt mit jeder Ausführung der Prozedur
statspack.snap die momentanen Werte aus
unterschiedlichen V$-Tabellen in die Statistiktabellen
mit einer so genannten Snap-ID ein
• Auswertung von Zeiträumen zwischen zwei beliebigen
Snap-Ids
– Einschränkung: Die Instanz darf zwischenzeitlich nicht neu
gestartet worden sein!
• Schwellenwerte für Report können gesetzt werden
• AWR in Version 10g automatisch konfiguriert
62
Statspack Informationen
•
•
•
•
•
•
•
•
Load Profile
Ausnutzung der Instanz (Trefferraten, etc.)
Wait Events
SQL
Instance Activity
Tablespace IO
File IO
…
63
AWR (Beispiel)
64
Parameter für PGA-Größe (alt)
• bitmap_merge_area_size = 1048576
– Bestimmt die Größe bei Bitmap-Merge
• hash_area_size = 131072
– Bestimmt die Größe für Hash-Joins
Default: 2 * sort_area_size
• sort_area_size = 65536
– Bestimmt die Memory-Größe für allgemeine Sortierungen
• sort_area_retained_size = 65536
– Memorybedarf nach einer Sortierung
– Default: sort_area_size
Vorsicht:
Vorsicht:Jeder
JederProzess
Prozesskann
kanndiese
dieseRessourcen
Ressourcenbelegen!
belegen!
65
Parameter für PGA-Größe (Neu)
• workarea_size_policy = MANUAL
– Bestimmt, dass ein gestimmter Workspace zur Verfügung gestellt
wird
– Default abhängig von pga_aggreate_target, wenn dieser nicht
gesetzt, dann MANUAL, sonst AUTO
• pga_aggregate_target = 0
– Gibt die Gesamtgröße der zur Verfügung stehenden PGA an
– Alle Prozesse gemeinsam können diesen Speicherplatz
ausnutzen
– Je mehr konkurrierende Prozesse, umso kleiner die PGA des
Einzelnen
Vorsicht:
Vorsicht:
Andere
AnderePGA-Parameter
PGA-Parameterhaben
habenkeine
keineWirkung
Wirkungmehr!
mehr!
66
Parameter für Optimizer
• optimizer_mode = CHOOSE
– Legt den Standardoptimizer für die Datenbank fest
• optimizer_features_enable = 9.2.0
– Gibt an, welche Optimizerspezifischen Funktionen genutzt werden
können
• optimizer_index_cost_adj = 100
– Legt die prozentualen Kosten für einen Indexzugriff fest.
Ein Wert von 50 gibt an, dass ein Indexzugriff nur halb so teuer
wie normalerweise ist.
• optimizer_max_permutations = 2000
– Maximale Anzahl Permutationen, die für einen Join in Betracht
gezogen werden
– Wenn optimizer_features_enabled < 9.0 => 80000
67
Sonstige Parameter
• query_rewrite_enabled = FALSE
– Grundvoraussetzung für neue Funktionalitäten wie z.B. Query
Rewrite aber auch die Verwendung von Function-Based Indizes
• statistics_level = TYPICAL
– Gibt an, welche Statistiken für die Datenbank gesammelt werden
• timed_statistics = TRUE
– Es werden Statistiken mit Zeitangaben gesammelt
– Default abhängig von statistics_level:
• TRUE, wenn statistics_level = TYPICAL oder ALL,
sonst FALSE
• timed_os_statistics = 5
– Zeitintervall in Sekunden, nach denen Betriebssystemstatistiken
gesammelt werden
– Default abhängig von statistics_level:
• 5, wenn statistics_level = ALL, sonst 0
68
Parameter für Cursor-Sharing
• CURSOR_SHARING = EXACT
– Nur identische Statements werden "geshared"
• CURSOR_SHARING = FORCE
– Das System wandelt Literale in Bindevariablen um
– Bessere Ausnutzung des Sharedpools
– Vorsicht: kann in Oracle8i zu falschen Ergebnissen führen!
• CURSOR_SHARING = SIMILAR
– Wie FORCE, aber nur dann, wenn kein anderer Zugriffspfad
gewählt werden würde (z.B. durch Histogramme)
69
Dynamischer Shared und Large Pool
• Shared und Large Pools können dynamisch
vergrößert und verkleinert werden:
ALTER
ALTER
ALTER
ALTER
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SET
SET
SET
SET
SHARED_POOL_SIZE
SHARED_POOL_SIZE == 64M;
64M;
LARGE_POOL_SIZE
LARGE_POOL_SIZE == 64M;
64M;
• Limitierungen:
– Die Größe ist ein Vielfaches der Granularität der Objekte
– Die gesamte SGA kann nicht größer werder als SGA_MAX_SIZE
70
Dynamischer Buffer Cache
• Der Buffer Cache kann dynamisch vergrößert oder
verkleinert werden
ALTER
ALTER SYSTEM
SYSTEM SET
SET DB_CACHE_SIZE
DB_CACHE_SIZE == 96M;
96M;
• Limitierungen:
– Vielfaches der entsprechenden db_block_size
– Die gesamte SGA kann nicht größer werder als SGA_MAX_SIZE
– DB_CACHE_SIZE kann nicht auf 0 gesetzt werden
71
Dynamische SGA Beispiel
Initialisierungsparameter
Initialisierungsparameter(init.ora):
(init.ora):
SGA_MAX_SIZE
== 128M
SGA_MAX_SIZE
128M
DB_CACHE_SIZE
== 96M
DB_CACHE_SIZE
96M
SHARED_POOL_SIZE
SHARED_POOL_SIZE == 32M
32M
SQL>
SQL>
=>
=>
ALTER
ALTER SYSTEM
SYSTEM SET
SET SHARED_POOL_SIZE
SHARED_POOL_SIZE == 64M;
64M;
insufficient
insufficientmemory
memory
SQL>
SQL>
SQL>
SQL>
=>
=>
=>
=>
ALTER
ALTER SYSTEM
SYSTEM SET
SET DB_CACHE_SIZE
DB_CACHE_SIZE == 64M;
64M;
ALTER
ALTER SYSTEM
SYSTEM SET
SET SHARED_POOL_SIZE
SHARED_POOL_SIZE == 64M;
64M;
insufficient
insufficientmemory
memoryerror
errormessage
message
check
checkV$BUFFER_POOL,
V$BUFFER_POOL,shrink
shrinkmuss
mussdurchgeführt
durchgeführtwerden
werden
SQL>
SQL>
=>
=>
ALTER
ALTER SYSTEM
SYSTEM SET
SET SHARED_POOL_SIZE
SHARED_POOL_SIZE == 64M;
64M;
Statement
Statementprocessed.
processed.
72
Empfehlungen für Caches
•
•
•
•
•
V$DB_CACHE_ADVICE
V$SHARED_POOL_ADVICE
V$PGA_TARGET_ADVICE
V$MTTR_TARGET_ADVICE
Beschreibung und Status in V$STATISTICS_LEVEL
73
Beispiel init.ora (1)
db_name
db_name
service_names
service_names
db_block_size
db_block_size
control_files
control_files
== JA102
JA102
== JA102
JA102
== 8192
8192
== ("/oradata1/JA102/control01.ctl",
("/oradata1/JA102/control01.ctl",
"/oradata2/JA102/control02.ctl",
"/oradata2/JA102/control02.ctl",
"/oradata1/JA102/control03.ctl")
"/oradata1/JA102/control03.ctl")
background_dump_dest
== /app/oracle/admin/JA102/bdump
background_dump_dest
/app/oracle/admin/JA102/bdump
user_dump_dest
== /app/oracle/admin/JA102/udump
user_dump_dest
/app/oracle/admin/JA102/udump
core_dump_dest
== /app/oracle/admin/JA102/cdump
core_dump_dest
/app/oracle/admin/JA102/cdump
log_archive_start
== true
log_archive_start
true
log_archive_dest
== /oradata3/JA102/archive/
log_archive_dest
/oradata3/JA102/archive/
log_archive_format
== arch_%s.arc
log_archive_format
arch_%s.arc
remote_login_passwordfile
remote_login_passwordfile == EXCLUSIVE
EXCLUSIVE
compatible
== 10.2.0.0.0
compatible
10.2.0.0.0
undo_management
=
AUTO
undo_management
= AUTO
undo_retention
== 10800
undo_retention
10800
undo_tablespace
== UNDOTBS1
undo_tablespace
UNDOTBS1
74
Beispiel init.ora (2)
db_cache_size
== 52428800
db_cache_size
52428800
db_file_multiblock_read_count
db_file_multiblock_read_count == 88
java_pool_size
== 00
java_pool_size
large_pool_size
== 8388608
large_pool_size
8388608
shared_pool_size
== 50331648
shared_pool_size
50331648
open_cursors
== 300
open_cursors
300
timed_statistics
== true
timed_statistics
true
job_queue_processes
== 22
job_queue_processes
query_rewrite_enabled
query_rewrite_enabled == true
true
processes
== 200
processes
200
fast_start_mttr_target
fast_start_mttr_target == 300
300
log_buffer
== 1048576
log_buffer
1048576
log_checkpoint_timeout
log_checkpoint_timeout == 00
pga_aggregate_target
== 104857600
pga_aggregate_target
104857600
aq_tm_processes
=
1
aq_tm_processes
= 1
75
Vorausschauende Programmierung
• Markieren von Anwendungsteilen mit
dbms_application_info
76
Anwendungstracing
• In der Anwendung:
dbms_application_info.set_module(
dbms_application_info.set_module(
module_name
module_name =>
=> 'Verkaufsbericht
'Verkaufsbericht (heute)'‚
(heute)'‚
action_name
action_name =>
=> 'Initialisierung')
'Initialisierung')
• Für einen speziellen Client:
dbms_session.set_identifier
dbms_session.set_identifier (client_id
(client_id =>
=> 'Lenz')
'Lenz')
• Tracing aktivieren:
dbms_monitor.serv_mod_act_trace_enable(
dbms_monitor.serv_mod_act_trace_enable(
service_name
service_name =>
=> 'SUN10G'‚
'SUN10G'‚
module_name
module_name =>
=> 'Verkaufsbericht
'Verkaufsbericht (heute)'‚
(heute)'‚
action_name
action_name =>
=> 'Initialisierung')
'Initialisierung')
dbms_monitor.client_id_trace_enable('Lenz');
dbms_monitor.client_id_trace_enable('Lenz');
77
Noch Fragen?
Johannes Ahrends
[email protected]
www.quest.com
Copyright © 2006 Quest Software