使用DBMS_HPROF进行PL/SQL代码探查

--使用DBMS_HPROF进行PL/SQL代码探查


通过包dbms_hprof进行管理的调用级别探查器（也称为分层探查器，hirearchical profiler）。


优势：
1.开启后对开销影响非常小。
2.提供调用级别的信息。
3.有self time和total time的概念。
4.并不需要附加的权限。

5.支持native-compiled PL/SQL。

--实验

SYS@PROD1> CREATE OR REPLACE PROCEDURE perfect_triangles(p_max IN INTEGER) IS
  2    hyp NUMBER;
  3    ihyp INTEGER;
  4    TYPE side_r IS RECORD(short INTEGER, long INTEGER);
  5    TYPE sides_t IS TABLE OF side_r INDEX BY BINARY_INTEGER;
  6    unique_sides sides_t;
  7    dup_sides sides_t;
  8    n integer :=0;
  9    m integer :=0;
 10  
 11    PROCEDURE store_dup_sides(p_long IN INTEGER, p_short IN INTEGER) IS
 12  	 mult INTEGER := 2;
 13  	 long_mult INTEGER := p_long*2;
 14  	 short_mult INTEGER := p_short*2;
 15    BEGIN
 16  	 WHILE long_mult < p_max OR short_mult < p_max
 17  	 LOOP
 18  	   n := n+1;
 19  	   dup_sides(n).long := long_mult;
 20  	   dup_sides(n).short := short_mult;
 21  	   mult := mult+1;
 22  	   long_mult := p_long*mult;
 23  	   short_mult := p_short*mult;
 24  	 END LOOP;
 25    END store_dup_sides;
 26  
 27    FUNCTION sides_are_unique(p_long IN INTEGER, p_short IN INTEGER) RETURN BOOLEAN IS
 28    BEGIN
 29  	 FOR j IN 1..n
 30  	 LOOP
 31  	   IF p_long = dup_sides(j).long
 32  	      AND
 33  	      p_short = dup_sides(j).short
 34  	   THEN
 35  	     RETURN FALSE;
 36  	   END IF;
 37  	 END LOOP;
 38  	 RETURN TRUE;
 39    END sides_are_unique;
 40  
 41  BEGIN
 42    FOR long IN 1..p_max
 43    LOOP
 44  	 FOR short IN 1..long
 45  	 LOOP
 46  	   hyp := sqrt(long*long + short*short);
 47  	   ihyp := floor(hyp);
 48  	   IF hyp-ihyp < 0.01
 49  	   THEN
 50  	     IF ihyp*ihyp = long*long + short*short
 51  	     THEN
 52  	       IF sides_are_unique(long, short)
 53  	       THEN
 54  		 m := m+1;
 55  		 unique_sides(m).long := long;
 56  		 unique_sides(m).short := short;
 57  		 store_dup_sides(long, short);
 58  	       END IF;
 59  	     END IF;
 60  	   END IF;
 61  	 END LOOP;
 62    END LOOP;
 63    FOR j IN 1..m
 64    LOOP
 65  	 dbms_output.put_line('.' ||
 66  			      lpad(unique_sides(j).long, 4,' ')||
 67  			      lpad(unique_sides(j).short,4,' '));
 68    END LOOP;
 69  END perfect_triangles;
 70  /
SYS@PROD1> 
SYS@PROD1> CREATE OR REPLACE PACKAGE perfect_triangles_pck IS
  2    PROCEDURE run(p_max IN INTEGER);
  3  END perfect_triangles_pck;
  4  /
SYS@PROD1> 
SYS@PROD1> CREATE OR REPLACE PACKAGE BODY perfect_triangles_pck IS
  2    PROCEDURE run(p_max IN INTEGER) IS
  3    BEGIN
  4  	 perfect_triangles(p_max);
  5    END run;
  6  END perfect_triangles_pck;
  7  /
SYS@PROD1> 
SYS@PROD1> CREATE OR REPLACE TYPE perfect_triangles_typ AS OBJECT (
  2    dummy NUMBER,
  3    STATIC PROCEDURE run(p_max IN INTEGER)
  4  );
  5  /
SYS@PROD1> 
SYS@PROD1> CREATE OR REPLACE TYPE BODY perfect_triangles_typ IS
  2    STATIC PROCEDURE run(p_max IN INTEGER) IS
  3    BEGIN
  4  	 perfect_triangles(p_max);
  5    END run;
  6  END;
  7  /
SYS@PROD1> 
SYS@PROD1> 
SYS@PROD1> 
SYS@PROD1> CREATE DIRECTORY plshprof_dir AS '&directory_path';
Enter value for directory_path: /home/oracle/ext/
SYS@PROD1> 
SYS@PROD1> PAUSE

SYS@PROD1> 
SYS@PROD1> REM
SYS@PROD1> REM Install the profiler tables
SYS@PROD1> REM
SYS@PROD1> 
SYS@PROD1> @?/rdbms/admin/dbmshptab.sql
SYS@PROD1> Rem
SYS@PROD1> Rem $Header: dbmshptab.sql 30-jul-2007.13:07:41 sylin Exp $
SYS@PROD1> Rem
SYS@PROD1> Rem dbmshptab.sql
SYS@PROD1> Rem
SYS@P