Johannes Ahrends CarajanDB GmbHblogs.gm.fh-koeln.de/faeskorn/files/2014/01/Ahrend... · •Hochverfügbarkeit (RAC, Data Guard, Failsafe, etc) •Einsatz der Oracle Standard Edition

© 2013 CarajanDB GmbH www.CarajanDB.com

Johannes Ahrends

CarajanDB GmbH


2

• CarajanDB

• Warum ist eine Anwendung langsam?

• Beispiele von „echten“ Performanceproblemen


3

• Experten mit über 20 Jahren Oracle Erfahrung

• Firmensitz in Erftstadt bei Köln

• Spezialisten für

• Oracle Datenbank Administration

• Hochverfügbarkeit (RAC, Data Guard, Failsafe, etc)

• Einsatz der Oracle Standard Edition

• Oracle Migrationen (HW, Unicode, Konsolidierung, Standard Edition)

• Replikation (Goldengate, SharePlex, Dbvisit)

• Performance Tuning

• Schulung und Workshops (Oracle, Toad)


4

• Ungünstig programmiert

• Verarbeitung zu aufwändig

• Es werden zu viele Daten gelesen

• Datenverarbeitung zu langsam

• Datenselektion zu aufwändig

• Änderung der Daten zu kompliziert

• Datenbank zu langsam

• Unterdimensionierte Hardware

• Zu viel Last

• Falsch konfigurierte Datenbank


5

• Subjektive Ursachen

• Schlechtes Wetter

• Persönliche Probleme

• Neue Softwareversion

„Früher war alles besser“

• Objektive Ursachen

• Datenmenge hat zugenommen

• Fehler durch Batchverarbeitung

• Falscher Ausführungsplan

• Falsche Statistiken


6

• Forrester Research



8

1. Parsen der Anweisung

• Ermitteln, ob identische Anweisung bereits im Library Cache vorliegt

• Syntax und Semantik der Anweisung prüfen

• Evtl. Sperren von verwendeten Objekten

• Erstellen des Ausführungsplans und Abspeichern im Library Cache

2. Ausführen

• ausgewählte Zeilen ermitteln

3. Abrufen

• Ermitteln, ob betroffene Elemente bereits im Database Buffer Cache vorliegen

• ggfs. Daten aus den Datenfiles in den Database Buffer Cache laden

• zurückgeben der Daten an den Benutzer-Prozess


9

1. Parsen der Anweisung • Ermitteln, ob identische Anweisung bereits im Library Cache vorliegt

• Syntax und Semantik der Anweisung prüfen

• Evtl. Sperren von verwendeten Objekten

• Erstellen des Ausführungsplans und Abspeichern im Library Cache

2. Ausführen • Lesen der Daten

(aus Undo-Segmenten, Database Buffer Cache oder Datenfiles)

• Sperren auf zu ändernde Zeilen setzen

• Protokollieren der Änderungen im Redolog Buffer

• Erstellen des "Before Image" im Undo Segment und Änderung an den Originaldaten im Database Buffer Cache vornehmen „Before Images“ ebenfalls im Redolog Buffer protokollieren

• Markieren der geänderten Blöcke als "dirty"


10

• Wiederverwendung von Befehlen • Parsen wird eingespart • Gleicher Ausführungsplan • Nur bei identischen Befehlen (gleicher Hash-Value)

• Beispiel: • Nicht wiederverwendbar (Literale):

• Wiederverwendbar (Bindevariable):

• Bindevariablen werden erst zur Laufzeit hinzugefügt

SELECT vorname, nachname FROM personen

WHERE nachname = 'Meier';

SELECT vorname, nachname FROM personen

WHERE nachname = :nachname;


11

• Über Statistiken werden die Plankosten ermittelt.

• Tabellen und Indizes werden überwacht („MONITORING“) und automatisch neu analysiert, wenn sich mehr als 10% der Daten seit der letzten Analyse geändert hat.

• Die Statistiken sollten immer aktuell sein! Genutzt werden:

• Tabellenstatistiken

• Spaltenstatistiken

• Indexstatistiken

• Systemstatistiken (CPU, Memory, I/O)

• Server Parameter (init.ora)


12

• Der Oracle Optimizer entscheidet aufgrund der Statistiken, auf welche Art das Statement abgearbeitet werden soll.

• Es wird ein Ausführungsplan erstellt. Alle beteiligten Objekte und die Reihenfolge der Abarbeitung sind dort beschrieben.

• Der Ausführungsplan wird ausgeführt.


13

• Beinhaltet die Ausführung des Statements

• Unterscheidung zwischen: • Ermitteln eines exemplarischen Ausführungsplans

„wenn ich diesen Befehl jetzt ausführen würde, was würde für ein Plan benutzt“

Befehl: „explain Plan for“

Tools (Toad, SQL-Developer, Oracle Enterprise Manager)

Enthält die Umgebung, unter der ich den Plan aufrufe

• Tatsächlicher Ausführungsplan V$SQL_PLAN

V$SQL_PLAN_STATISTICS

