oracle tuning – teil 1 - ORBIT Gesellschaft für Applikations

ORACLE TUNING – TEIL 1
_ Einführung & Allgemeines
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
PERFORMANCE TUNING: OVERVIEW
_ Schematische Darstellung der wichtigsten Einflussfaktoren
auf die Performance
Sessions
SQL
Blockgröße
Memory
I/O
Locks
Performance
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
SEGMENTS, EXTENTS UND BLOCKS
_ Übersicht
Tablespace
Segments
Extents
Blocks
Default: 8KB
Max: 32KB
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
Rows
SEGMENTS, EXTENTS UND BLOCKS
_ Datenbankobjekte (Tabellen, Indizes, Partitionen) werden
in Segmenten gespeichert
_ Segmente bestehen aus mindestens 1 Extent (Initial
Extent)
_ Extent Allocation Auto (>64K/<64M) oder Uniform
Allocation
_ Uniform Allocation kann Fragmentierung reduzieren (BigTables)
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
PCTFREE UND PCTUSED
_
PCTFREE gibt den für Updates zu
reservierenden Speicherplatz in
einem Block an
_
Verfügbarer Speicherplatz: Blocksize
– Overhead – PCTFREE
_
PCTUSED gibt an, zu wieviel Prozent
der Block nur gefüllt sein darf,
um neue Rows aufnehmen zu dürfen
_
Row-Chaining: Row muss über
mehrere Blöcke verkettet abgelegt
werden
_
Row-Migration: Row wird nach
Update in neuen Block verschoben,
wenn aktueller Block (inkl. PCTFREE)
das Update nicht aufnehmen kann
_
PCTFREE = 20%
High-Update-Tables = 40-50%
empfohlen
Low-Update-Tables =
5-10% empfohlen
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
PCTUSED = 40%
100% – PCTFREE – 1 Row
wird empfohlen
AUTOMATIC SEGMENT SPACE MANAGEMENT (ASSM)
_
ASSM stellt eine wesentlich einfachere und effizientere Segmentverwaltung
bereit. Insbesondere sind ausgedehnte Freelists nicht mehr nötig.
_
Vorteile ergeben sich insbesondere bei Datensätzen stark variierender
Größe.
_
Nachteilig ist allerdings die längere Antwortzeit bei Full Table Scans sowie
bei häufigen, parallelen Inserts.
 ASSM sollte bei OLAP-Datenbanken nicht benutzt werden.
 ASSM sollte bei OLTP-Datenbanken nur dann benutzt werden, wenn die
Einfügefrequenz nicht zu hoch ist.
_
Die Parameter PCTFREE und PCTUSED werden nicht mehr benötigt und
werden gar ignoriert, falls sie gesetzt sind.
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
MULTIPLE FREELISTS
_
Oracle verwaltet freie Blöcke unterhalb der Highwater-Mark (HWM) in
Freelists
_
Multiple-Freelists als Option bei “Heavy-Concurrent-User-DML”
_
Session verwendet nur eine der Freelists
_
Nicht mit ASSM verwendbar
CREATE TABLESPACE mssm_ts
DATAFILE '/u01/oradata/mssm_ts_01.dbf' SIZE 500M
SEGMENT SPACE MANAGEMENT MANUAL;
CREATE TABLE test (
pid number not null primary key
) STORAGE (freelists 5)
TABLESPACE mssm_ts;
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
DB_BLOCK_SIZE
_
Der Parameter „db_block_size“ definiert die Größe der Datenblöcke.
_
db_block_size

Muss ein ganzzahliges Vielfaches der Festplattenblockgröße sein

kann laut Donald Burleson nachträglich beim Reorganisieren der Datenbank verändert
werden

darf zwischen 2048 und 32768 Byte liegen

Default-Wert: 8192 Byte

Größere Werte
» erhöhen bei OLTP-Datenbanken die Wahrscheinlichkeit von Sperrsituationen
» Bei OLAP-Datenbanken führen sie jedoch zu schnelleren Abfragen

