PL/SQL-Performance-Tuning in 11g

PL/SQL-Performance-Tuning
in 11g
DOAG Konferenz Nürnberg 2009
Dr. Hildegard Asenbauer
MuniQSoft GmbH
Gegründet 1998
Tätigkeitsbereiche:
Oracle Schulungen
Oracle IT Consulting & Services
Software Lösungen
Oracle Lizenzen
MuniQSoft GmbH
Grünwalder Weg 13 a
D-82008 Unterhaching
www.muniqsoft.de
+49 89 6628 6789-0
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 2
Tuning
Datenbank-Tuning
DBA
SQL-Tuning
Entwickler
PL/SQL-Tuning
Entwickler
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 3
Vermeidung wiederholter Ausführungen
Vor 11g:
Statische Tabellen in PL/SQL-Arrays vorhalten
Returnwert von Funktionen in Variablen speichern
Vorteil: schnellerer Zugriff
Nachteile:
nur innerhalb der gleichen Session
Keine Invalidierung bei Änderungen
Speicherverbrauch
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 4
11g: RESULT CACHE
Result Cache in SGA  Session-übergreifend
Neuer Parameter result_cache_max_size
Muss > 0 sein
Default abhängig von anderen init.ora-Parametern
(memory_target / sga_target / shared_pool_size)
In SQL und PL/SQL nutzbar
SQL: result_cache_mode bzw. Hint result_cache
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 5
RESULT CACHE
Schlüsselwort RESULT_CACHE bei Funktionsdeklaration
Package-Funktionen: Header UND Body
Bei Zugriff auf Tabellen: RELIES_ON
Package-Funktionen: Body
Cache wird bei Tabellenänderung automatisch invalidiert
ACHTUNG: KEIN Fehler, wenn Klausel fehlt!
Funktion wird bei weiteren Aufrufen nicht ausgeführt,
wenn sich Parameter-Werte nicht geändert haben
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 6
RESULT CACHE
Gespeichert werden:
Input-Parameter
Ergebnis
Invalidierung:
Cache wird für EIGENE Session während offener
Transaktion nicht mehr genutzt
Invalidierung erst bei COMMIT
 Lesekonsistenz ist gewährleistet
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 7
Beispiel
CREATE OR REPLACE PACKAGE p_cache
AS
FUNCTION f_dept_count(p_deptno in number)
RETURN NUMBER RESULT_CACHE ;
END;
/
CREATE OR REPLACE PACKAGE BODY p_cache
AS
FUNCTION f_dept_count(p_deptno IN NUMBER)
RETURN NUMBER RESULT_CACHE
RELIES_ON (emp)
IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp
WHERE deptno = p_deptno;
RETURN v_count;
END;
END;
/
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 8
Einschränkungen zum RESULT CACHE
Keine OUT-Parameter
Keine Selects auf SYS-Tabellen und -Views
Nur skalare Parameter
Keine LOBs, Ref Cursor oder Objekte als Returntypen
Nicht zulässig bei Invoker Rights
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 9
Bulk Binds
Bereits ab Version 8i verfügbar, seither laufend
Erweiterung der Syntax
Verwendung von Collections zwingend
Seit Version 9.2 sind Records als Datentyp erlaubt
Bei Cursor FOR-Schleifen implizites Bulk DML durch
Parameter plsql_optimize_level möglich
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 10
Bulk Binds
Bulk Select
SELECT .. BULK COLLECT INTO ..
FETCH .. BULK COLLECT INTO .. [LIMIT X]
Speicherplatz wird nicht automatisch wieder freigegeben
Bulk DML
FORALL i IN x..y [SAVE EXCEPTIONS]
FORALL i IN INDICES OF v_arr
[BETWEEN x AND y] [SAVE EXCEPTIONS]
FORALL i IN VALUES OF v_arr [SAVE EXCEPTIONS]
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 11
Bulk Binds in 11g
Endlich kann im DML-Befehl auf einzelne Felder des
Records zugegriffen werden!
Damit uneingeschränkt für alle DML-Befehle nutzbar:
FORALL i IN
UPDATE
SET
WHERE
v_rec.FIRST..v_rec.LAST
tab
col1 = v_rec(i).field1
col2 = v_rec(i).field2;
FORALL i IN v_rec.FIRST..v_rec.LAST
DELETE FROM tab
WHERE col1 = v_rec(i).field1;
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 12
PLSQL_OPTIMIZE_LEVEL
Parameter in 10g eingeführt, mögliche Einstellungen:
0: keine zusätzlichen Optimierungen, Verhalten wie 9i
1: Optimierungen wie Entfernung unnötiger Berechnungen
2 (Default): weitergehende Optimierungen
z.B. implizite Umwandlung von CURSOR FOR-Schleifen in
BULK SELECT
Erweiterung in 11g:
3: weitergehend als 2
u.a. auch automatisches Inlining
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 13
PLSQL_OPTIMIZE_LEVEL
Auf System- / Session-Ebene einstellbar
ALTER SYSTEM
SET PLSQL_OPTIMIZE_LEVEL=1;
ALTER SESSION
SET PLSQL_OPTIMIZE_LEVEL=3;
Für einzelne Programmeinheit einstellbar
ALTER PROCEDURE <my_proc> COMPILE
plsql_optimize_level=3;
DD-View: DBA_PLSQL_OBJECT_SETTINGS
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 14
Native Kompilierung
Erstellung von Maschinencode, Interpreter-Schritt entfällt
Eingeführt in 9i, aber umständlich
Vereinfachung in 10g:
automatische Suche nach OS-spezifischem C-Compiler
(GCC, VC++, .Net)
Datei $ORACLE_HOME/plsql/spnc_commands
Weiter C-Compiler und externes Verzeichnis nötig
Parameter PLSQL_OPTIMIZE_LEVEL muss mindestens
auf 2 stehen
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 15
Native Kompilierung 11g
Kein C-Compiler mehr nötig
Kein externes Verzeichnis mehr nötig
Speicherung im SYSTEM-Tablespace
Einzig nötiger Parameter: PLSQL_CODE_TYPE
Neuer Datentyp SIMPLE_INTEGER
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 16
Native Kompilierung
Gültige Werte für PLSQL_CODE_TYPE:
NATIVE
INTERPRETED
Native Kompilierung einstellbar:
Auf System- und Sessionebene
ALTER SESSION SET PLSQL_CODE_TYPE = 'NATIVE' ;
Auf Prozedurebene
ALTER PROCEDURE <my_proc> COMPILE
PLSQL_CODE_TYPE = NATIVE;
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 17
SIMPLE_INTEGER
Subtyp von PLS_INTEGER
Gleicher Wertebereich (-2.147.483.648 bis 2.147.483.647)
NOT NULL Constraint
Kein Überlauf, stattdessen Wrapping
Nur bei Nativer Kompilierung mit Vorteilen
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 18
Inlining
Inlining verhindert Overhead bei Aufruf von Subroutinen
Pragma: INLINE
PRAGMA INLINE (prozedur, 'YES' | 'NO');
PLSQL_OPTIMIZE_LEVEL >= 2
Position: unmittelbar vor Aufruf
Automatisches Inlining: PLSQL_OPTIMIZE_LEVEL = 3
Quellcode bliebt unverändert
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 19
NOCOPY
Compiler Hint, kann bei OUT und IN OUT Parametern
angegeben werden:
PROCEDURE p_nocopy
(p_array IN OUT NOCOPY MY_PACK.t_array)
Bewirkt Übergabe BY REFERENCE statt BY VALUE
Kein Umkopieren, sondern Übergabe der Speicheradresse
Kann bei PLSQL_OPTIMIZE_LEVEL > 1 auch automatisch
erfolgen
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 20
SQL in PL/SQL
Hauptproblem bei echten Performance-Engpässen!
 SQL Tuning nötig