Natürlich nur für bereits ausgeführte Befehle

Nur im Cache (ev. über Tools separat abgespeichert)


14

SQL> @rdbms/admin/UTLXPLS.SQL

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1342056138

---------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3583 | 328K| 386 (3)| 00:00:05 |

|* 1 | HASH JOIN | | 3583 | 328K| 386 (3)| 00:00:05 |

|* 2 | HASH JOIN | | 700 | 56700 | 103 (4)| 00:00:02 |

| 3 | MERGE JOIN | | 700 | 37800 | 89 (5)| 00:00:02 |

| 4 | TABLE ACCESS BY INDEX ROWID| STATUS | 5 | 170 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | PK_STATUS | 5 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 700 | 14000 | 87 (5)| 00:00:02 |

|* 7 | TABLE ACCESS FULL | AUFTRAEGE | 700 | 14000 | 86 (4)| 00:00:02 |

| 8 | TABLE ACCESS FULL | PERSONEN | 10000 | 263K| 14 (0)| 00:00:01 |

| 9 | TABLE ACCESS FULL | POSITIONEN | 353K| 4482K| 281 (2)| 00:00:04 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."AUFID"="PO"."AUFID")

2 - access("P"."PERSID"="A"."PERSID")

6 - access("S"."STATUSID"="A"."AUFSTATUS")

filter("S"."STATUSID"="A"."AUFSTATUS")

7 - filter(TO_CHAR(INTERNAL_FUNCTION("A"."AUFDATUM"),'DD.MM.YYYY')='10.08.2007')

25 Zeilen ausgewõhlt.


15


16



18

• Diätdatenbank („Online Abnehmen“)

• Neuer Kunde klagt über massive Probleme:

• Server mit 16 Cores fast permanent zu 100 % ausgelastet

• Grund: Ende Dezember wurde ein neuer „Anwender“ in Betrieb genommen

• Administrator beklagt sich bei Facebook mit einigen Screenshots des Servers (!)


19

• CPU Load Ende 2013 Anfang 2014


20

• Remote Login mit Teamviewer

• Check der wesentlichen Datenbank Parameter

• Keine Auffälligkeiten

• Ermitteln der derzeitigen Statistikdaten (Statspack)

• Anschließend DBA zum Mittagessen geschickt …

• Weiterer Snapshot der Statistikdaten


21

• Index auf einer Tabelle fehlte


22

• Langsam oder schnell?

SQL> SELECT p.vorname, p.nachname, a.plz, a.ort

FROM personen p INNER JOIN adressen a ON (p.persid = a.persid)

WHERE p.nachname like 'M%'

AND p.vorname = 'Karl-Gustav-Theodor';


23

• Schnell wenn:

• Wenig Datensätze (z.B. Testsystem mit 100 Sätzen)

• Indizierung des Vornamens

• Langsam wenn:

• Viele Datensätze (Produktion!!!)

• Indizierung des Nachnamens und Vornamens (Zusammengesetzter Index)


24

• Neue Anwendung für Krankheitsgruppen

• Anwender bemängeln lange Laufzeiten für Anpassung der Daten

• Auffällig:

• Persid ist Primärschlüssel!

• Langsam oder schnell?

SQL> DELETE FROM partner

WHERE id=:id;


25


26

• Warum werden so viele Datensätze gelesen?

• Tabelle „partner“ hat abhängige Datensätze (Master – Detail)

• Kein Index auf den Foreign Key:

• Table Lock auf die Detail Tabelle, wenn Update auf Primary Key der Master Tabelle

• Updates auf den Primary Key sind selten

• Index auf Foreign Key nicht erforderlich

• Aber:

• DELETE FROM Master Tabelle FULL TABLE SCAN auf die Detail Tabelle


27 2_abfrage_ls.bat

• Abfragen der Versichertendaten langsam

• Index oder nicht?

SQL> CREATE INDEX idx_name

ON personen (vorname, nachname);

SQL> SELECT anrede, vorname, nachname

2 FROM tuk.personen pe

3 WHERE pe.nachname LIKE 'wei_'

4 AND pe.vorname LIKE 'Martin';

ANRED VORNAME NACHNAME

----- -------------------- --------------------

Herr Martin Weiß

Herr Martin Weis

Herr Martin Weiz

3 Zeilen ausgewählt.


28


29


30

• Versuch einer Analyse:

SQL> SELECT anrede, vorname, nachname

2 FROM tuk.personen pe

3 WHERE pe.nachname LIKE 'wei_'

4 AND pe.vorname LIKE 'Martin';

ANRED VORNAME NACHNAME

----- -------------------- --------------------

Herr Martin Weiß

Herr Martin Weis

Herr Martin Weiz

3 Zeilen ausgewählt.


31

• Versuch einer Analyse:

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------

Plan hash value: 1826680655

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 25 | 275 (2)| 00:00:04 |

| 1 | SORT ORDER BY | | 1 | 25 | 275 (2)| 00:00:04 |