Kleinere Werte
» führen bei OLAP-Datenbanken zu unnötig häufigen Festplattenzugriffen
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
DB_FILE_MULTIBLOCK_READ_COUNT
_ Gibt an, wie viele Datenblöcke bei Full Table Scans gleichzeitig
und asynchron von der Festplatte gelesen werden können.
_ Für OLAP-Datenbanken sollte er hoch gesetzt werden.
_ Bei OLTP-Datenbanken kann ein zu hoher Wert den Optimizer
zu unnötig häufigen Full Table Scans verleiten.
_ Unter UNIX liest ORACLE grundsätzlich 64 KByte von der
Festplatte. Daher kann es sinnvoll sein, wenn das Produkt von
db_block_size und db_file_multiblock_read_count den Wert
65536 hat.
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
DB_WRITER_PROCESSES
_
Durch den Initialisierungsparameter db_writer_processes kann die Anzahl
der parallel laufenden DBWR-Prozesse definiert werden.
_
Aktivitäten des DBWR:
 Der Database Writer (DBWR), schreibt Änderungen an den Datenblöcken
in die Datendateien auf der Festplatte.
 Die Übertragung geänderter Datenblöcke aus dem Database Buffer in die
Datendateien erledigt der Prozess DBWR nahezu kontinuierlich.
 DBWR Schreibt ASYNCHRON, wenn
»
»
»
»
Die Anzahl der Dirty Buffers zu hoch ist
Zu wenig Platz im Buffer-Cache ist
Das Zeitintervall abgelaufen ist (3 s)
Der Logwriter einen CHECKPOINT auslöst (z.B. wegen Logfile-Switch)
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
SQL HARD UND SOFT PARSES
_
Jedes DDL/DML-Statement wird nach syntaktischem und semantischem Check in
“geparster” Form nach Bestimmung eines Execution Plans in die SQL-Area geladen
_
Dieser teils mit erheblichen Kosten verbundene Vorgang wird als “Hard-Parse” bezeichnet
_
Vor dem Parsing wird über einen Hashvergleich bestimmt, ob sich das Statement bereits in
der SQL-Area befindet. Wenn ja, kann der Aufwand für das Parsing eingespart werden, es
wird dann das bereits vorhandene Statement für das Execute verwendet (“Soft Parse”)
_
Wiederkehrende Statements sollten daher mit Bind-Variablen ausgeführt, um den Anteil
der Soft Parses zu erhöhen mehrfache Hard Parses zu vermeiden
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
INDEXE (B-TREE)
_
Die Blattknoten (Leaf) beinhalten Einträge, welche aus den indizierten Werten und der
zugehörigen rowid (Nummer des Datensatzes in der Tabelle) aufgebaut sind
_
Über den Wurzelknoten (Root) und die Zweigknoten (Branch) lässt sich der benötigte
Blattknoten (Leaf) in logarithmischer Laufzeit auffinden (Unique Scan)
_
Um Bereichanfragen effizent zu beantworten sind Blattknoten untereinander verkettet; alle
anderen Werte im Bereich (Range Scan) lassen sich sequentiell finden
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
TABLE ACCESS BY INDEX (ROWID)

Zeilen werden über physikalische Adresse (ROWID) ermittelt und die Blöcke gelesen

Diesem schnellsten Zugriff auf eine Tabelle via ROWID geht immer ein Index-Zugriff voraus
»
»
»
»
Index
Index
Index
Index
Unique Scan = Auffindung von einer ROWID im Index
Range Scan = Auffindung von mehr als einer ROWID im Index
Full Scan = Abruf aller ROWIDs aus dem Index, kann sortiert werden
Fast Full Scan = Abruf aller ROWIDs über Multiblock I/O, nicht sortierbar
SELECT * FROM sh.customers WHERE cust_id = 1;
SELECT * FROM sh.customers WHERE cust_id > 1 AND
cust_id < 10;
SELECT cust_id FROM sh.customers ORDER BY cust_id;
SELECT cust_id FROM sh.customers WHERE cust_id > 1;
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
NESTED LOOP, SORT MERGE JOIN UND HASH JOIN