Benutzen Sie Bind-Variablen!
Lagern Sie SQL-Befehle in eigene Module aus und
vermeiden Sie Duplizierungen!
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 21
Vermischtes
Testen Sie unter realistischen Bedingungen
Achten Sie auf Randbedingungen (verfügbare
Ressourcen, parallele Sessions vs. Einzelsession, ...)
Halten Sie Sperren möglichst kurz
Modularisieren Sie und achten Sie auf saubere
Programmabläufe
Stellen Sie bei bedingten Anweisungen aufwendige
Überprüfungen ans Ende
Pipelined Functions können die Antwortzeit
beschleunigen
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 22
Performance-Messung
Einfachste Möglichkeit (in Testphase): Zeitmessung
DBMS_UTILITY.get_time
DBMS_UTILITY.get_cpu_time
v_begin := DBMS_UTILITY.get_cpu_time;
p_do_proc1;
v_end := DBMS_UTILITY.get_cpu_time;
DBMS_OUTPUT.put_line ('Zeit Methode 1: '
||(v_end - v_begin));
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 23
Performance-Messung
DBMS_PROFILER:
Welche Programme wurden aufgerufen?
Welche Zeilen wurden wie oft abgearbeitet?
Wie lange dauerte die Abarbeitung einer Zeile gesamt,
minimal, maximal?
Wie lange dauerte der gesamte Lauf?
Output landet in (vorher anzulegenden) Tabellen
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 24
Performance-Messung
3 Funktionen:
v_start := DBMS_PROFILER.START_PROFILER;
v_flush := DBMS_PROFILER.FLUSH_DATA;
v_stop
MuniQSoft GmbH
:= DBMS_PROFILER.STOP_PROFILER;
DOAG 2009 PL/SQL Performance-Tuning
Seite 25
Performance-Messung 11g
DBMS_HPROF:
Hierarchische Beziehungen (Parents, Children)
Anzahl der Aufrufe
Dauer (Function bzw. Subtree)
getrennte Namespaces für SQL und PL/SQL
Directory-Objekt nötig, Output landet in Datei:
DBMS_HPROF.START_PROFILING('MYDIR', 'test.trc');
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 26
Performance-Messung 11g
Daraus können (vorher anzulegende) Tabellen gefüllt werden:
v_id := DBMS_HPROF.ANALYZE (LOCATION=>'MYDIR',
FILENAME=>'test.trc',
RUN_COMMENT=>'TEST');
Erzeugung von HTML-Reports durch plshprof (11.1.0.7):
plshprof -output …\tracetest …\test.trc
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 27
Danke für Ihre Aufmerksamkeit...
?
MuniQSoft GmbH
DOAG 2009 PL/SQL Performance-Tuning
Seite 28