Performance-Stabilisierung mit Einsatz von SQL Plan Baselines

© OPITZ CONSULTING Deutschland GmbH 2013 Seite 1Performance-Stabilisierung mit Einsatz von SQL Plan Baselines

Performance-Stabilisierung mit Einsatz von SQL Plan Baselines


<Bild oder OC-Tätigkeitsfeld-Logo>

Katja WernerSenior Consultant

OPITZ CONSULTING GmbH

mit Einsatz von SQL Plan Baselines

Nürnberg, 19.11.2013

Performance-Stabilisierung

© OPITZ CONSULTING GmbH 2012 Seite 3<Präsentationstitel – bitte im Folienmaster ändern>

MissionWir entwickeln gemeinsam mit allen Branchen Lösungen, die dazu führen, dass sich diese Organisationen besser entwickeln als ihr Wettbewerb.

Unsere Dienstleistung erfolgt partnerschaftlich und ist auf eine langjährige Zusammenarbeit angelegt.

LeistungsangebotBusiness IT AlignmentBusiness Information ManagementBusiness Process ManagementAnwendungsentwicklungSOA und System-Integration IT-Infrastruktur-Management

MärkteBranchenübergreifendÜber 600 Kunden

29%Industrie / Versorger /

Telekommunikation

29%Handel / Logistik / Dienstleistungen

42% Öffentliche Auftraggeber / Banken und Versicherungen / Vereine und Verbände

EckdatenGründung 1990 400 Mitarbeiter9 Standorte


1. Der Anfang

2. Etwas Theorie

3. Der Weg zu stabiler Performance

4. SQL Plan Baselines in der Praxis

5. Und noch etwas Praxis – Testcases

6. Am Ziel

Agenda


1 Der Anfang


Herausforderung

Als Datenbankadministrator beim Kunden

"Die Performance ist schlecht"


Ist die Performance

immer schlecht?

Nein. Letzte Woche war gut.

…

Ursachenforschung

... Ist mal besser. Und mal

schlechter.


2 Etwas Theorie


Wie entsteht der Explain Plan?

Query Transformer

Estimator Plan Generator

Bester Explain Plan


Was denken Sie?

A) Oracle kanns nicht besser.

B) Cost based Optimierung hat noch nie funktioniert.

C) Richtige Antwort.

Warum sind die Explain Plans so schlecht?


Warum sind die Explain Plans so „schlecht“?

Bindevariablen in Verbindung mit unterschiedlich großen Datenmengen

Funktionen/Prozeduren im Code

Viele Joinmöglichkeiten

Veraltete Statistiken

Ungünstige Einstellungen

C) Richtige Antwort(en):


Hint Stored Outline

SQL Profile

SQL Plan Baseline

Möglichkeiten zur Stabilisierung


3 Der Weg zu stabiler Performance


Fragen

Wieviel Statements sind betroffen?

Was für Statements sind betroffen?

Was will ich wie stabilisieren?


Der Weg zu stabiler Performance

Analyse (wechselnde Explain Plans, lang laufende SQL-Statements/Geschäftsprozesse)

Priorisieren, Stabilisierungsmaßnahme(n) festlegen

Datensammlung (DBA_HIST_SQLSTAT)


Anzahl unterschiedlicher Explain Plans

700

40 15 Für alle Statements auf der DB

1 2>2


Klassifizierung von SQL Statements

„Normales“ Statement:select * from employee where id=99;

Literalstatements:select * from employee where name=‘SCOTT‘;select * from employee where name=‘MUELLER‘;select * from employee where name=‘HUGO‘;

Statements mit Bindevariablen:select * from employee where name=:var1;


Für alle Statements auf der DB

1 2>2

Bindevariablen-Statements (ca. 550)

Unterschiedliche Explain Plans pro Statement“art“

Literalstatements (ca. 100)

Einfache Statements

(ca. 100)


Fazit für vorliegende Datenbank

Kaum Statements mit Literalen

Viele Statements mit Bindevariablen

Insgesamt ca. 50 Statements, die stabilisiert werden könnten

SQL Plan Baselines sollen eingesetzt werden

Große und kleine Datenmengen testen


4 SQL Plan Baselines in der Praxis


Das ist eine SQL Plan Baseline

ALL_ROWSDB_VERSION('11.2.0.3')OPTIMIZER_FEATURES_ENABLE('11.2.0.3')USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2")USE_NL(@"SEL$F5BB74E1" "A"@"SEL$2")LEADING(@"SEL$F5BB74E1" "B"@"SEL$2" "A"@"SEL$2" "C"@"SEL$2")INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("PROMOTIONS"."PROMO_ID"))INDEX(@"SEL$F5BB74E1" "A"@"SEL$2" ("PRODUCTS"."PROD_ID"))……


Voraussetzungen

Enterprise Edition

Bei Nutzung von SQL Tuning Sets: Tuning + Diagnostic Pack

OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE

Ab Oracle 11g


ADMINISTER SQL MANAGEMENT OBJECT

Zum vernünftig arbeiten können:

SELECT ANY DICTIONARY

CREATE TABLE + Quota

CREATE, ALTER SESSION

Evtl. Rechte auf Applikationsschemata

Evtl. ADVISOR

Rechte für Baselines