Verknüpfung von zwei Ergebnismengen (bei Plan über mehrere Tabellen bzw. Indizes)

Nested Loop = Für jeden Satz der 1. Menge wird die 2. Menge durchsucht
(kostengünstig bei kleinen Ergebnismengen und Cartesian Products)

Sort Merge Join = Bei gleicher Sortierung des Join-Kriteriums beider Mengen
(kostengünstig bei zwei gleich geordneten Ergebnismengen nach Join-Kriterium)

Hash Join = Caching der kleineren Ergebnismenge, Berechnung eines Hash
(kostengünstig, bei kleiner mit großer Ergebnismenge, kleine Menge wird gepuffert
und gehasht)
SELECT cu.cust_last_name
FROM oe.customers cu, oe.orders od
WHERE cu.customer_id = od.customer_id;
SELECT * FROM sh.customers cu, sh.countries co
WHERE co.country_id = cu.country_id;
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
SUBQUERYS [NOT ]IN UND EXISTS
 Eine IN-Subquery muss für jede Zeile der äußeren Query
ausgeführt werden (Nested)
 Eine EXISTS-Subquery muss nur einmal ausgeführt werden
(Ausnahme: korrelierte Subquery)
 Standard-Joins sind generell meistens kostengünstiger als
Subquerys und sollten wenn möglich daher immer bevorzugt
werden
SELECT c1.customer_id FROM oe.customers c1
WHERE c1.customer_id IN
(SELECT customer_id FROM oe.customers c2);
SELECT c1.customer_id FROM oe.customers c1
WHERE EXISTS
(SELECT customer_id FROM oe.customers c2);
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
QUERY HINTS

Über Query-Hints kann das Verhalten des CBO positiv wie negativ beeinflusst werden

Die manuelle Beeinflussung der Plan-Erstellung kann Kosten für das SQL-Parsing
sparen

Ordered-Hint = Erzwingt die Verarbeitung der From-Klausel in angegebener
Reihenfolge
SELECT * FROM oe.customers cu, oe.orders od WHERE cu.customer_id = od.customer_id;
SELECT /*+ ordered */ * FROM oe.customers cu, oe.orders od
WHERE cu.customer_id = od.customer_id;
Entscheidung des CBO auf Basis der Statistik war hier richtig, Orders als Driving-Table zu nehmen (Count= 105)
Query-Hint erzwingt Customers als Driving-Table, hier negative manuelle Einflußnahme (Count = 319)
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
QUERY HINTS
Hint
Beschreibung
ALL_ROWS
Verwendung des CBO, optimaler Throughput
CACHE(table)
In Kombination mit Full, Tabelle wird im Buffer gecached
FIRST_ROWS(n)
Verwendung des CBO, schnelle Rückgabe erster Zeilen
FULL(table)
Veranlasst den CBO, für die Tabelle einen FTS
durchzuführen
INDEX(table index)
Zwingt den CBO, den angegebenen Index zu verwenden
INDEX_FFS(table index)
Zwingt den CBO, den Index mit Fast Full Scan zu
verwenden
NO_INDEX(table index)
Zwingt den CBO, den angegebenen Index nicht zu
verwenden
Eine vollständige Auflistung aller Hints ist verfügbar unter:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50301
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
VORGEHENSWEISE BEIM TUNING
_
Schritte:

Tunen von Oben nach Unten (Top-Down)
» Anwendung vor SQL
» SQL vor Instanz

Stets dort tunen, wo die größte Einsparung liegt
» Wo sind die längsten Wartezeiten?
» Wo sind die längsten Antwortzeiten?

_
Wenn Ziel erreicht ist: Aufhören!!
Hilfsmittel:

Statspack
 Kostenfrei

AWR/ADDM
 muss gesondert lizensiert werden
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
SQL TUNING TIPPS

Function-based-Indexes nutzen, wenn Funktionen in Where-Bedingungen genutzt
werden

Partionierung, IOT und Bitmap-Indexes in Betracht ziehen

Bind-Variablen, Table-Aliase und FQN verwenden

Cursor-Loops vermeiden, SQL ist meist schneller als PL/SQL (Context-Switching)!

