Warum ist eine Anwendung langsam?

Johannes Ahrends
CarajanDB GmbH
www.CarajanDB.com
© 2013 CarajanDB GmbH
• CarajanDB
• Warum ist eine Anwendung langsam?
• Beispiele von „echten“ Performanceproblemen
2
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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)
3
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
4
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
5
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Forrester Research
6
www.CarajanDB.com
© 2013 CarajanDB GmbH
www.CarajanDB.com
© 2013 CarajanDB GmbH
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
8
www.CarajanDB.com
© 2013 CarajanDB GmbH
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"
9
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Wiederverwendung von Befehlen
• Parsen wird eingespart
• Gleicher Ausführungsplan
• Nur bei identischen Befehlen (gleicher Hash-Value)
• Beispiel:
• Nicht wiederverwendbar (Literale):
SELECT vorname, nachname FROM personen
WHERE nachname = 'Meier';
• Wiederverwendbar (Bindevariable):
SELECT vorname, nachname FROM personen
WHERE nachname = :nachname;
• Bindevariablen werden erst zur Laufzeit hinzugefügt
10
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Ü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)
11
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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.
12
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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)
13
www.CarajanDB.com
© 2013 CarajanDB GmbH
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')
www.CarajanDB.com
25 Zeilen ausgewõhlt.
14
© 2013 CarajanDB GmbH
15
www.CarajanDB.com
© 2013 CarajanDB GmbH
16
www.CarajanDB.com
© 2013 CarajanDB GmbH
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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 (!)
18
www.CarajanDB.com
© 2013 CarajanDB GmbH
• CPU Load Ende 2013 Anfang 2014
19
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
20
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Index auf einer Tabelle fehlte
21
www.CarajanDB.com
© 2013 CarajanDB GmbH
SQL> SELECT
FROM
WHERE
AND
p.vorname, p.nachname, a.plz, a.ort
personen p INNER JOIN adressen a ON (p.persid = a.persid)
p.nachname like 'M%'
p.vorname = 'Karl-Gustav-Theodor';
• Langsam oder schnell?
22
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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)
23
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Neue Anwendung für Krankheitsgruppen
• Anwender bemängeln lange Laufzeiten für Anpassung der Daten
• Auffällig:
SQL> DELETE FROM partner
WHERE id=:id;
• Persid ist Primärschlüssel!
• Langsam oder schnell?
24
www.CarajanDB.com
© 2013 CarajanDB GmbH
25
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
26
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
----Herr
Herr
Herr
VORNAME
-------------------Martin
Martin
Martin
3 Zeilen ausgewählt.
NACHNAME
-------------------Weiß
Weis
Weiz
2_abfrage_ls.bat
27
www.CarajanDB.com
© 2013 CarajanDB GmbH
28
www.CarajanDB.com
© 2013 CarajanDB GmbH
29
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
----Herr
Herr
Herr
VORNAME
-------------------Martin
Martin
Martin
NACHNAME
-------------------Weiß
Weis
Weiz
3 Zeilen ausgewählt.
30
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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') )
31
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
32
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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'));
33
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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)
34
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Statspack Report
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
• Elapsed Time:
3.011,8(s) = ca. 50 Minuten
35
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Abfrage: SELECT COUNT(DISTINCT …)
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
• disk:
• query:
29645
= 232 Mbyte
966964995 = 7.399 Gbyte = ca. 7.4 Terabyte (für eine 20 GB große Datenbank)
36
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
37
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
38
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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, …)
39
www.CarajanDB.com
© 2013 CarajanDB GmbH
• 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
40
www.CarajanDB.com
© 2013 CarajanDB GmbH
• Eine optimale Konfiguration kann nur gelingen, wenn Architekten, Entwickler und
Administratoren zusammenarbeiten
• „Fingerpointing“ führt zu nichts!
41
www.CarajanDB.com
© 2013 CarajanDB GmbH
[email protected]
www.carajandb.com
www.carajandb.com/blogs
www.CarajanDB.com
© 2013 CarajanDB GmbH