PTA_DB_OracleDBTuning_Schulung

Beratung
Organisation
Softwareentwicklung
Tuning
Tuningvon
vonOracle-Datenbanken
Oracle-Datenbanken
(Version
(Version8.1.7)
8.1.7)
seit 1969
Tuning von Oracle-Datenbanken
Gliederung
Gliederung
1.
Einleitung
2.
Performance: Abläufe und Messungen
2.1 Optimizer
2.2 Statistiken
2.3 SQL Trace
3.
Zugriffsoptimierung
3.1 Datenbankdesign (Parallelisierung, Anzahl Hintergrundprozesse usw.)
3.2 Speicherstrukturen (Extents, Cluster, Indizes usw.)
3.3 Anwendungsbezogene Optimierung
4.
Datenbankoptimierung
4.1 Optimierung externer Ressourcen (I/O, Hauptspeicher, Prozessoren usw.)
4.2 Optimierung interner Ressourcen (SGA, Latches usw.)
5.
Quellen
12.02.2007
PTA GmbH, Unternehmensberatung
2
Tuning von Oracle-Datenbanken
Aufgabenspektrum
Aufgabenspektrum der
der Datenbankadministration
Datenbankadministration
•
•
•
•
•
•
•
•
Datenbankverfügbarkeit verwalten
Datenbanken planen und erstellen (Zugriffsoptimierung)
Physikalische Strukturen verwalten
Speicherplatz entsprechend Design verwalten
Sicherheit verwalten
Netzwerkadministration
Backup und Recovery
Datenbankoptimierung
Quelle: Oracle Schulung „K3308 Oracle8i Datenbankadministration“
12.02.2007
PTA GmbH, Unternehmensberatung
3
Tuning von Oracle-Datenbanken
Ziele
Zieledes
desDB-Tunings
DB-Tunings
•
Online Transaction Processing (OLTP) Anwendungen
– Performance über den Durchsatz (throughput) definiert
– Pro Tag tausende oder sogar hunderttausende von kleinen Transaktionen.
•
Decision Support Systeme (DSS Anwendungen)
– Performance (unter anderem) über die Antwortzeit (response time) definiert.
– Abfragen, lesen und sortieren tausende von Datensätzen aus verschiedenen
Tabellen.
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
4
Tuning von Oracle-Datenbanken
Tuningziele
Tuningziele(gemäß
(gemäßOracle)
Oracle)
•
Klare Ziele fürs Tuning setzen:
– Man kann keinen Erfolg haben, wenn man „Erfolg“ nicht definiert hat.
•
“Machen Sie die Anwendung so schnell wie möglich."
– Sehr schwierig herauszufinden, wann dieses Ziel erreicht wurde
– Sind die Ergebnisse den Anforderungen der Anwendung gerecht?
•
Besseres Beispiel: "Für 90% aller Transaktionen eine Antwortzeit von
weniger als drei Sekunden"
•
Behalten Sie die Ziele im Kopf während jede einzelne Tuningmaßnahme
durchdacht wird.
•
Behalten Sie auch im Kopf, daß Ziele kollidieren können
– Damit die beste Performance für einen SQL Befehl erreicht wird, nimmt ev. die
Performance anderer SQL Befehle ab.
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
5
Tuning von Oracle-Datenbanken
Vorgehensweise
VorgehensweiseTuningmaßnahmen
Tuningmaßnahmen
•
Business Rules
– Allgemeine Anforderung, Eigenschaften der Datenbank
•
•
Datendesign
- Normalisierung der Daten
- Indizes auf Primary- und Foreign-Keys
- Zugriffskonflikte:
-
•
Wie groß ist die Datenmenge?
z.B. die DB hat eine Größe von 1TB, auf nur auf 0,5% der Daten wird
zugegriffen => "hot spot"
Anwendung
- Effiziente Kommunikation mit der Datenbank: Netzwerkverkehr
12.02.2007
PTA GmbH, Unternehmensberatung
6
Tuning von Oracle-Datenbanken
Vorgehensweise
VorgehensweiseTuningmaßnahmen
Tuningmaßnahmen
•
Logische Datenbankstruktur
– Nutzung von Views
– Tabellen u. Indizes in verschiedenen Tablespaces?
•
Datenbankoperationen
– Wann Stored Procedures, Backups laufen
– Wie viele Ressourcen sie brauchen
•
Datenzugriff
– Indizes, Optimizer Hints
•
Hauptspeicher (Memory)
– Größe der verschiedenen Cache-Bereiche
– Blockgröße: db_block_size
•
I/O, physische Struktur
– Effizientes Setzen der Extentgröße
12.02.2007
PTA GmbH, Unternehmensberatung
7
Tuning von Oracle-Datenbanken
Vorgehensweise
VorgehensweiseTuningmaßnahmen
Tuningmaßnahmen
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
8
Tuning von Oracle-Datenbanken
Wann
WannTuning
Tuningdurchgeführt
durchgeführtwerden
werdensollte
sollte
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
9
Tuning von Oracle-Datenbanken
Gliederung
Gliederung
1.
Einleitung
2.
Performance: Abläufe und Messungen
2.1 Optimizer
2.2 Statistiken
2.3 SQL Trace
3.
Zugriffsoptimierung
3.1 Datenbankdesign (Parallelisierung, Anzahl Hintergrundprozesse usw.)
3.2 Speicherstrukturen (Extents, Cluster, Indizes usw.)
3.3 Anwendungsbezogene Optimierung
4.
Datenbankoptimierung
4.1 Optimierung externer Ressourcen (I/O, Hauptspeicher, Prozessoren usw.)
4.2 Optimierung interner Ressourcen (SGA, Latches usw.)
5.
Quellen
12.02.2007
PTA GmbH, Unternehmensberatung
10
Tuning von Oracle-Datenbanken
Welche
WelcheBereiche
Bereichesind
sindrelevant?
relevant?
•
Online Transaction Processing (OLTP)
–
–
–
–
–
–
•
SQL Statements
Transaktionen
Bindevariablen
Rollbacksegmente
Größe der Shared Pool, Datenblöcke, Database Buffer Cache
Packages und Stored Procedures
Decision Support Systems (DSS)
–
–
–
–
–
SQL Statements
Indizes
Abfragen-Hints
Der Optimizer
Sort-Operationen
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
11
Tuning von Oracle-Datenbanken
Phasen
Phaseneiner
einer SQL-Anweisung
SQL-Anweisung
Instanz
SGA
Shared Pool
Innerhalb des Shared Pool des System
Global Area (SGA):
Im Library Cache werden gespeichert:
-
Datenbank
12.02.2007
Der geparste Code
Die Ausführungspläne.
Der Data Dictionary Cache enthält die
zuletzt verwendeten Informationen über
DB-Objekte und Berechtigungen.
PTA GmbH, Unternehmensberatung
12
Tuning von Oracle-Datenbanken
Phasen
Phaseneiner
einer SQL-Anweisung
SQL-Anweisung
1. Parse
- Der Serverprozess sucht im Library Cache nach identischen Anweisungen
(Groß/Kleinschreibung wird beachtet).
- Überprüft Syntax des Statement,
- Überprüft Objektnamen und Privilegen des Benutzers
- Erzeugt und speichert den Ausführungsplan.
- Für das Statement wird ein Abschnitt (Cursor) im Library Cache vergeben.
2. Bind
- Die Anweisung wird nach Bindevariablen durchsucht
- Die Werte werden zugewiesen
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
13
Tuning von Oracle-Datenbanken
Phasen
Phaseneiner
einer SQL-Anweisung
SQL-Anweisung
3. Ausführung (Execute):
-
Identifizierung der ausgewählten Zeilen anhand des Ausführungsplans
Änderungen an den Datenblöcken bei DML
4. Abrufen (Fetch):
-
Bei Abfragen werden die Zeilen abgerufen und an den Serverprozess
zurückgegeben
Sortierungen (wenn erforderlich) werden durchgeführt
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
14
Tuning von Oracle-Datenbanken
Der
Der Optimizer
Optimizer
•
Der Optimizer stellt die effizienteste Weise fest, auf die eine SQL
Anweisung ausgeführt werden kann.
•
Dies ist ein wichtiger Schritt bei allen DML Anweisungen. SELECT,
INSERT, UPDATE oder DELETE.)
•
Verfahren (Instanz (siehe init.ora), pro Sitzung oder pro Statement):
a) Regelbasiert (rule based):
• eine Entscheidung wird auf der Basis fester Regeln getroffen
b) Kosten- bzw. Statistikbasiert (cost based, ab Oracle 7)
• das Anlegen von Statistiken zu den Tabellen und Indizes über den
Befehl ANALYZE ist unbedingt erforderlich.
12.02.2007
PTA GmbH, Unternehmensberatung
15
Tuning von Oracle-Datenbanken
Der
Der regelbasierte
regelbasierteOptimizer
Optimizer
•
Die Entscheidung über den besten Ausführungsplan wird nach festen
Regeln anhand Informationen aus dem Data Dictionary (indizierte Spalten,
Clustersegmente) getroffen.
– Bewertungsschema
• Rang 1: Einzelne Zeile über ROWID
• ...
• Rang 9: Einspaltiger Index
• ....
• Rang 15: Full Table Scan
– Wenn vorhanden, werden Indizes auf jeden Fall verwendet.
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
16
Tuning von Oracle-Datenbanken
Der
Der kostenbasierte
kostenbasierteOptimizer
Optimizer
•
Verfahren
–
–
–
–
•
Der Optimizer generiert potentielle Zugriffspläne, basierend auf den verfügbaren
Zugriffspfaden und Hinweisen (Hints) im SQL-Statement.
Mittels der im Data Dictionary vorhandenen Statistiken über die Datenverteilung
und Speicherattribute der Tabellen und Indizes, auf die zugegriffen wird, schätzt
der Optimizer die Kosten jedes Plans.
Die Kosten: Ein geschätzter Wert, proportional zum erwarteten
Ressourceverbrauch. Ressourcen sind u.a. der I/O und Speicher, die bei der
Ausführung des Statements gebraucht werden.
Der Optimizer wählt den Plan mit den geringsten Kosten aus.
Mit der wachsenden Leistungsfähigkeit des statistikorientierten Optimizers
wird der regelbasierte Ansatz - vor allem für komplexe Zugriffe - immer mehr
in den Hintergrund gerückt.
12.02.2007
PTA GmbH, Unternehmensberatung
17
Tuning von Oracle-Datenbanken
Der
Der Optimizer:
Optimizer: Hints
Hints
•
Hints: Steuerung des Optimizers auf der Ebene einzelner SQL-Befehle.
•
Hints können so unterschiedliche Dinge regeln wie:
– Das Optimierungsziel des Optimizers
– Den Zugriffspfad für eine Tabelle
– Die Reihenfolge beim Joins von Tabellen
•
Beispiele (siehe auch später):
select /*+ ALL_ROWS */ * from TAB_KUNDE;
select /*+ FIRST_ROWS */ * from TAB_KUNDE;
•
Nachteil: Hints sind hart codiert, also statisch.
12.02.2007
PTA GmbH, Unternehmensberatung
18
Tuning von Oracle-Datenbanken
Der
Der Befehl
Befehl EXPLAIN
EXPLAIN PLAN
PLAN
•
In der Parse-Phase wird für jedes abgesetzte SQL-Anweisung ein
Ausführungsplan erzeugt.
•
Der Ausführungsplan kann mit dem Statement EXPLAIN PLAN untersucht
werden.
SQL> explain plan for
2 SELECT * FROM emp, dept
3 WHERE emp.deptno = dept.deptno;
Explained.
•
Voraussetzung ist die Existenz der plan_table, in der die Pläne
gespeichert werden.
– Sie wird mit dem Skript %oracle_home%\rdbms\admin\utlxplan.sql
erstellt.
12.02.2007
PTA GmbH, Unternehmensberatung
19
Tuning von Oracle-Datenbanken
Der
Der Befehl
Befehl EXPLAIN
EXPLAIN PLAN
PLAN
•
DBA Studio (8i Client): Instance Manager
12.02.2007
PTA GmbH, Unternehmensberatung
20
Tuning von Oracle-Datenbanken
Der
Der Befehl
Befehl EXPLAIN
EXPLAINPLAN
PLAN
•
Bei der Auswertung muss die plan_table direkt abgefragt werden:
– Die Zugriffspläne aller analysierten Befehle werden gemeinsam
abgelegt
– Es kann ein Statement-ID mitgegeben werden.
SQL> select operation from plan_table;
OPERATION
-----------------------------SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS
TABLE ACCESS
INDEX
12.02.2007
PTA GmbH, Unternehmensberatung
21
Tuning von Oracle-Datenbanken
Der
Der Befehl
Befehl AUTOTRACE
AUTOTRACE
•
Der Ausführungsplan kann auch direkt (einfacher) in der SQL*Plus Sitzung
ausgewertet werden:
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY] | [EXP[LAIN]] |
[STAT[ISTICS]]}
12.02.2007
PTA GmbH, Unternehmensberatung
22
Tuning von Oracle-Datenbanken
Der
Der Befehl
Befehl AUTOTRACE
AUTOTRACEund
undEXPLAIN
EXPLAINPLAN
PLAN
Bemerkungen zu dem Beispiel:
•
set autotrace trace explain statistics schaltet die Ausgabe
der Spalten aus und zeigt den Ausführungsplan an.
– Für die Statistik sind Zugriffsrechte auf die Dynamic Performance Views
und plan_table notwendig.
•
Die Tabelle emp hat eine (indizierte) Primary Key.
•
Die Ausführung fängt mit dem Full Table Scan von Tabelle emp an:
– Für jede Zeile in emp wird über den PK-Index die entsprechende Zeile
über die ROWID (siehe später) aus dept gelesen.
•
In diesem Beispiel handelt es sich um eine regelbasierte Optimierung. Es
sind keine Tabellenstatistiken vorhanden.
12.02.2007
PTA GmbH, Unternehmensberatung
23
Tuning von Oracle-Datenbanken
Ausführungsplan
Ausführungsplaninterpretieren
interpretieren
•
Jeder Schritt des Ausführungsplan nimmt entweder Zeilen aus der
Datenbank ab oder übernimmt Zeilen von anderen Schritten.
8
7
3
1
Auf dem gleichen Niveau.
Die Schritte sind nach ihrer
Ausführung nummeriert.
2
6
5
Joins
4
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
24
Tuning von Oracle-Datenbanken
Messung
Messungder
der Performance
Performancemit
mit SQL
SQLTrace
Trace
•
Über SQL Trace können weitere Details zu der Performance SQL-Befehle
herausgefunden werden. SQL Trace hat folgende Eigenschaften:
– SQL Trace kann für eine Instanz oder eine Sitzung eingeschaltet
werden.
– Verschiedene Ausführungspläne der gleichen Statement lassen sich
damit vergleichen.
– Für die Phasen Parse, Execute und Fetch werden Zugriffs- und auch
Zeitstatistiken zurückgegeben, z.B.:
• CPU Zeit
• Leseoperationen von der Platte
• Nicht-Treffer im Library Cache
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
25
Tuning von Oracle-Datenbanken
Utility
UtilitySQL
SQLTrace
Trace-- Aktivierung
Aktivierung
•
Ein Zugriff auf die Tabelle plan_table (utlxplan.sql) und die Rolle
plustrace (plustrce.sql) sind erforderlich.
•
Einträge in initSIDora:
timed_statistics = true
user_dump_dest = (Zielort für Tracedateien)
•
Trace aktivieren
– für gesamtes System (initSID.ora): sql_trace = true
– Pro Sitzung: ALTER SESSION SET sql_trace = true;
– Für eine andere Session:
Procedure DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
•
Pro Session wird eine Trace-Datei im user_dump_dest geschrieben, die
nach dem Betriebssystemprozess (Windows Thread) benannt ist.
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
26
Tuning von Oracle-Datenbanken
Utility
UtilitySQL
SQLTrace
Trace-- Auswertung
Auswertungmit
mitTKPROF
TKPROF
•
TKPROF wertet die Datei, die mit SQL Trace erstellt wurde, aus und erzeugt
eine formatierte Ausgabedatei.
System> TKPROF Ora00210.trc
abst_emp.txt
sys=no
explain=abst/abst@abst
table=abst.plan_table
•
Zahlreiche SORT-Optionen
12.02.2007
•
•
•
•
•
PTA GmbH, Unternehmensberatung
Input-Datei
Ausgabedatei
Die rekursive SQL wird
unterdrückt (Abfragen an sysTabellen).
Mit EXPLAIN PLAN wird für
jedes Statement ein
Ausführungsplan generiert:
Dies ist der Connect-String.
Temporäre Plan-Tabelle
27
Tuning von Oracle-Datenbanken
Utility
UtilitySQL
SQLTrace
Trace-- Ausgabe
Ausgabevon
vonTKPROF
TKPROF
SELECT * FROM dept, emp
WHERE emp.deptno = dept.deptno
call
count
------- -----Parse
1
Execute
1
Fetch
2
------- -----total
4
cpu
elapsed
disk
query
current
-------- ---------- ---------- ---------- ---------0.00
0.02
0
0
0
0.00
0.00
0
0
0
0.01
0.01
0
30
4
-------- ---------- ---------- ---------- ---------0.01
0.03
0
30
4
rows
---------0
0
14
---------14
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20 (ABST)
Rows
------14
15
14
28
12.02.2007
Row Source Operation
--------------------------------------------------NESTED LOOPS
TABLE ACCESS FULL EMP
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN (object id 3732)
PTA GmbH, Unternehmensberatung
28
Tuning von Oracle-Datenbanken
Statistik
Statistikmittels
mittelsANALYZE
ANALYZEgenerieren
generieren
•
Statistiken werden für Cluster, Tabellen und Indices mit dem ANALYZEKommando generiert.
•
Der kostenbaserte Optimizer nutzt diese Statistiken um abzuschätzen,
welches der Ausführungsplan mit dem niedrigsten I/O und Speicheraufwand
für einen bestimmten SQL Befehl ist.
•
Die Statistiken werden im Data Dictionary gehalten und werden mit diesen
Views abgefragt (z.B. user_tables).
•
Durch die Zusätze compute statistics und estimate statistics
wird eine genaue oder überschlägige Berechnung der Statistiken
gewährleistet.
•
Tabellen, deren Inhalt häufig geändert wird, sollten regelmäßig analysiert
werden.
Quelle: Oracle8i Tuning Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
29
Tuning von Oracle-Datenbanken
Statistik
Statistikmittels
mittelsANALYZE
ANALYZEgenerieren
generieren
SQL>
SQL>
SQL>
•
ANALYZE TABLE
ANALYZE table
for table for
ANALYZE table
emp ESTIMATE STATISTICS n%;
TAB_KUNDE compute statistics
all indexed columns for all indexes;
TAB_KUNDE compute statistics;
Ab Oracle 7.3 können Histogramme hinzugezogen werden, zum Beispiel,
wenn einige Werte sehr häufig vorkommen und andere sehr selten.
– Der Optimizer kann die Anzahl zu bearbeitenden Zeilen besser schätzen.
– Per Default wird von einer gleichmäßigen Datenverteilung ausgegangen.
•
Statistiken können auch über das Supplied Package SYS.DBMS_STATS
generiert werden (z.B. für ein ganzes DB-Schema)
12.02.2007
PTA GmbH, Unternehmensberatung
30
Tuning von Oracle-Datenbanken
Gliederung
Gliederung
1.
Einleitung
2.
Performance: Abläufe und Messungen
2.1 Optimizer
2.2 Statistiken
2.3 SQL Trace
3.
Zugriffsoptimierung
3.1 Datenbankdesign (Parallelisierung, Anzahl Hintergrundprozesse usw.)
3.2 Speicherstrukturen (Extents, Cluster, Indizes usw.)
3.3 Anwendungsbezogene Optimierung
4.
Datenbankoptimierung
4.1 Optimierung externer Ressourcen (I/O, Hauptspeicher, Prozessoren usw.)
4.2 Optimierung interner Ressourcen (SGA, Latches usw.)
5.
Quellen
12.02.2007
PTA GmbH, Unternehmensberatung
31
Tuning von Oracle-Datenbanken
Datenbankdesign
Datenbankdesign
•
Parallelisierung
– Tabellenzugriffe und Sortierungen können auch parallel durchgeführt
werden.
• Ein Konfigurieren von Parallel Query-Server Prozessen und ein Festlegen
des Grades der Parallelisierung sind erforderlich.
• Parallelisierung ist nur bei entsprechenden Hardwarevoraussetzungen
(mehrere CPU's, mehrere Platten und Plattencontroller,...) sinnvoll.
•
Anzahl Redo-Log-Gruppen
– Beim Wechsel der Redo-Log-Gruppe (Log Switch), wird gleichzeitig
auch ein Checkpoint angestoßen. DBWR (Database Writer) schreibt
geänderte Blöcke von Block Buffer in die Datendateien.
– Es besteht die Möglichkeit, dass beim nächsten Log Switch auf den
DBWR gewartet werden muss, wenn:
• Die Redo Log Members groß sind,
• Die Anzahl der Redo Log Gruppen ist klein,
• Keine anderen Parameter konfiguriert sind.
12.02.2007
PTA GmbH, Unternehmensberatung
32
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen
•
•
Bei der Erstellung Tabellen/ Indizes kann die Extent-Größe sowie der freie
Platz im Data Block festgelegt werden.
Diese Werte können sich auf die Performance einer Abfrage auswirken.
CREATE TABLE my_contacts
(
person_id
NUMBER NOT NULL,
first_name
VARCHAR2(10) NOT NULL,
last_name
VARCHAR2(10) NOT NULL,
address
VARCHAR2(200)
)
PCTFREE
5
PCTUSED
60
TABLESPACE abst
STORAGE(
INITIAL
1024
NEXT
1024
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 120);
12.02.2007
PTA GmbH, Unternehmensberatung
Kleine PCTFREE,
für Updates wenig Platz
Kleine Extents (1KB)
PCTINCREASE = 0
(Default 50 kommt von Oracle7,
max. 1023 Data Files)
33
Tuning von Oracle-Datenbanken
Datenblöcke
Datenblöcke-- PCTFREE
PCTFREE 55
•
5
•
5%
•
•
Der Parameter PCTFREE hat nur
mit der Befüllung der Datenblöcke
zu tun.
5% der Blockgröße bleiben für
zukünftige Updates zu bestehenden
Zeilen frei.
Der Block wird bis ca. 95% mit
Tabellendaten befüllt.
Default-Wert PCTFREE = 10%
Tabellendaten
Quelle: Oracle8i Concepts Release 8.1.5
12.02.2007
PTA GmbH, Unternehmensberatung
34
Tuning von Oracle-Datenbanken
Datenblöcke
Datenblöcke-- PCTUSED
PCTUSED 60
60
60
30%
60%
•
•
PCTUSED = 60%: Neue Zeilen
werden erst in diesen Block
gespeichert, wenn er unter 60%
befüllt ist.
In diesem Beispiel sind 30% frei
und daher ca. 70% belegt.
60%
Tabellendatenmenge
schrumpt wegen DELETEs
Quelle: Oracle8i Concepts Release 8.1.5
12.02.2007
PTA GmbH, Unternehmensberatung
35
Tuning von Oracle-Datenbanken
Datenbankblöcke
Datenbankblöcke--ROW
ROW PIECE
PIECE
Row Header hat
Informationen über:
•
•
•
eindeutige ROWID
Anzahl Spalten
Blocksplitting
Quelle: Oracle8i Concepts
Release 8.1.5
12.02.2007
PTA GmbH, Unternehmensberatung
36
Tuning von Oracle-Datenbanken
Datenbankblöcke
Datenbankblöcke--Block
BlockChaining
Chaining
•
•
In einem Block werden 1 bis n Zeilen einer Tabelle gespeichert.
Aus folgenden Gründen kann eine Zeile über mehr als ein Datenblock
gehen (Block Chaining):
-
Die Tabellenzeile ist größer als ein Datenblock (Spaltentyp wie z.B. LONG oder
VARRAY).
-
Nach einem Update kann die Tabellenzeile wegen Vergrößerung der
Dateninhalte nicht mehr in dem Datenblock gespeichert werden. Beispiel:
UPDATE my_contacts SET address =
'The Charles Dickens Inn, 145 Old Compton Street, London EC1 9QX, UK';
COMMIT;
12.02.2007
PTA GmbH, Unternehmensberatung
37
Tuning von Oracle-Datenbanken
Datenbankblöcke
Datenbankblöcke--Block
BlockChaining
Chaining
•
Chained Blocks bremsen den Lesezugriff über Indizes.
– Im Index ist die eindeutige Adresse (ROWID) der Zeile gespeichert.
– Das Lesen der ganzen Tabelle (full table scan) bleibt unbetroffen.
•
Die Anzahl an Chained Blocks wird bei ANALYZE TABLE im Data Dictionary
gespeichert.
SQL> ANALYZE TABLE abst.my_contacts COMPUTE STATISTICS;
-----------------------------------------------------------SQL> SELECT table_name, last_analyzed, chain_cnt
2 FROM user_tables
3 WHERE table_name = 'MY_CONTACTS';
TABLE_NAME
LAST_ANA CHAIN_CNT
------------------------------ -------- ---------MY_CONTACTS
04.01.02
17379
12.02.2007
PTA GmbH, Unternehmensberatung
38
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Tablespaces
Tablespacesund
undExtents
Extents
•
Extents werden zugewiesen, wenn das Segment:
– erzeugt wird (CREATE TABLE/INDEX, temporärer Segmente)
– wächst (INSERT oder UPDATE).
•
Extents werden freigegeben, wenn das Segment:
– gelöscht/ geleert wird (TRUNCATE TABLE, DROP TABLE/INDEX)
•
Empfehlungen:
– Segmente mit Extents einer Uniformgröße anlegen.
– Wenige größere statt viele kleine Extents anlegen.
– Tablespaces für verschiedene Zwecke und auf verschiedene Platten
anlegen: Ein paralleler Zugriff auf Indizes und Tabellen ist möglich.
– (Bei Datenbanken auf einer Platte oder auf RAID5 besteht gemäß
Oracle kein Performanceverlust, wenn Tabellen und Indizes getrennt
sind)
Quelle: Informationen aus Oracle MetaLink
12.02.2007
PTA GmbH, Unternehmensberatung
39
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Extents
Extentsund
undFragmentierung
Fragmentierung
•
Die Fragmentierung eines Tablespace entsteht durch:
– Das Löschen von Objekten mit kleinen Extents: Der freigegebene Platz reicht für
neue Objekt nicht und Lücken entstehen.
– Das rasche Wachsen eines Segments, das als Folge aus sehr vielen gestreuten
Extents besteht.
•
•
•
Tablespace-Fragmentierung kann zu gebremster Performance der
Abfragen durch zu starke I/O führen.
Die aktuell belegten Extents können mit Data Dictionary View
dba_segments/ dba_extents abgefragt werden.
Gute Erfahrung mit Tools anderer Hersteller, z.B. 'Toad' von Firma Quest,
'Hora' von der Firma KeepTool.
SQL> SELECT segment_name, owner, tablespace_name,
extents, min_extents, max_extents
FROM dba_segments;
12.02.2007
PTA GmbH, Unternehmensberatung
40
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Extents
Extents
12.02.2007
PTA GmbH, Unternehmensberatung
•
Tablespace Map
ist Teil des Oracle
Enterprise
Manager Tuning
Pack.
•
Die Belegung
sowie eventuelle
Fragmentierung
eines Tablespace
werden deutlich
angezeigt.
41
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Cluster
Cluster
•
Basis für Cluster sind Tabellen, die hauptsächlich auf Basis des
Clusterschlüssels abgefragt werden.
•
Zeilen werden gruppiert und physikalisch gemeinsam gespeichert.
•
Cluster können weniger Speicherplatz belegen und Abfrageperformanz
verbessern.
•
Zwei Arten: Index-Cluster und Hash-Cluster.
- Bei einem Index-Cluster wird ein indizierter Cluster-Key verwendet.
10 //
20 //
Accounting, New York
King, President ......
Research, Detroit
30//
40//
Index Cluster für dept und emp;
Gemeinsamer Schlüssel deptno
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
42
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Hash
HashCluster
Cluster
•
Der Hash Cluster verwendet eine Hash-Funktion, um den Speicherort einer
Zeile zu berechnen (und nach der Zeile zu suchen).
-
Für eine neue Zeile in einer Tabelle des Cluster wird deren Schlüsselspalte/n zur
Berechnung des Hash-Wertes benutzt.
Nach diesem Wert wird die Zeile gespeichert.
•
Hash Cluster sind für große Tabellen und Gleichheitsabfragen geeignet und
für Full Table Scans ungeeignet.
•
Index-Cluster sind für einen gleichmäßig verteilten Schlüsselwert und
kleinere Datenmenge geeignet.
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
43
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Partitionierung
Partitionierung
•
•
•
Sehr große Tabellen und Indizes (z.B. in Data Warehouse) können in
kleinere und handlichere Partitionen zerteilt werden.
SQL-Statements können statt der ganzen Tabelle nur bestimmte
Partitionen lesen und manipulieren.
Die Partitionen dürfen in verschiedene Tablespaces abgelegt werden
– die I/O Last kann ausgeglichen werden
•
Es wird entweder nach Range (z.B. Datum unten) oder nach einer HashFunktion partitioniert.
CREATE TABLE sales ( acct_no
NUMBER(5),
acct_name
CHAR(30),
amount_of_sale NUMBER(6),
week_no
INTEGER )
PARTITION BY RANGE ( week_no ) ...
(PARTITION sales1 VALUES LESS THAN ( 4 ) TABLESPACE ts0,
...
PARTITION sales13 VALUES LESS THAN ( 52 ) TABLESPACE ts12 );
Oracle8i Concepts
12.02.2007
PTA GmbH, Unternehmensberatung
44
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Indizes
Indizes
•
Indizes werden benutzt, entweder um Abfragen zu beschleunigen oder um
Eindeutigkeit zu prüfen.
•
Beim Anlegen eines Primary Key wird automatisch der Index erstellt.
– Der Index stellt sicher, dass alle Einträge einen eindeutigen Wert haben.
•
Foreign Keys werden nicht automatisch indiziert.
– Wenn die Spalten häufig in Join-Bedingungen benutzt werden, sollten sie
indiziert werden.
– Beim UPDATE oder DELETE in einer Parent-Tabelle wird auf der Child-Tabelle
eine Sperre gesetzt:
• Auf Tabellenebene, wenn kein FK-Index vorhanden
• Auf Zeilenebene, wenn ein Index besteht.
•
•
Indizes verlangsamen INSERT und UPDATE Statements, weil die neuen
Daten in die Indizes eingetragen werden müssen.
Bei Indexerstellung wird der Sortierungsbereich im PGA verwendet.
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
45
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Struktur
Struktur Baumindex
Baumindex
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
rowid ADAMS
rowid ALLEN
•
Der Root-Block (Wurzel) enthält die Adressen der Branch-Blöcke.
•
Die Branch-Blöcke (Zweige) enthalten die Leaf-Block Adressen.
•
In den Blättern werden die individuellen Einträge der indizierten Spalten zusammen
mit dem eindeutigen ROWID gespeichert.
•
Über den ROWID erfolgt der Zugriff auf dem Datensatz am allerschnellsten.
12.02.2007
PTA GmbH, Unternehmensberatung
46
Tuning von Oracle-Datenbanken
Indizes
Indizesfür
fürAuswertungen
Auswertungen
•
Viele Abfragen werden durch Indizes beschleunigt: Indizierte Spalten, über
die in der WHERE-Bedingung gesucht wird.
•
Über die regelbasierte Optimierung greift eine SQL-Anweisung auf eine
indizierte Tabellenspalte immer über deren Index zu.
•
Wenn nur Spalten zu lesen sind, die im Index vorkommen, wird die Tabelle
gar nicht gelesen.
•
Beispiel auf nächster Folie (**regelbasierte Optimierung**):
1. Ohne Index wird ein Full Table Scan durchgeführt.
2. Über den vorhandenen Index werden die Daten aus der Tabelle gelesen.
3. Der Wert für ename wird nur aus dem Index gelesen.
12.02.2007
PTA GmbH, Unternehmensberatung
47
Tuning von Oracle-Datenbanken
Indizes
Indizesfür
fürAuswertungen
Auswertungen
-SQL> set autotrace trace expl
SQL> select * from emp where job='MANAGER';
1
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
1
0
TABLE ACCESS (FULL) OF 'EMP'
-------------------------------------------------------------------------------------SQL> create index i_job_ename on emp(JOB, ENAME);
-------------------------------------------------------------------------------------SQL> select * from emp where job='MANAGER';
2
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2
1
INDEX (RANGE SCAN) OF 'I_JOB_ENAME' (NON-UNIQUE)
-------------------------------------------------------------------------------------SQL> select ename from emp where job='MANAGER';
3
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
1
0
INDEX (RANGE SCAN) OF 'I_JOB_ENAME' (NON-UNIQUE)
12.02.2007
PTA GmbH, Unternehmensberatung
48
Tuning von Oracle-Datenbanken
Indizes
Indizesund
undStatistiken
Statistiken
•
Die kostenbasierte Optimierung entscheidet sich für einen
Ausführungsplan anhand der mit Kommando ANALYZE erstellten
Tabellenstatistik.
–
–
–
•
Die Selektivität (Eindeutigkeit) der Werte ist jetzt bekannt und spielt eine große
Rolle.
Die Größe der Tabelle ist bekannt (Datenblöcke) und ob es sich lohnt, sowohl
die Index- als auch die Tabellenblöcke zu lesen.
In vielen Fällen verursacht ein Full Table Scan weniger Kosten.
Beispiel auf nächster Folie: **kostenbasierte Optimierung**
1. Der Optimizer weißt, dass die Tabelle emp nur 16 Zeilen hat. Ein Full Table
Scan kostet weniger als ein Zugriff über Index.
– Oracle empfiehlt, Indizes auf einer Tabelle mit Größe erst ab 30-50 Blöcke
anzulegen.
2. Mit dem Optimizer Hint /*+ INDEX (<table> <index>) */ wird ein
Indexzugriff erzwungen.
12.02.2007
PTA GmbH, Unternehmensberatung
49
Tuning von Oracle-Datenbanken
Indizes
Indizesund
undSelektivität
Selektivität
SQL> analyze table emp compute statistics;
Table analyzed.
-----------------------------------------------------------------------------------SQL> select * from emp
2 where ename='SMITH'
3 /
1
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=64)
1
0
TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=2 Bytes=64)
-----------------------------------------------------------------------------------SQL> select /*+ INDEX (emp i_ename) */ * from emp
2 where ename='SMITH'
3 /
2
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=64)
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=2 Bytes=64)
2
1
INDEX (RANGE SCAN) OF 'I_ENAME' (NON-UNIQUE) (Cost=1 Card=2)
12.02.2007
PTA GmbH, Unternehmensberatung
50
Tuning von Oracle-Datenbanken
Indizes
Indizesund
undSelektivität
Selektivität
•
Im Beispiel auf der nächsten Folie:
– Die Tabelle abs_ma_gs wurde analysiert => Statistiken vorhanden.
– Der Index 'X_ABS_MA_GS_3' indiziert eine Kombination von 3 Spalten,
von denen mdt_nr (Mandantnummer) die erste ist.
– Bei der regelbasierten Optimierung und mit dem INDEX Hint wird die
Tabellendaten über einen Index gelesen. (vermutlich X_ABS_MA_GS_3).
– Der Mandant PTA (mdt_nr=1) kommt schätzungsweise in 90% der
Zeilen vor.
– Bei einem erzwungene Zugriff über den vorhandenen Index (2) sind die
Kosten viel höher als bei einem Full Table Scan (1).
•
Indizes beschleunigen Abfragen, die einen kleinen Teil der Zeilen der
Tabelle zurückgeben. (5% bis ca. 15%)
12.02.2007
PTA GmbH, Unternehmensberatung
51
Tuning von Oracle-Datenbanken
Indizes
Indizesund
undSelektivität
Selektivität
SQL> analyze table abs_ma_gs compute statistics;
--------------------------------------------------------------------------------------------SQL> select * from abs_ma_gs
2
where mgs_mdt =1;
1
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=3869 Bytes=88987)
1
0
TABLE ACCESS (FULL) OF 'ABS_MA_GS' (Cost=14 Card=3869 Bytes=88987)
--------------------------------------------------------------------------------------------SQL> select /*+ INDEX (abs_ma_gs X_ABS_MA_GS_3) */ * from abs_ma_gs
2 where mgs_mdt =1;
2
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=442 Card=3869 Bytes=88987)
1
0
TABLE ACCESS (BY INDEX ROWID) OF 'ABS_MA_GS' (Cost=442 Card=3869 Bytes=88987)
2
1
INDEX (RANGE SCAN) OF 'X_ABS_MA_GS_3' (NON-UNIQUE) (Cost=26 Card=3869)
12.02.2007
PTA GmbH, Unternehmensberatung
52
Tuning von Oracle-Datenbanken
Indizes
Indizesund
undSelektivität
Selektivität
•
Indizes beschleunigen Abfragen, die einen kleinen Teil der Zeilen der
Tabelle zurückgeben. (5% bis ca. 15%)
– Die Eindeutigkeit kann im View user_indexes (Spalte
DISTINCT_KEYS) oder mit Histogrammen abgefragt werden.
– Je eindeutiger der Schlüsselwert, desto wahrscheinlicher der Zugriff
über Index.
Arbeit'
Oracle 7 ca. 15%
Oracle 8.0 ca. 8%
Oracle 8i ca 5%
Index gelesen
Full Table Scan,
ohne Index
15
12.02.2007
50
PTA GmbH, Unternehmensberatung
Datenvolumen
(gelesene % der Tabelle)
53
Tuning von Oracle-Datenbanken
Bitmap
BitmapIndizes
Indizes
•
Jedes Bitmap enthält für jede indizierte Spalte Information über einen
bestimmten Wert.
•
Jede Position speichert Information zu einer bestimmten Zeile.
•
Bitmap Indices bringen bessere Performance in folgenden Fällen:
– Wenn eine Tabelle sehr viele Zeilen enthält und die Kardinalität der
Schlüsselspalte niedrig (1%) ist, d.h. wenige Werte, die mehrfach
auftreten.
– wenn Abfragen logische Verknüpfungen (AND, OR, NOT) in der WHERE
Klausel verwenden.
•
Bitmap Indizes haben eine B*Baum Struktur, aber sparen viel Platz, weil
keine Werte sondern nur die Bits gespeichert werden.
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
54
Tuning von Oracle-Datenbanken
Bitmap
BitmapIndizes
Indizes--Beispiel
Beispiel
CUST #
MARITAL_ STATUS
REGION
GENDER
INCOME_ LEVEL
101
102
103
104
105
106
...
single
married
married
divorced
single
married
east
central
west
west
central
central
male
female
female
male
female
female
bracket_1
bracket_4
bracket_2
bracket_4
bracket_2
bracket_3
nur 3
eindeutige Werte
WHERE status ='married'
AND region='central'
OR region='west'
Quelle: Oracle8i Concepts Release 8.1.7
12.02.2007
PTA GmbH, Unternehmensberatung
55
Tuning von Oracle-Datenbanken
Funktionsbasierte
FunktionsbasierteIndizes
Indizes
•
Funktionen auf der Seite des Prädikats (WHERE-Bedingung) machen den
Index unbrauchbar.
SQL> SELECT * FROM emp WHERE upper(ename) = 'SMITH';
•
Das Problem kann gelöst werden:
– durch ein Umschreiben des Statements:
SQL> SELECT * FROM emp WHERE ename = upper('SMITH');
– Mit einem funktionsbasierten Index
•
Beim funktionsbasierten Index werden berechnete Ausdrücke abgelegt.
– Auch selbstgeschriebene Funktionen können verwendet werden.
•
Dieses bringt bessere Performance, weil eventuell komplizierte Ausdrücke
nicht für jede Zeile berechnet werden müssen.
Quelle: Oracle Schulung „K3308 8i Datenbankadministration“
12.02.2007
PTA GmbH, Unternehmensberatung
56
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Rollbacksegmente
Rollbacksegmente
•
In einem Rollback-Segment wird der alte Zustand (Before-Image) vor einer
INSERT, UPDATE, DELETE Operation gespeichert.
•
Lesekonsistenz: Grundsätzlich werden anderen Benutzern (Prozessen) die
alten Werte angezeigt, bis die offene Transaktion abgeschlossen wird.
•
Benutzer-Rollbacksegmente sollten aus Performancegründen in einem
eigenen Tablespace abgelegt werden.
– Rollbacksegmente können konfiguriert werden, um zu einem
vorgegebenen Wert (OPTIMAL) zu schrumpfen. Wenn sie schrumpfen,
wird der Platz für alle anderen Segmente freigegeben.
•
Das SYSTEM RBS wird nur für Änderungen an den Objekte im SYSTEM
Tablespace verwendet.
12.02.2007
PTA GmbH, Unternehmensberatung
57
Tuning von Oracle-Datenbanken
Speicherstrukturen
Speicherstrukturen--Rollbacksegmente
Rollbacksegmente
Die Extents eines Rollbacksegment werden zyklisch
beschrieben; wurde ein Extent gefüllt, so wird das nächste
inaktive, d.h. nicht mit Undo-Daten belegte Extent genutzt.
Wurde das letzte Extent beschrieben, so beginnt erneut das
Beschreiben des ersten nicht-aktiven Extents. Sind alle
vorhandenen Extents belegt, so wird ein neuer Extent
angelegt.
Quelle: Oracle8i Concepts Release 8.1.5
12.02.2007
PTA GmbH, Unternehmensberatung
58
Tuning von Oracle-Datenbanken
Rollbacksegmente
Rollbacksegmente--High
HighWater
Water Mark
Mark
•
Das High Water Mark ist die Grenze zwischen dem benutzten und dem
freien Platz in jedem Segment.
Beispiel: Eine Transaktion, die viele Daten bearbeitet, schiebt den High
Water Mark des RBS nach oben.
Quelle: Oracle8i Concepts Release 8.1.5
•
Nach Transaktionsabschluss schrumpft das Segment bis den (in Byte)
gesetzten OPTIMAL Wert. Die oberen Extents werden freigegeben.
12.02.2007
PTA GmbH, Unternehmensberatung
59
Tuning von Oracle-Datenbanken
Lesekonsistenz
Lesekonsistenzund
und"Snapshot
"Snapshot too
tooold"
old"
•
Im Beispiel auf der nächsten Folie handelt es sich um einen langen
Leseprozess (z.B. über einen Stored Procedure).
– Der Vorgang wird am Zeitpunkt (system change number) 10023
angefangen.
– Bis der Prozess den dritten Block liest, wurde dieser geändert (auch
COMMITed), deshalb hat er im Header eine spätere SCN (10024).
Um einen lesekonsistenten Zustand zu liefern, muss der Prozess den
alten Block (den bei 10023 gültigen Before-Image) aus dem
Rollbacksegment lesen.
– Wenn dieser Block nicht mehr vorhanden ist, weil er von einem neueren
Transaktion überschrieben würde, oder durch Schrumpfen des
Rollbacksegment freigegeben wurde, kommt der Oracle Fehler:
ORA-1555: snapshot too old
12.02.2007
PTA GmbH, Unternehmensberatung
60
Tuning von Oracle-Datenbanken
Lesekonsistenz
Lesekonsistenzund
und"Snapshot
"Snapshot too
tooold"
old"
BEGIN
Stored
Procedure
Zyklische Beschreibung des Rollbacksegments:
Der COMMIT der Transaktion bewirkt, dass das
Extent wieder freigegeben wird.
END
12.02.2007
(wenn keine anderen Transaktion dieses belegt)
PTA GmbH, Unternehmensberatung
61
Tuning von Oracle-Datenbanken
Anwendungsbezogene
AnwendungsbezogeneOptimierung
Optimierung
Optimierungsmöglichkeiten bei SQL-Anweisungen:
•
•
•
•
•
•
•
Index-Operationen (siehe vorherige Beispiele)
- Die Statistiken können die Abfrageperformance verbessern
Sortierungsoperation (ORDER BY; GROUP BY; DISTINCT)
-
Index anlegen, der die Daten bereits sortiert?
Join-Operationen
Optimizer Hints
- Sollten als letztes Mittel verwendet, z.B. wenn der Administrator mehr
über die Datenverteilung weiß als das System
Optimierung von Zugriffen über Views
Materialized Views
Verwendung von Bindevariablen statt hart codierte SQL-Anweisungen
12.02.2007
PTA GmbH, Unternehmensberatung
62
Tuning von Oracle-Datenbanken
Anwendungsbezogene
AnwendungsbezogeneOptimierung
Optimierung--Joins
Joins
•
•
•
•
Nested Loop-Joins
– Wird für Equi- und Non-Equi-Joins verwendet.
– Für jede Zeile in der äußeren Tabelle werden alle übereinstimmenden
Zeilen der inneren Tabelle abgerufen.
Sort/Merge-Joins
– Beide Zeilenquellen werden anhand der Werte/n in der Equi-JoinSpalte/n sortiert.
Hash-Joins
– siehe Beispiel
Cluster-Joins
– Erfolgt, wenn die zu verknüpfenden Tabellen Teil desselben Clusters
sind (ähneln Nested Loop Joins).
Der regelbasierte Optimizer kennt nur Nested Loop und Sort/Merge Joins.
Quelle: Oracle Schulung „K1110 SQL- und Zugriffsoptimierung“
12.02.2007
PTA GmbH, Unternehmensberatung
63
Tuning von Oracle-Datenbanken
Anwendungsbezogene
AnwendungsbezogeneOptimierung
Optimierung--Nested
NestedLoop
LoopJoin
Join
SQL> select * from emp, dept
2 where dept.deptno = emp.deptno;
Equi-Join
dept ist die führende Tabelle.
Execution Plan
Für jede Zeile in dept wird der Wert
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE
aus emp gelesen. Dabei wird der
1
0
NESTED LOOPS
ganze Index FK_DEPTNO durchsucht.
2
1
TABLE ACCESS (FULL) OF 'DEPT'
3
1
TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4
3
INDEX (RANGE SCAN) OF 'FK_DEPTNO' (NON-UNIQUE)
?
SQL> select * from dept, emp
2 where dept.deptno = emp.deptno;
Execution Plan
emp ist die führende Tabelle.
---------------------------------------------------------Für jede Zeile in emp wird über den
0
SELECT STATEMENT Optimizer=CHOOSE
1
0
NESTED LOOPS
Index der Wert aus dept gelesen.
2
1
TABLE ACCESS (FULL) OF 'EMP'
3
1
TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4
3
INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
12.02.2007
PTA GmbH, Unternehmensberatung
64
Tuning von Oracle-Datenbanken
Anwendungsbezogene
AnwendungsbezogeneOptimierung
Optimierung--Hash
HashJoin
Join
Der Hash-Join kann nur für Equi-Joins verwendet werden. Die Schritte im Beispiel:
1. Full Scan des virtuellen Views a und der Tabelle emp,beide werden in möglichst
viele Hash-Partitionen zerteilt.
2. Für jedes Paar Partition (einer aus jeder Tabelle) wird aus der kleineren eine HashTabelle erstellt. Mit der Größeren wird die Hash-Tabelle untersucht.
SQL> select * from emp, (select * from dept
2
order by deptno) s
3 where s.deptno = emp.deptno;
deptno in PK-Index schon sortiert!
Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=15 Bytes=930)
1
0
HASH JOIN (Cost=5 Card=15 Bytes=930)
2
1
TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=16 Bytes=512)
3
1
VIEW (Cost=2 Card=5 Bytes=150)
4
3
TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=5 Bytes=85)
5
4
INDEX (FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=5)
12.02.2007
PTA GmbH, Unternehmensberatung
65
Tuning von Oracle-Datenbanken
Gliederung
Gliederung
1.
Einleitung
2.
Performance: Abläufe und Messungen
2.1 Optimizer
2.2 Statistiken
2.3 SQL Trace
3.
Zugriffsoptimierung
3.1 Datenbankdesign (Parallelisierung, Anzahl Hintergrundprozesse usw.)
3.2 Speicherstrukturen (Extents, Cluster, Indizes usw.)
3.3 Anwendungsbezogene Optimierung
4.
Datenbankoptimierung
4.1 Optimierung externer Ressourcen (I/O, Hauptspeicher, Prozessoren usw.)
4.2 Optimierung interner Ressourcen (SGA, Latches usw.)
5.
Quellen
12.02.2007
PTA GmbH, Unternehmensberatung
66
Tuning von Oracle-Datenbanken
Optimierung
Optimierungexterner
externer Ressourcen
Ressourcen
Hardware und Betriebssystem:
• I/O
• Hauptspeicher
• Prozessoren
• Generelle Systemempfehlung
12.02.2007
PTA GmbH, Unternehmensberatung
67
Tuning von Oracle-Datenbanken
Optimierung
Optimierunginterner
interner Ressourcen
Ressourcen
SGA
•
Tuning Shared Pool (im schlimmsten Fall Instanzabsturz)
• Dictionary Cache (Row Cache)
• Library Cache (Shared SQL Area)
•
•
Database Buffer Cache.
Standardisierte Abfragen (Standard Packages bei 8i) die die Trefferquote
beim Zugriff auf die verschiedenen Caches wiedergeben.
Sort Area innerhalb des PGA:
•
•
•
•
Pro Sitzung zugewiesen und wächst nach Platzbedarf.
Wird aber erst bei Abmeldung wieder freigegeben.
Vermeidet Auslagern in temp- Segmente bei großen Sortierungen.
Größe bestimmt durch sort_area_size = n .
12.02.2007
PTA GmbH, Unternehmensberatung
68
Tuning von Oracle-Datenbanken
Optimierung
Optimierunginterner
interner Ressourcen
Ressourcen-- SGA
SGA
•
•
Anzahl der Treffer im Library Cache abfragen
Kann durch den Einsatz von Bindevariablen beeinflusst werden
select namespace, gets, gethits, gethitratio, pins, pinhits,
pinhitratio, reloads
from v$librarycache;
NAMESPACE
GETS
GETHITS
GETHITRATI PINS
PINHITS
PINHITRATI RELOADS
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------SQL AREA
18612785
18605992 .999635036 172232212 172205046 .999842271
13092
TABLE/PROCEDURE
135741
118379 .872094651
73944417
73918569 .99965044
66
BODY
8690
8685 .999424626
8690
8685 .999424626
0
TRIGGER
100
99
.99
100
98
.98
0
INDEX
31
5 .161290323
31
5 .161290323
0
CLUSTER
27
12 .444444444
15
5 .333333333
0
OBJECT
0
0
1
0
0
1
0
PIPE
0
0
1
0
0
1
0
12.02.2007
PTA GmbH, Unternehmensberatung
69
Tuning von Oracle-Datenbanken
Optimierung
Optimierunginterner
interner Ressourcen
Ressourcen-- SGA
SGA
•
Library Cache
Empfehlung: Reloads zu Treffern < 1%
select round(sum(reloads) / sum(pins) * 100,2) "% Reloads"
from v$librarycache;
•
Dictionary Cache
Empfehlung: Fehlschläge zu Treffern < 15%
select (round(sum(getmisses) /
decode(sum(gets), 0,1,sum(gets)),4)) "% getmisses"
from
v$rowcache;
12.02.2007
PTA GmbH, Unternehmensberatung
70
Tuning von Oracle-Datenbanken
Optimierung
Optimierunginterner
interner Ressourcen
Ressourcen-- SGA
SGA
•
•
Database Buffer Cache
Empfehlung: Trefferrate > 90%
SQL>
2
3
4
5
6
7
select round ((1 - (s1.value /
(s2.value + s3.value))) * 100,2) "Cache Hits %"
from v$sysstat s1, v$sysstat s2, v$sysstat s3
where s1.name = 'physical reads'
and
s2.name = 'db block gets'
and s3.name = 'consistent gets'
/
Cache Hits %
-----------86.34
12.02.2007
(gerade geöffnete DB)
PTA GmbH, Unternehmensberatung
71
Tuning von Oracle-Datenbanken
Optimierung
Optimierunginterner
interner Ressourcen
Ressourcen
•
•
•
Latches
Kopfbereich der Rollbacksegmente
Freelists
12.02.2007
PTA GmbH, Unternehmensberatung
72
Tuning von Oracle-Datenbanken
Gliederung
Gliederung
1.
Einleitung
2.
Performance: Abläufe und Messungen
2.1 Optimizer
2.2 Statistiken
2.3 SQL Trace
3.
Zugriffsoptimierung
3.1 Datenbankdesign (Parallelisierung, Anzahl Hintergrundprozesse usw.)
3.2 Speicherstrukturen (Extents, Cluster, Indizes usw.)
3.3 Anwendungsbezogene Optimierung
4.
Datenbankoptimierung
4.1 Optimierung externer Ressourcen (I/O, Hauptspeicher, Prozessoren usw.)
4.2 Optimierung interner Ressourcen (SGA, Latches usw.)
5.
Quellen
12.02.2007
PTA GmbH, Unternehmensberatung
73
Tuning von Oracle-Datenbanken
Quellen
Quellen
•
•
•
•
Oracledokumentation für Oracle Server 8i
Lehrmaterial zur Oracleschulung
„K3308 Oracle8i Datenbankadministration“
Lehrmaterial zur Oracleschulung
„K1110 Oracle SQL und Zugriffsoptimierung “
„Oracle 8 für den DBA - Verwalten, optimieren, vernetzen“
Uwe Herrmann, Dierk Lenz, Günter Unbescheid
Verlag Addison-Wesley
ISBN 3-8273-1310-0
12.02.2007
PTA GmbH, Unternehmensberatung
74