Distinct und Union sind kostenintensiv, Union All aber nicht!

Full-Table-Scans vermeiden, wenn < 40% der Zeilen betroffen sind

Kleine Tabellen, die FTS unterliegen, in den Keep-Pool des Buffer-Cache aufnehmen

EXISTS und NOT-EXISTS als Outer-Joins umschreiben

Temporäre Tabellen bei komplexen Unterabfragen in Betracht ziehen

Denormalisierung und Materialized Views bewusst einsetzen
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
ORACLE TUNING – TEIL 2
_ Einsatz von Automatischem SQL-Tuning
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
SQL-TUNING
 Schlecht laufende SQL-Statements können eine Applikation massiv
verlangsamen.
 Kommt es in der Applikation zu datenbankseitigen
Performanceproblemen, hat dies meist mit schlecht laufenden SQLStatements zu tun.
 Leider haben Entwickler oft zu wenig SQL-Tuning-Kenntnisse.
 DBAs haben meist zu wenig Kenntnisse bzgl. der Applikation.
 Klassische Verfahren sind das Erstellen und Analysieren von TraceFiles und die Analyse von sog. Ausführungsplänen. Beides setzt
einiges an Erfahrung voraus.
 An dieser Stelle setzen die Möglichkeiten des Automatischen SQLTunings von Oracle ein, die beide Seiten bei der Lösung von
Performanceproblemen unterstützen.
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
21
BEGRIFFE RUND UM DAS AUTOMATISCHE SQL-TUNING
 Automatic Tuning Optimizer (ATO)
 Query Optimizer
 SQL Tuning Advisor
 SQL Tuning Tasks
 SQL Tuning Sets
 SQL Profile
 Automatic Workload Repository (AWR)
Diese Begriffe und die damit verbundenen Funktionalitäten sind
zahlreichen Administratoren und Entwicklern nicht wirklich bekannt.
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
22
QUERY OPTIMIZER / AUTOMATIC TUNING OPTIMIZER
SQLStatements
Query Optimizer
Ausführungspläne
 Query Optimizer muss sich beeilen!
 Der sog. Automatic Tuning Optimizer
kann sich hingegen mehr Zeit lassen.
 Ein Expertensystem, das bei Bedarf
aufgerufen wird, längere Laufzeiten hat
und dem Anwender Tuning-Tipps inkl.
Skripts hierfür liefert.
Oracle-Datenbank
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
23
WIE SIEHT EIN SQL-AUSFÜHRUNGSPLAN AUS?
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
24
WIE KOMMT DIE DATENBANK ZU EINER ENTSCHEIDUNG?
 Die Datenbank muss viele Dinge entscheiden. Z.B.:
 Wird über einen Index (falls es einen passenden gibt) zugegriffen
oder lieber die ganze Tabelle gescannt.
 Wie werden Joins durchgeführt (z.B. Hash-Joins oder Nested
Loops)
 Um solche Entscheidungen treffen zu können, benötigt der Query
Optimizer Informationen, die sog. Statistiken, die im sog. Data
Dictionary abgelegt werden.
 Oracle erfasst Statistiken großenteils automatisch.
 Der Automatic Tuning Optimizer erfasst zahlreiche zusätzliche
Informationen, die mit den „normalen“ Statistiken nicht erfasst
werden. Das führt zu Verbesserungsvorschlägen und/oder besseren
Ausführungsplänen.
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
25
SQL-TUNING ADVISOR
 Das Werkzeug, mit dem der Automatic Tuning Optimizer dieses tut,
ist der SQL Tuning Advisor (Package DBMS_SQLTUNE).
 Der Anwender kann hiermit explizit SQL-Statements detailliert
untersuchen lassen.
 Meist sind dies Statements, von denen man festgestellt hat, dass sie
große Last auf der Datenbank verursachen und so ggf. die gesamte
Anwendung ausbremsen.
 Dies kann viele Gründe haben (z.B. fehlende Indizes,
Defragmentierung, veraltete Statistiken, suboptimale
Ausführungspläne).
 Die Statements werden an den SQL Tuning Advisor übergeben und
