PL/SQL-Performance unter Oracle 10g

Entwicklung PL/SQL
PL/SQL-Performance unter Oracle 10g
Autor: Dustin Schmitt, Ordix AG
Bei Oracle 10g gab es einige Neuerungen im Bereich PL/SQL. Diese werden hier hinsichtlich der
Performance von PL/SQL-Programmen vorgestellt. Der Artikel richtet sich an Oracle Datenbank-Administratoren und -Entwickler, die in
10g PL/SQL-Skripte einsetzen.
Der Code-Generator in Oracle 10g ist vollständig erneuert.
Der generierte Code ist nun deutlich effizienter und nach
Oracles Aussagen sogar bis zu zweimal so schnell wie noch
in Oracle 9i. Neben dem neuen Code-Generator wurden
im Release 10g auch weitere Features eingeführt, die detailliert im Whitepaper "PL/SQL Just Got Faster" [1] festgehalten sind. Dazu zählen eine überarbeitete PL/SQL
Virtual Machine (PVM) sowie die Automatic-PerformanceDiagnostic- und Tuning-Features. Nachfolgend sind einige
Beispiele für Verbesserungen aufgeführt.
Der neue Code-Generator
In früheren Oracle-Versionen wurde PL/SQL-Code eins zu
eins übersetzt, ohne viele Änderungen vorzunehmen. Der
Code-Generator war sehr mechanisch. Es galt das Motto:
"What you write is what you get". Wurde im Quellcode
der Weg von A nach B über einen Umweg C gewählt, so
wurde dieser Umweg während der Ausführung auch
konsequent ausgeführt.
Nun wird ein neuer Code-Generator genutzt, der den
Code zur Erzielung einer besseren Performance gegebenenfalls komplett umgestaltet – und das ohne jeglichen
Eingriff von außen. Bei demselben Quellcode, der von A
nach B über den Umweg C führt, weiß der neue Code-Generator in 10g nun, dass es auch einen direkten Weg von A
nach B gibt, und führt diesen auch aus.
Nun gilt: "What you write is NOT what you get"! Dies
führt zum Beispiel bei rekursiven Schleifen, wie in Beispiel
A zu sehen (siehe Abbildung 1), zu einer enormen Verbesserung der Performance. Viele altbekannte Regeln der
PL/SQL-Programmierung sind nun hinfällig, weil sie durch
den neuen Code-Generator in den zur Ausführung "günstigsten" Code umgewandelt werden.
Neuer Initialisierungsparameter
Mit dem neuen Code-Generator wurde auch ein neuer
Initialisierungsparameter PLSQL_OPTIMIZE_LEVEL eingeführt. Dieser beeinflusst die Code-Optimierung und kann
mit den Werten 1 und 2 belegt werden, wobei 2 der Standardwert ist. Dieser Standardwert ist normalerweise auch
der bessere. Er liefert die optimale Code-Generierung,
während beim Wert 1 die Code-Generierung nur geringfügiger optimiert wird, dadurch aber eine kürzere Kompilierungszeit hat.
Dauert die Kompilierung bei sehr großen Anwendungen zu lange, sollte der Wert auf 1 gesetzt werden. In sehr
wenigen Fällen ist zwischen den beiden Parametern auch
eine unterschiedliche Behandlung der Exceptions festzustellen. Eventuell werden Exceptions gar nicht ausgelöst oder zu einem früheren Zeitpunkt als erwartet.
Ist der Initialisierungsparameter PLSQL_OPTIMIZE_
LEVEL auf 0 gesetzt, so wird das Optimieren des Codes
komplett ausgeschaltet. Dies wird natürlich nicht empfohlen.
PL/SQL Virtual Maschine
Die PL/SQL Virtual Maschine (PVM) ist wie in allen anderen Programmiersprachen (z. B. Java) notwendig, um den
vom Code-Generator erstellten Code auszuführen. Die
PVM wurde zwar nicht wie der Code-Generator komplett
erneuert, aber entscheidend verbessert. So sind Konkatenationen (Verkettungen) nicht mehr wie in früheren
Releases nacheinander verarbeitet und temporär zwischengespeichert, sondern können in einer einmaligen
Verkettung ausgeführt werden.
Die meisten Änderungen wurden an den Befehlen der
PVM vorgenommen, beispielsweise an der Speicheradressierung. Außerdem konnte durch eine bessere Programmierung der PVM selbst die Performance während
der Ausführung entscheidend gesteigert werden. Da sich
die PVM wie eine "Black-Box" verhält, sind diese Neuerungen vom Entwickler nicht beeinflussbar. Sie sind für
ihn aber trotzdem sehr hilfreich. Folgende Beispiele sollen die Performance-Steigerung in Oracle 10g deutlich
machen.
CREATE function Fib_REKURSION ( n in pls_integer ) return pls_integer
-- Rekursive Lösung
is
begin
if n = 1
then
return 1;
elsif n = 2
then
return 1;
else
50
News Q4-2006
www.doag.org
PL/SQL
Entwicklung
return Fib_REKURSION ( n – 1 ) + Fib_REKURSION ( n – 2 );
end if;
end Fib_REKURSION;
/
CREATE function Fib_ITERATION ( n in pls_integer ) return pls_integer
-- Iterative Lösung
is
current pls_integer;
last_but_one pls_integer;
last pls_integer;
begin
if n = 1
then
return 1;
elsif n = 2
then
return 1;
else
last_but_one := 1;
last := 1;
for j in 3..n loop
current := last_but_one + last;
last_but_one := last;
last := current;
end loop;
return current;
end if;
end Fib_ITERATION;
/
-- Rekursion
declare
time1 number; time2 number; erg number; anz number;
begin
anz:=30;
time1:=dbms_utility.get_time;
select fib_REKURSION(anz) into erg from dual;
dbms_output.put_line('Ergebnis: '||erg);
time2:=dbms_utility.get_time;
dbms_output.put_line('Dauer Rekursion: '||(time2-time1));
end;
/
-- Iteration
declare
time1 number; time2 number; erg number; anz number;
begin
anz:=30;
time1:=dbms_utility.get_time;
select fib_ ITERATION(anz) into erg from dual;
dbms_output.put_line('Ergebnis: '||erg);
time2:=dbms_utility.get_time;
dbms_output.put_line('Dauer Iteration: '||(time2-time1));
end;
/
Abb.1: Beispielcode zum Performance-Vergleich zwischen Rekursion und Iteration
www.doag.org
News Q4-2006
51
Entwicklung PL/SQL
Rekursion versus Iteration
Jeder PL/SQL-Entwickler weiß, dass eine Rekursion immer
langsamer ist als eine gleiche iterative Lösung. Diese Aussage trifft zwar auch in Oracle 10g zu, aber in unseren Tests
konnten rekursive Lösungen gegenüber Oracle 9i deutlich
aufholen. Abbildung 1 zeigt ein einfaches Beispiel zum Vergleich.
Wie aus den Abbildungen 2 a und 2b zu erkennen ist, sind
rekursive Lösungen in Oracle 10g deutlich leistungsstärker als
in früheren Versionen. Im Vergleich zu Oracle 9i ist eine bis
zu rund 25 Prozent gesteigerte Performance festzustellen.
Dennoch konnte die Rekursion die Performance der Iteration
nicht einholen.
Expression versus Faktor
Abbildungen 2a und 2b: Das Ergebnis des Performance-Vergleichs
Ein Grundsatz für jeden PL/SQL-Entwickler ist, dass
Rechen-Ausdrücke nicht innerhalb einer Schleife zu verwenden sind. Dies führte in Oracle 9i zu deutlichen Performance-Einbußen, da die Rechenoperation in jedem
Schleifendurchlauf erneut auszuführen war. Dies gilt so
nicht mehr in Oracle 10g. Hier sind sogar fast gleichwertige Zeiten festzustellen. Abbildung 3 zeigt dazu ein einfaches Beispiel.
Die Abbildungen 4a und 4b zeigen den deutlichen Performance-Unterschied zwischen Expression und Faktor. Im
Fall von Expression in 10g ergibt sich sogar eine Performance-Verbesserung von bis zu 75 Prozent gegenüber der
Version 9i.
-- Expression in Schleife
declare
time1 number; time2 number; erg number; n_of_repeats number;
a number; b number; c number; d number; e number;
factor number; checksum number;
begin
time1:=dbms_utility.get_time;
n_of_repeats:=100000;
a:=10; b:=20; c:=4; d:=1; e:=500;
checksum:=0;
for r in 1..n_of_repeats loop
checksum := checksum
+ ((a + b) / ( c + d)) * e;
end loop Naive;
dbms_output.put_line('Ergebnis: '||checksum);
time2:=dbms_utility.get_time;
dbms_output.put_line('Dauer Expression: '||(time2-time1));
end;
/
-- Faktor in Schleife
declare
time1 number; time2 number; erg number; n_of_repeats number;
a number; b number; c number; d number; e number;
factor number; checksum number;
begin
time1:=dbms_utility.get_time;
n_of_repeats:= 100000;
52
News Q4-2006
www.doag.org
PL/SQL
Entwicklung
a:=10; b:=20; c:=4; d:=1; e:=500;
checksum:=0;
factor := ((a + b) / ( c + d)) * e;
for r in 1..n_of_repeats loop
checksum := checksum
+ factor;
end loop Hand_Optimized;
dbms_output.put_line('Ergebnis: '||checksum);
time2:=dbms_utility.get_time;
dbms_output.put_line('Dauer Faktor: '||(time2-time1));
end;
/
Abbildung 3: Beispielcode zum Performance-Vergleich zwischen Expression und Faktor
Abbildungen 4a und 4b: Das Ergebnis des Performance-Vergleichs zwischen Expression und Fakto
Konstanten in PL/SQL
PL/SQL Performance Measurement Harness
Als letzte Einflussgröße sind Konstanten genannt. Diese
hatten bisher keinen Einfluss auf die Performance einer
PL/SQL-Anwendung. Dies lässt sich nun für 10g mit dem
Beispiel aus Abbildung 5 widerlegen.
Das Ergebnis aus den Abbildungen 6a und 6b belegt,
dass die vorzugsweise Benutzung von Konstanten gegenüber einer Verwendung von Variablen geringfügig die Performance verbessert.
Es gibt unzählige weitere Beispiele, die eine Performance-Steigerung in Oracle 10g verdeutlichen. Jeder kann
das einmal selbst mit seinen Skripten ausprobieren.
Mit dem Harness Software Kit stellt Oracle dem Entwickler ein Tool zur Verfügung, mit dem sich die Performance-Unterschiede eines PL/SQL-Programms messen
lassen. Das Tool steht zum freien Download zur Verfügung und kann mit den circa 30 vorhandenen TestProgrammen oder mit eigenen PL/SQL-Anwendungen
genutzt werden.
Durch eine Vielzahl vorgefertigter Reports lassen sich so
zum Beispiel HTML-Dokumente oder sogar Microsoft-ExcelSheets erstellen. Das Tool dient nicht nur der PerformanceMessung von PL/SQL-Anwendungen auf Datenbanken der
-- Konstanten
declare
time1 number; time2 number; erg number; n_of_repeats number;
CONS_1 CONSTANT number := 100;
checksum number;
begin
time1:=dbms_utility.get_time;
n_of_repeats:=10000000;
checksum:=1;
for r in 1..n_of_repeats loop
checksum := checksum
+ cons_1;
www.doag.org
News Q4-2006
53
Entwicklung PL/SQL
end loop Konstante;
dbms_output.put_line('Ergebnis: '||checksum);
time2:=dbms_utility.get_time;
dbms_output.put_line('Dauer Konstante: '||(time2-time1));
end;
/
-- Variablen
declare
time1 number; time2 number; erg number; n_of_repeats number;
VAR_1 number := 100;
checksum number;
begin
time1:=dbms_utility.get_time;
n_of_repeats:=10000000;
checksum:=1;
for r in 1..n_of_repeats loop
checksum := checksum
+ VAR_1;
end loop Variable;
dbms_output.put_line('Ergebnis: '||checksum);
time2:=dbms_utility.get_time;
dbms_output.put_line('Dauer Variable: '||(time2-time1));
end;
/
Abbildung 5: Beispielcode zum Performance-Vergleich zwischen der Nutzung von Konstanten und Variablen
Versionen 9i und 10g, sondern es kann bereits ab der
Version 8.0 benutzt werden. Eine ausführliche Anleitung
sowie das komplette Software-Kit findet man unter [1]
zum Download bereitgestellt.
Fazit
In Oracle 10g wird dem Entwickler die Arbeit vereinfacht. Er braucht sich weniger Gedanken um die Performance-Optimierung, um schnelle Kompilierungszeiten
oder gar um das Sammeln von Statistiken zu machen.
Dies erledigt der Code-Generator nun größtenteils von
selbst. Und das, wie gezeigt wurde, sogar sehr schnell
und zuverlässig. Auch wenn wir die von Oracle angepriesenen 50 Prozent in unseren Testfällen nicht durchweg bestätigen konnten, ist dennoch eine deutliche
Performance-Verbesserung feststellbar. Wer dennoch
den Code von Hand optimieren möchte und gar keine
Optimierung seitens des Code-Generators wünscht, ist
aber auch weiterhin mit Oracle PL/SQL sehr gut bedient.
So erreichen Sie die DOAG:
Alle eMail-Adressen finden Sie unter
www.doag.org/public/doag/adressen
Abbildung 6a und 6b: Das Ergebnis des Performance-Vergleichs
zwischen der Nutzung von Konstanten und Variablen in PL/SQL
54
News Q4-2006
www.doag.org
PL/SQL
Entwicklung
Man sollte sich die beiden folgenden Aspekte bewusst machen: Ab der Version 10g gilt "Just write it the
way that feels most natural" und "What you write is NOT
what you get".
Links
[1] Auf der Seite http://www.oracle.com/technology/
tech/pl_sql/htdocs/New_In_10gR1.htm stehen alle Neuerungen zu PL/SQL in Oracle 10g sowie das eingangs
erwähnte Whitepaper "PL/SQL Just Got Faster".
[2] Unter http://www.oracle.com/technology/tech/pl_
sql/index.html stehen allgemeine Infos zu PL/SQL.
Kontakt:
Dustin Schmitt
[email protected]
Unsere Kunden brauchen uns ...
... und wir brauchen Sie!
Die ORDIX AG ist ein unabhängiges IT-Beratungsunternehmen.
8QVHU /HLVWXQJVVSHNWUXP XPIDVVW KRFKTXDOL¿]LHUWH %HUDWXQJ
(QWZLFNOXQJ6HUYLFH6FKXOXQJXQG3URMHNWPDQDJHPHQWLP8P
IHOGYRQ8QL[6\VWHPHQ:LQGRZV6HUYHUQXQG'DWHQEDQNHQ
8QVHUH.XQGHQJHK|UHQ]XGHQ7RS$GUHVVHQLQ'HXWVFKODQG
Zur Verstärkung unseres Teams in Köln und Wiesbaden
VXFKHQZLU]XPIUKHVWP|JOLFKHQ=HLWSXQNW
Datenbankberater/-entwickler
ORACLE-Newsticker
In seiner aktuellen Studie mit dem Titel
"Forrester Wave POS Software, Q3 2006,
September 2006" hat das bekannte Marktforschungsunternehmen Forrester Research
Oracle Retail als führenden Anbieter von Pointof-Sale-Lösungen bezeichnet. Oracle Retail
erhielt speziell bei den Anforderungen Spitzenwerte, bei denen mehrere Vertriebskanäle
in eine Lösung einzubinden sind. Laut dem
Forrester-Report "ist Oracle Retail Point-ofService die einzige Anwendung, die den Anforderungen des Vertriebs über mehrere
Vertriebskanäle gewachsen ist". Forrester stufte Oracle ebenfalls als den führenden Anbieter
bei den Filiallösungen für den Lebensmittelhandel und den Bereich Convenience
Retailing ein.
Oracle Retail Point-of-Service unterstützt
Handelsunternehmen dabei, ihren Kunden ein
klares, durchgängiges und hochwertiges Einkaufskonzept anzubieten – sei es im Geschäft,
online oder per Katalog. Eines der besonderen
Merkmale der Lösung ist die frühe Standardisierung der Java-Plattform, die umfangreichen
Verkaufsfunktionen sowie die hervorragende
Unterstützung der Prozesse im Filial BackOffice.
www.doag.org
Ihre Aufgaben:
• 5HDOLVLHUXQJ0LWDUEHLWLQ(QWZLFNOXQJVSURMHNWHQ
• 3ODQXQJXQG.RQ]HSWLRQYRQ,73URMHNWHQ
• 7HFKQLVFKH.XQGHQEHUDWXQJ
• 4XDOLWlWVVLFKHUXQJ7XQLQJPD‰QDKPHQ
• 9RUEHUHLWXQJXQG'XUFKIKUXQJYRQ6FKXOXQJHQ
,KU3UR¿O
• 6HKUJXWH2UDFOH.HQQWQLVVH8QL[:LQGRZV
• 0LQGHVWHQV-DKUH%HUXIVHUIDKUXQJLP2UDFOH8PIHOG
• 6HKUJXWHNRPPXQLNDWLYH)lKLJNHLWHQ
• -((*UXQGNHQQWQLVVH
• 8QHLQJHVFKUlQNWH5HLVHEHUHLWVFKDIWGHXWVFKODQGZHLW
Wir bieten Ihnen:
$WWUDNWLYH /HLVWXQJHQ SHUV|QOLFKHQ )UHLUDXP JXWH$XV XQG
:HLWHUELOGXQJVRZLHHLQVHKUJXWHV%HWULHEVNOLPD,QWHUHVVLHUW"
'DQQELWWHQZLU6LHXP=XVHQGXQJDXVVDJHNUlIWLJHU8QWHUODJHQ
XQWHU$QJDEH,KUHU*HKDOWVYRUVWHOOXQJHQXQGGHPJHZQVFK
WHQ(LQVWHOOXQJVWHUPLQDQIROJHQGH$GUHVVH
ORDIX AG
&KULVWRSK/DIHOG:HVWHUQPDXHU3DGHUERUQ
7HO(0DLOSHUVRQDO#RUGL[GH
,QIRUPLHUHQ6LHVLFKLP,QWHUQHWXQWHUZZZRUGL[GHRGHU
DPLQ0DQQKHLPDQXQVHUHP6WDQG
auf der DOAG Konferenz
News Q4-2006
55