--使用DBMS_HPROF进行PL/SQL代码探查
通过包dbms_hprof进行管理的调用级别探查器(也称为分层探查器,hirearchical profiler)。
优势:
1.开启后对开销影响非常小。
2.提供调用级别的信息。
3.有self time和total time的概念。
4.并不需要附加的权限。
通过包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