Wo bekomme ich SQL Plan Baselines her?

Automatisches Laden, wenn:

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE

Manuelles Laden aus:

SQL Tuning Set

Cursor Cache

Stored Outline

Import aus anderen Datenbanken

AWR (nur über SQL Tuning Set)


Export und Import von Baselines

Erstellen einer Stagingtabelle:DBMS_SPM.CREATE_STGTAB_BASELINE

Befüllen der Stagingtabelle:DBMS_SPM.PACK_STGTAB_BASELINE

Export/Import und dann wieder entpacken:DBMS_SPM.UNPACK_STGTAB_BASELINE


SQL Plan History

Wie entsteht der Explain Plan mit Baselines?

Query Transformer

Estimator Plan Generator

Bester Explain Plan

SQL Plan Baseline


Wie entsteht der Explain Plan mit Baselines?

Vorhandene SQL Plan Baseline:

Vorhandene SQL Plan Baseline und vom Optimizer entwickelter Alternativplan:


Wird meine Baseline genutzt?

V$SQL.SQL_PLAN_BASELINE für aktuell laufende Statements

DBA_SQL_PLAN_BASELINES.LAST_EXECUTED (nicht immer zuverlässig)


Lessons Learned

Baseline wirkt für gesamte Datenbank

dbms_xplan.display_sql_plan_baseline

Laden per Tracefile – SQL Tuning Set nicht möglich

Für Join von Tabelle mit Collection CAST-Operator nutzen

Retentiontime

Automatisch gesammelte Baselines erst beim 2. Ausführen

DBA_SQL_PLAN_BASELINES.LAST_EXECUTED


5 Und noch etwas Praxis - Testcases


Ist die Performance

immer schlecht?

Nein. Letzte Woche war gut.

…

Ursachenforschung

Ja. Performance war wunderbar.

Wo sind die Testergebnisse?

Konkrete Daten, Laufzeiten, Explain Plans,

…?

Häh?

... Ist mal besser. Und mal

schlechter.

Ne. Das haben wir nicht mehr.

Wurde der Prozess

getestet?


Ziel von Testcases

Nachvollziehbarkeit: Was wurde getestet und wie waren die Ergebnisse?-> Vergleich zwischen Gestern und Heute

Wiederholbarkeit: Wie sind die Ergebnisse, wenn ich den Test erneut durchführe?


Was gehört zu einem Testcase?

Dokumentation:

Welcher konkrete Geschäftsprozess? (Beispiel: Rechnungserstellung für Telefonnummer 0172-2048790 für die Monate 01-03/2013)

Start- und Endezeitpunkt des Prozesses

SQL Tuning Set für alle SQLs

AWR-Report für genau diesen Zeitraum

Skripts


Umsetzung der Testcases

Erstellung zusammen mit Fachbereich für konkret (!!!) definierte Geschäftsprozesse

Immer dieselben Datenmengen pro Testcase

Für viel und wenig Daten

Messung gesamter Prozesse, nicht einzelner SQL Statements


Best Practice

Modulnamen verwenden

Shared Pool und Buffer Cache leeren

SQL Tuning Sets

AWR-Snapshots vor und nach Testlauf

Große sowie kleine Datenmengen

Testdatenmenge möglichst langlebig wählen

Tuningmaßnahmen im Releaseplan einplanen


Woran festmachen, ob die Performance besser ist?

Testumgebung:

Testcases ablaufen lassen

Laufzeiten messen

Vergleichen und evtl. Unschärfen auswerten

Produktion

Vergleiche der Prozesslaufzeiten mit Testcases


Unschärfen

Ressourcen, die nicht nur von Testdatenbank allein genutzt werden (Storage, Netzwerk, CPU)

Bei Vergleich zwischen Test und Produktion:

Prozesse laufen in Produktion nicht isoliert

Unterschiedliche abgefragte Datenmengen

Unterschiedliche Hardware

AWR-Reports, SQL Tuning Sets und Laufzeiten helfen bei der Beurteilung


6 Am Ziel


Die Kür – SQL Plan Baselines + Testcases

1. Statement-auswahl 2. Testcase

3. Baseline/ Tunen

4. Kontrolle

Test/EntwicklungProduktion

2. Testcase

3. Baseline/ Tunen

Abnahme


Erkenntnisse fürs weitere DBA-Leben

1. Tune nicht nur. Stabilisiere!

2. Klassifiziere deine Statements nach Art und Anzahl und du kennst die Datenbank!

3. Priorisiere!

4. Nutze Testcases für Vergleiche und Wiederholbarkeit!


Nein. Ist super. Immer! Stabil!

Ist die Performance immer noch schlecht?

Am Ziel!

Testcases haben wir auch

…

… zum Vergleich bei:- Problemen- Upgrades- Codeänderungen


Fragen und Antworten


Kontakt

Katja WernerSenior Consultant

OPITZ CONSULTING Deutschland GmbHStandort MünchenWeltenburger Straße 4 | 81677 MünchenTel. +49 (89) 68 00 [email protected]

youtube.com/opitzconsulting

@OC_WIRE

slideshare.net/opitzconsulting

xing.com/net/opitzconsulting

mailto:[email protected]

Documents

Performance-Stabilisierung mit Einsatz von SQL Plan Baselines