|* 2 | TABLE ACCESS FULL| PERSONEN | 1 | 25 | 274 (1)| 00:00:04 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("PE"."NACHNAME" LIKE 'wei_' AND

NLSSORT("PE"."VORNAME",'nls_sort=''BINARY_CI''') =HEXTORAW('6D617274696E00') )


32

• ALTER SESSION SET nls_sort=binary_ci;

• Sortierung ist unabhängig von Groß- / Kleinschreibung aber abhängig von Akzenten

• Alternativen:

binary_ai Case und Akzent insensitiv

german_ai Deutsche Sortierung, Case und Akzent insensitiv

…

• ALTER SESSION SET nls_comp=linguistic;

• Filter verwenden die gleiche Funktion wie NLS_SORT, d.h. in diesem Fall ist die WHERE-Clausel unabhängig von Groß- / Kleinschreibung und von Akzenten

• Alternativen:

BINARY oder ANSI


33

• Entweder Linguistische Suche ausschalten

• … oder Index auf Linguistische Suche

CREATE INDEX idx_name2

ON personen (NLSSORT (vorname, 'nls_sort=binary_ci'),

NLSSORT (nachname, 'nls_sort=binary_ci'));


34

• Einsatz in der Schweiz Ende 2012

• Anwendung überwacht Mobilfunksender

Bei 3000 überwachten Servern ca. 15 Minuten pro Report

Bei 9000 überwachten Servern Abbruch des Reports nach 60 Minuten bzw. 80 Minuten Laufzeit

Datenbankgröße ca. 20 GByte (5,6 GB Tabellen; 6,5 GB Indizes)


35

• Statspack Report

• Elapsed Time: 3.011,8(s) = ca. 50 Minuten

SQL ordered by Elapsed Time DB/Inst: NCDBP/NCDBP Snaps: 19907-19909

…

Elapsed Elapsed Time

Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id

---------------- -------------- ------------- ------ ------ ------ -------------

3,011.8 1 3,011.85 61.5 98.8 1.1 bx7m7493x4fbu

Module: perl.exe

SELECT COUNT(DISTINCT h.guid) as host_count, COUNT(DISTINCT fav.app_id) as app_c

ount, COUNT(DISTINCT fav.vuln_id) as vuln_count FROM host h INNER JOIN (SELECT h

s.guid, NVL(ROUND(AVG(CASE WHEN hs.host_score = 0 THEN NULL ELSE hs.host_score E

ND), 0), 0) as host_score, MAX(hs.audit_id) as audit_id, hs.vne_id, hs.host_guid


36

• Abfrage: SELECT COUNT(DISTINCT …)

• disk: 29645 = 232 Mbyte

• query: 966964995 = 7.399 Gbyte = ca. 7.4 Terabyte (für eine 20 GB große Datenbank)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 13.87 13.88 0 5 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 2962.56 2997.92 29645 966964990 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 2976.44 3011.81 29645 966964995 0 1


37

• Anwendung ursprünglich für SQL-Server geschrieben

• Daraus folgt:

• SQL-Server standardmäßig Index-Organized-Tabellen

• Oracle standardmäßig Heap Tabellen

Kein Index auf die abgefragte Spalte


38

• Nicht zu komplexe Tabellenstrukturen (zu viele Spalten)

• Möglichst wenig Änderung in der Tabellenstruktur (ALTER TABLE ADD/DROP COLUMN)

• Möglichst immer Primary Key

• Foreign Keys benennen und indizieren


39

• Analysieren der SQL-Befehle

• Wird ein Index benutzt

• Gibt es die Möglichkeit für zusammengesetzte Indizes

• Was ist mit abhängigen Spalten (z.B. Postleitzahl, Ort)

• Anwender bei Abfragen „leiten“, um bestimmte, indizierte Spalten zu benutzen

• Vermeiden von Full-Table-Scans

• Bewusste Verwendung von Variablen und Literalen

• Literale bei Spalten mit wenigen Ausprägungen (z.B. Auftragsstatus)

• Variablen bei „üblichen“ Spalten (z.B. Vorname, Artikel, …)


40

• Regelmäßige Health-Checks

• Gibt es Tabellen ohne Index

• Wie gut ist der Cache ausgelastet

• Erstellung von Baselines für kritische SQL-Befehle

• Festlegen des Ausführungsplans

• Backups zu lastarmen Zeiten planen

• Möglich konstante Konfiguration

• keine dynamisch veränderbaren Parameter, z.B. sga_target, memory_target

• Permanente Performanceüberwachung


41

• Eine optimale Konfiguration kann nur gelingen, wenn Architekten, Entwickler und Administratoren zusammenarbeiten

• „Fingerpointing“ führt zu nichts!


[email protected]

www.carajandb.com

www.carajandb.com/blogs

mailto:[email protected]

http://www.carajandb.com/

http://www.carajandb.com/blogs

Documents

Johannes Ahrends CarajanDB GmbHblogs.gm.fh-koeln.de/faeskorn/files/2014/01/Ahrend... · •Hochverfügbarkeit (RAC, Data Guard, Failsafe, etc) •Einsatz der Oracle Standard Edition