dieser prüft nun, welche Optimierungsmöglichkeiten es gibt.
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
26
SQL TUNING TASKS
 Zu diesem Zweck legt der SQL Tuning Advisor eine Aufgabendefinition (Task) in der Datenbank an, der die Untersuchung
vornehmen soll. Dies ist der sog. SQL Tuning Task .
Anlegen eines Tuning Tasks: DBMS_SQLTUNE.CREATE_TUNING_TASK
Ausführen eine Tuning Tasks: DBMS_SQLTUNE.EXECUTE_TUNING_TASK
Tuning-Bericht anzeigen: DBMS_SQLTUNE.REPORT_TUNING_TASK
Löschen eines Tuning Tasks: DBMS_SQLTUNE.DROP_TUNING_TASK
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
27
SQL TUNING TASKS UND SQL PROFILE
 Der Bericht, den der SQL Tuning Advisor mit Hilfe eines Tuning Tasks
erstellt, kann verschiedene Arten von Verbesserungsvorschläge
enthalten. Er enthält nicht nur Vorschläge sondern auch die Skripte,
mit denen man diese Vorschläge direkt umsetzt!
 SQL Tuning Advisor kann ein SQL-Statement nehmen und
verschiedene Ausführungsplanvarianten schlicht ausprobieren.
 Findet er einen Plan, der besser ist, als der der zur Zeit verwendet
wird, legt er ein sog. SQL Profil an.
 Ein SQL Profil enthält einige zusätzliche Statistiken zu einem SQLStatement.
 Meist ist es sinnvoll, einen vorgeschlagenes SQL Profil zu akzeptieren
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
 Mehrere SQL-Statements können zu einem sog. SQL Tuning Set
zusammengefasst und gemeinsam analysiert werden
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
28
NOCHMAL DIE BEGRIFFE IM ÜBERBLICK
SQL-Statement
Query Optimizer
Ausführungsplan
SQL Tuning Task
TuningVorschläge
SQL-Profil
SQL Tuning
Advisor
Automatic
Tuning Optimizer
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
29
SQL-TUNING IM ORACLE ENTERPRISE MANAGER
 Übersicht im Bereich „Performance“
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
30
SQL-TUNING IM ORACLE ENTERPRISE MANAGER
 Übersicht im Bereich „Top Aktivität“
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
31
SQL-TUNING IM ORACLE ENTERPRISE MANAGER
 Maske zu „SQL Tuning Advisor planen“
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
32
SQL-TUNING IM ORACLE ENTERPRISE MANAGER
 Lauf eines Tuning Tasks
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
33
SQL-TUNING IM ORACLE ENTERPRISE MANAGER
 Ergebnis des Tuning Tasks; in diesem Fall ein SQL Profil
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
34
SQL-TUNING IM ORACLE ENTERPRISE MANAGER
 Vergleich alter und neuer Ausführungsplan
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
35
AWR (AUTOMATIC WORKLOAD REPOSITORY)
 Oft ist es allerdings nicht im ersten Moment klar, welche SQLStatements für die Performanceprobleme zuständig sind. Hier kann
das sog. AWR (Automatic Workload Repository) weiterhelfen.
 Das AWR sammelt, wenn aktiviert, permanent zahlreiche Statistiken
zu aktiven Sessions, Zugriffen, Warteereignissen etc.
 Auf die Informationen des AWR kann über zahlreiche Dictionary Views
zugegriffen werden.
 Auf den Informationen des AWRs baut der sog. ADDM (Automatic
Database Diagnostic Monitor) auf. Der ADDM gibt einen Report mit
Empfehlungen zur DB aus.
 Ein Beispiel …
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
36
VIELEN DANK
_
für Ihre Aufmerksamkeit
 Falls Sie Fragen zu dieser Präsentation haben, sprechen Sie uns einfach
an.
 Ihre Ansprechpartner
Dr. Götz Gleitsmann
+49 228-95693-0
[email protected]
www.orbit.de
Frank Winter
+49 228-95693-0
[email protected]
www.orbit.de
© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor
37