Exceptionfault
Erfahrenes Mitglied
Auf Grund des folgenden Problems http://www.tutorials.de/forum/relat...kopieren-grosser-datenmengen.html#post1339201 habe ich mir erlaubt wieder ein kleines Tutorial für Oracle Entwickler zu schreiben. Diesmal das Thema
"PL/SQL Profiler", oder
"Was ist an meiner Prozedur so langsam?"
Zunächst einmal, was ist der PL/SQL Profiler und was kann ich grob mit ihm machen ?
Der Profiler ist eine PL/SQL Package, dass die Ausführung von anderem PL/SQL Code beobachtet und Ausführungszeit und Häufigkeit protokolliert. Er dient dazu langsame Codestücke zu finden, oder Code zu identifizieren der einfach unnötig oder zu oft ausgeführt wird. Ein Profiler ist nicht mit einem Debugger zu verwechseln, denn ich kann weder Schritt für Schritt den Ablauf verfolgen, noch kann ich Haltepunkte oder ähnliches definieren. Das macht aber auch die Anwendung eines Profilers sehr einfach:
Der Profiler ist wie gesagt keine eigene Applikation sondern ein PL/SQL Package in der Datenbank. Wer testen möchte ob es installiert ist kann dies einfach mit einem "DESC DBMS_PROFILER" feststellen. Eine Fehlermeldung könnte aber auch bedeuten, dass ihr nicht die Rechte auf das Package habt, also mal mit einem DBA User probieren.
Als kleine Vorbereitung habe ich im folgenden mal 2 Testtabellen aufgebaut.
Die eine der beiden Tabellen füllen wir mit 1.000.000 Datensätze.
Ziel soll sein, die 1.000.000 Datensätze per PL/SQL Prozedur in die andere Tabelle zu kopieren. Spontan sind mir 3 mehr oder weniger gute Methoden eingefallen, die ich in 3 eigenen Prozeduren realisiert habe:
Bevor wir den Profiler nutzen können müssen wir die Tabellen und Sequences anlegen, die der Profiler selbst benötigt um Informationen abzulegen. Oracle liefert hierfür ein Script mit namens "proftab.sql". Einfach mit "@/path_to_proftab/proftab.sql" ausführen, und zwar als der User, der später auch den zu testenden PL/SQL Code ausführen soll.
Als nächstes habe ich mir ein kleines Testprogramm geschrieben. Es ruft der Reihe nach alle 3 Prozeduren auf, startet vorher einen neuen Profiler Lauf und beendet ihn danach wieder. Zwischen den einzelnen läufen wird auch die Zieltabelle wieder geleert.
Das Ganze dauert nun ein paar Minten, immerhin werden 3 Mio. Sätze erzeugt und wieder gelöscht.
Nun zu Punkt 4, der Auswertung: Alle Daten die der Profiler gesammelt hat stehen in den zuvor angelegten Tabellen, z.B. PLSQL_PROFILER_RUNS:
Leider kann man mit den übrigen Daten erstmal nur wenig anfangen. Dafür gibt es aber auch ein Script von Oracle welches eine ziemlich informative HTML Datei erstellt. Einfach aufrufen mit "@/pfad_zur_datei/profiler.sql <runid>". Die HTML Datei wird im aktuellen Verzeichnis abgelegt, aus dem SQL*Plus gestartet wurde und heisst "profiler_<runid>.html"
In diesem HTML File seht ihr nun jede einzelne Zeile der PL/SQL Prozedur, wie oft diese ausgeführt wurden (LOOP Statements) und wie lange sie gedauert haben.
p.s. Die Ausgabe als HTML Tabelle ist ein wenig besser zu erkennen ;-)
Happy profiling!
"PL/SQL Profiler", oder
"Was ist an meiner Prozedur so langsam?"
Zunächst einmal, was ist der PL/SQL Profiler und was kann ich grob mit ihm machen ?
Der Profiler ist eine PL/SQL Package, dass die Ausführung von anderem PL/SQL Code beobachtet und Ausführungszeit und Häufigkeit protokolliert. Er dient dazu langsame Codestücke zu finden, oder Code zu identifizieren der einfach unnötig oder zu oft ausgeführt wird. Ein Profiler ist nicht mit einem Debugger zu verwechseln, denn ich kann weder Schritt für Schritt den Ablauf verfolgen, noch kann ich Haltepunkte oder ähnliches definieren. Das macht aber auch die Anwendung eines Profilers sehr einfach:
- Profiler starten
- Code ausführen der analysiert werden soll
- Profiler stoppen
- Auswertung interpretieren
Der Profiler ist wie gesagt keine eigene Applikation sondern ein PL/SQL Package in der Datenbank. Wer testen möchte ob es installiert ist kann dies einfach mit einem "DESC DBMS_PROFILER" feststellen. Eine Fehlermeldung könnte aber auch bedeuten, dass ihr nicht die Rechte auf das Package habt, also mal mit einem DBA User probieren.
Als kleine Vorbereitung habe ich im folgenden mal 2 Testtabellen aufgebaut.
SQL:
create table mydata
(
id NUMBER(10,0) NOT NULL,
text VARCHAR2(1000) NOT NULL,
datum DATE NOT NULL
)
/
create table my_copy_data
(
id NUMBER(10,0) NOT NULL,
text VARCHAR2(1000) NOT NULL,
datum DATE NOT NULL
)
/
SQL:
begin
for i in 1 .. 1000000 loop
INSERT INTO mydata VALUES( i, 'abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', sysdate );
end loop;
end;
/
COMMIT
/
SQL:
create or replace procedure TESTLAUF_1
is
myrow mydata%ROWTYPE;
begin
FOR myrow IN ( SELECT * FROM MYDATA ) LOOP
INSERT INTO my_copy_data VALUES( myrow.id, myrow.text, myrow.datum );
END LOOP;
end;
/
create or replace procedure TESTLAUF_2
is
type myrowtable is table of mydata%ROWTYPE;
myrow myrowtable;
begin
SELECT *
BULK COLLECT into myrow
FROM mydata;
FOR i in myrow.FIRST .. myrow.LAST LOOP
INSERT /*+ APPEND */ INTO my_copy_data VALUES( myrow(i).id, myrow(i).text, myrow(i).datum );
END LOOP;
end;
/
create or replace procedure TESTLAUF_3
is
begin
INSERT INTO my_copy_data
SELECT * FROM mydata;
end;
/
Bevor wir den Profiler nutzen können müssen wir die Tabellen und Sequences anlegen, die der Profiler selbst benötigt um Informationen abzulegen. Oracle liefert hierfür ein Script mit namens "proftab.sql". Einfach mit "@/path_to_proftab/proftab.sql" ausführen, und zwar als der User, der später auch den zu testenden PL/SQL Code ausführen soll.
Als nächstes habe ich mir ein kleines Testprogramm geschrieben. Es ruft der Reihe nach alle 3 Prozeduren auf, startet vorher einen neuen Profiler Lauf und beendet ihn danach wieder. Zwischen den einzelnen läufen wird auch die Zieltabelle wieder geleert.
SQL:
declare
procedure CLEAN_UP
is
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE my_copy_data';
end;
begin
CLEAN_UP();
-- run 1 ------------------------------------------------------------
dbms_profiler.start_profiler( 'Testlauf 1', 'Ohne BULK COLLECT' );
TESTLAUF_1();
dbms_profiler.stop_profiler();
CLEAN_UP();
-- run 2 ------------------------------------------------------------
dbms_profiler.start_profiler( 'Testlauf 2', 'Mit BULK COLLECT' );
TESTLAUF_2();
dbms_profiler.stop_profiler();
CLEAN_UP();
-- run 3 ------------------------------------------------------------
dbms_profiler.start_profiler( 'Testlauf 3', 'Mit DIRECT PATH INSERTS' );
TESTLAUF_3();
dbms_profiler.stop_profiler();
CLEAN_UP();
end;
/
Das Ganze dauert nun ein paar Minten, immerhin werden 3 Mio. Sätze erzeugt und wieder gelöscht.
Nun zu Punkt 4, der Auswertung: Alle Daten die der Profiler gesammelt hat stehen in den zuvor angelegten Tabellen, z.B. PLSQL_PROFILER_RUNS:
SQL:
SQL> select runid, run_date, run_comment, run_comment1 from plsql_profiler_runs order by runid;
RUNID RUN_DATE RUN_COMMENT RUN_COMMENT1
---------- -------- -------------------- ------------------------------
8 24.10.06 Testlauf 1 Ohne BULK COLLECT
9 24.10.06 Testlauf 2 Mit BULK COLLECT
10 24.10.06 Testlauf 3 Mit DIRECT PATH INSERTS
In diesem HTML File seht ihr nun jede einzelne Zeile der PL/SQL Prozedur, wie oft diese ausgeführt wurden (LOOP Statements) und wie lange sie gedauert haben.
Code:
Line Total Time1 Times Executed Text
1 0.00 0 procedure TESTLAUF_1
2 is
3 myrow mydata%ROWTYPE;
4 begin
5 T2 14.25 10002 FOR myrow IN ( SELECT * FROM MYDATA ) LOOP
6 T1 114.48 1000000 INSERT INTO my_copy_data VALUES( myrow.id, myrow.text, myrow.datum );
7 END LOOP;
8 0.00 1 end;
--------------
1 0.00 1 procedure TESTLAUF_2
2 is
3 type myrowtable is table of mydata%ROWTYPE;
4 myrow myrowtable;
5 begin
6 T2 5.00 1 SELECT *
7 BULK COLLECT into myrow
8 FROM mydata;
9 T3 3.56 1000001 FOR i in myrow.FIRST .. myrow.LAST LOOP
10 T1 123.20 1000000 INSERT /*+ APPEND */ INTO my_copy_data VALUES( myrow(i).id, myrow(i).text, myrow(i).datum );
11 END LOOP;
12
13 T6 0.13 1 end;
--------------
1 0.00 0 procedure TESTLAUF_3
2 is
3 begin
4 T1 19.90 1 INSERT INTO my_copy_data
5 SELECT * FROM mydata;
6 0.00 1 end;
p.s. Die Ausgabe als HTML Tabelle ist ein wenig besser zu erkennen ;-)
Happy profiling!
Zuletzt bearbeitet von einem Moderator: