oracle tuning – teil 1 - ORBIT Gesellschaft für Applikations
Werbung
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
