ORACLE TUNING – TEIL 1 - orbit.de · PDF file_ Unter UNIX liest ORACLE...

© 2013 ORBIT Gesellschaft für Applikations- und Informationssysteme mbH | Präsentation vom 18.09.2013 | Autor

ORACLE TUNING – TEIL 1

_ Einführung & Allgemeines


PERFORMANCE TUNING: OVERVIEW

_ Schematische Darstellung der wichtigsten Einflussfaktoren auf die Performance

Sessions

SQL

Blockgröße

I/O

Performance

Memory Locks


SEGMENTS, EXTENTS UND BLOCKS

Tablespace Segments Extents Blocks Rows

Default: 8KBMax: 32KB

_ Übersicht


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 (Big-Tables)


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

PCTUSED = 40%

100% – PCTFREE – 1 Rowwird 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.


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;


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


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 Optimizerzu 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.


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)


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


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ötigteBlattknoten (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


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 Unique Scan = Auffindung von einer ROWID im Index» Index Range Scan = Auffindung von mehr als einer ROWID im Index» Index Full Scan = Abruf aller ROWIDs aus dem Index, kann sortiert werden» Index Fast Full Scan = Abruf aller ROWIDs über Multiblock I/O, nicht sortierbar

SELECT * FROM sh.customers WHERE cust_id = 1;

SELECT cust_id FROM sh.customers ORDER BY cust_id;

SELECT cust_id FROM sh.customers WHERE cust_id > 1;

SELECT * FROM sh.customers WHERE cust_id > 1 AND

cust_id < 10;


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 * FROM sh.customers cu, sh.countries co

WHERE co.country_id = cu.country_id;

SELECT cu.cust_last_name

FROM oe.customers cu, oe.orders od

WHERE cu.customer_id = od.customer_id;


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 EXISTS

(SELECT customer_id FROM oe.customers c2);

SELECT c1.customer_id FROM oe.customers c1

WHERE c1.customer_id IN

(SELECT customer_id FROM oe.customers c2);


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-Parsingsparen

Ordered-Hint = Erzwingt die Verarbeitung der From-Klausel in angegebener Reihenfolge

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)

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;


QUERY HINTSHint 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


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


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


ORACLE TUNING – TEIL 2

_ Einsatz von Automatischem SQL-Tuning

21© 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 SQL-Statements 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 Trace-Files und die Analyse von sog. Ausführungsplänen. Beides setzt einiges an Erfahrung voraus.

An dieser Stelle setzen die Möglichkeiten des Automatischen SQL-Tunings von Oracle ein, die beide Seiten bei der Lösung von Performanceproblemen unterstützen.


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.


Oracle-Datenbank

Query Optimizer

Aus-führungs-pläne

SQL-Statements

Query Optimizer muss sich beeilen!

Der sog. Automatic Tuning Optimizerkann 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.

QUERY OPTIMIZER / AUTOMATIC TUNING OPTIMIZER


WIE SIEHT EIN SQL-AUSFÜHRUNGSPLAN AUS?


WIE KOMMT DIE DATENBANK ZU EINER ENTSCHEIDUNG?

Die Datenbank muss viele Dinge entscheiden. Z.B.:

Wird über einen Index (falls es einen passenden gibt) zugegriffenoder lieber die ganze Tabelle gescannt.

Wie werden Joins durchgeführt (z.B. Hash-Joins oder NestedLoops)

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.


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.


SQL TUNING TASKS

Zu diesem Zweck legt der SQL Tuning Advisor eine Aufgaben-definition (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


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 SQL-Statement.

Meist ist es sinnvoll, einen vorgeschlagenes SQL Profil zu akzeptierenDBMS_SQLTUNE.ACCEPT_SQL_PROFILE

Mehrere SQL-Statements können zu einem sog. SQL Tuning Setzusammengefasst und gemeinsam analysiert werden


NOCHMAL DIE BEGRIFFE IM ÜBERBLICK

SQL-Statement Query Optimizer Ausführungsplan

Automatic Tuning Optimizer

SQL Tuning Task Tuning-Vorschläge SQL-Profil

SQL Tuning Advisor


SQL-TUNING IM ORACLE ENTERPRISE MANAGER Übersicht im Bereich „Performance“


SQL-TUNING IM ORACLE ENTERPRISE MANAGER Übersicht im Bereich „Top Aktivität“


SQL-TUNING IM ORACLE ENTERPRISE MANAGER Maske zu „SQL Tuning Advisor planen“


SQL-TUNING IM ORACLE ENTERPRISE MANAGER Lauf eines Tuning Tasks


SQL-TUNING IM ORACLE ENTERPRISE MANAGER Ergebnis des Tuning Tasks; in diesem Fall ein SQL Profil


SQL-TUNING IM ORACLE ENTERPRISE MANAGER Vergleich alter und neuer Ausführungsplan


AWR (AUTOMATIC WORKLOAD REPOSITORY)

Oft ist es allerdings nicht im ersten Moment klar, welche SQL-Statements 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 …


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 [email protected]

Frank Winter+49 [email protected]

ORACLE TUNING – TEIL 1 - orbit.de · PDF file_ Unter UNIX liest ORACLE...

Documents

Transcript of ORACLE TUNING – TEIL 1 - orbit.de · PDF file_ Unter UNIX liest ORACLE...