# [Oracle] Tutorial: PL/SQL Profiler



## Exceptionfault (24. Oktober 2006)

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:


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.

```
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
)
/
```
Die eine der beiden Tabellen füllen wir mit 1.000.000 Datensätze.

```
begin
	for i in 1 .. 1000000 loop
	INSERT INTO mydata VALUES( i, 'abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ', sysdate );
	end loop;
end;
/
COMMIT
/
```
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:

```
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.

```
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> 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
```
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. 


```
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!


----------



## Thomas Darimont (25. Oktober 2006)

Hallo!

Wunderbar  Klasse tutorial! 
Wie wär's denn mal mit einem Tutorial wie man 2 Oracle 10 g Instanzen zu einem (mini) Grid zusammenschaltet? ;-)

Gruß Tom


----------



## Exceptionfault (25. Oktober 2006)

Erstmal danke fürs Lob  

Das (mini) Grid Tutorial ist eine Herausforderung, mal sehen. Da muss ich mir erstmal ein bisschen Platz schaffen. Ich geh mal davon aus, dass RAC on Windows mehr Interesse weckt als RAC on LINUX, oder ? 

Muss mal schauen ob ich das in einer VM Ware hinkriege. Knifflig wirds wohl mit dem Shared Disk Array auf das alle Knoten im Grid Zugriff brauchen. Leider fehlt mir da zu Hause ein bisschen die Hardware ums realistisch aufzubauen, oder hat jemand zufällig noch ein NAS oder SAN zu verschenken ? *g*


----------



## Thomas Darimont (25. Oktober 2006)

Hallo,

ja ein Beispiel zu einem RAC (Real Application Cluster) unter Windows wär schon ganz nett 

Mir ist eben noch eingefallen, dass man ja in solchen tutorials auch noch hier und da mal einen Vergleich zu anderen Datenbanken einstreuen könnte.
Jetzt wissen wir wie man mit einfachen Mitteln PL/SQL Prozeduren unter Oracle Profilen kann. Aber wie profiled man Transact-SQL Proceduren unter MSSQL Server oder PL/SQL unter Postgres? Hmm... mal schauen. Das nur mal so als Anmerkung ;-)

Beim MS SQL Server gibts dazu scheinbar die:
SQL Server Profiler Extended Procedures
http://doc.ddart.net/mssql/sql70/sp_00.htm

Hier noch ne Anleitung zum Profiling von Statements unter MS SQL Server:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp

Zu Postgresql hab ich auf die schnelle mal nix gefunden...

Gruß Tom


----------



## tplanitz (28. Oktober 2006)

Hallo,

auch von mir ein doppelt dickes *Lob*. Das hier eingestellte Thema ist für mich "Gold"  wert. Sobald ich Zeit habe probier ichs mal aus.

Grüße aus Berlin


----------



## Nico Graichen (29. Oktober 2006)

Hi zusammen

Erstmal danke für das Tutorial. Wieder was dazu gelernt 

Ich hab aber mal noch eine Frage:
Gibt es unter Oracle auch so etwas wie den Profiler unter SQL Server, der die Aktivitäten auf der Datenbank mittracet?


----------



## Exceptionfault (29. Oktober 2006)

Hab leider keinen Schimmer vom SQL-Server. Was macht denn der Profiler genau? Welche Infos liefert er denn über die DB?


----------



## Nico Graichen (29. Oktober 2006)

Der Profiler vom SQL Server listet Vorgänge auf, was auf der DB passiert. Also Queries die abgesetzt werden, Inserts die stattfinden, Prozeduren die ausgeführt werden etc. 
Der Profiler ist sozusagen ein Tracer, der alle Vorgänge mitlogt.

Mehr Infos dazu gibt's hier.


----------



## Exceptionfault (29. Oktober 2006)

Oracle bietet zwei Möglichkeiten:

*Fine Grained Auditing*. Ist mehr für Sicherheitsfragen / Überprüfungen gedacht. Zum Tuning überhaupt nicht geeignet. Ich sehe also wenn ein User z.b. ein Systemprivileg nutzt, eine Tabelle Updated o.ä. Oracle kann diese Daten entweder in ein externes File (Flat oder XML) schreiben, sie in das Systemlog (Windows Ereignisliste) oder eine Tabelle protokollieren. Ich sehe aber nicht wie gut oder schlecht die Statements sind.

und

*SQL Tracing*. Zeigt exakt die ausgeführten Statements und die dazugehörigen Ausführungspläne. Kann pro Session oder Datenbankweit aktiviert werden. Pro Session wird ein Tracefile im UserDUmp Verzeichnis erzeugt welches mit dem Tool TKPROF in ein lesbares Format gewandelt werden kann.
Dieser Artikel http://www.tutorials.de/forum/relat...ing-workshop.html?highlight=tkprof#post913120 schneidet das Thema tkprof und SQL Tracing auch kurz an.


----------



## Nico Graichen (29. Oktober 2006)

Das zweite klingt nach dem was ich suche. Werd mir das bei Gelegenheit mal anschauen.
Danke dir


----------



## Thomas Darimont (29. Oktober 2006)

Hallo!

Also mich interessiert das erste ;-)


> Fine Grained Auditing


Normalerweise arbietet ja eine Datenbankgestützte Anwendung mit einem Datenbank-user über denn dann auf die entsprechenden Tabellen zugegriffen wird. Die Authentifikation wird dann meistens von der Anwendung selbst durchgeführt (D.h. in der Anwendung gibt es dann spezielle Benutzer Accounts, oder es wird ein externer Verzeichnisdienst wie LDAP oder Active Directory bemüht). Welche Möglichkeiten hat man bei Oracle 9 / 10 über Fine Grained Auditing diese applikationsspezifischen Authentifikationsinfomationen (Name des Benutzers innerhalb der Anwendung der gerade eine Datenmanipulation auf der Datenbankebene durchführt) bei Veränderungen am Datenbestand mitzuloggen?
Gruß Tom


----------



## ishino (29. Oktober 2006)

In 10g kann man so ziemlich alles in einen Audit-Trail aufnehmen, was man mit einem Objekt in der Datenbank anstellen kann. DDL, DML, Systemereignisse (Einloggen, ...)...alles halt. In 9i gab es noch einige Einschränkungen (zum Beispiel konnte man keine INSERTS/UPDATES in einen AT aufnehmen ).

Kurze Einführung zum Thema gibts hier:

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/nanda_fga.html


----------

