Database Reference
In-Depth Information
isn't very reliable. In fact, for CPU-bound processing, the overhead of the profiler may be very high. In this specific
case, which is indeed CPU bound, the processing time increases from less than 1 second to about 7 seconds. Of these
7 seconds, only about 4 seconds are accounted for by the profiler:
SQL> SELECT s.line,
2 round(ratio_to_report(p.total_time) OVER ()*100,1) AS time,
3 total_occur,
4 s.text
5 FROM all_source s,
6 (SELECT u.unit_owner, u.unit_name, u.unit_type,
7 d.line#, d.total_time, d.total_occur
8 FROM plsql_profiler_units u, plsql_profiler_data d
9 WHERE u.runid = 1
10 AND d.runid = u.runid
11 AND d.unit_number = u.unit_number) p
12 WHERE s.owner = p.unit_owner (+)
13 AND s.name = p.unit_name (+)
14 AND s.type = p.unit_type (+)
15 AND s.line = p.line# (+)
16 AND s.owner = user
17 AND s.name = 'PERFECT_TRIANGLES'
18 AND s.type IN ('PROCEDURE', 'PACKAGE BODY', 'TYPE BODY')
19 ORDER BY s.line;
LINE# TIME% EXEC# CODE
------ -------- ---------- ------------------------------------------------------
1 0.0 1 PROCEDURE perfect_triangles(p_max IN INTEGER) IS
...
29 17.7 1,105,793 FOR j IN 1..n
30 LOOP
31 22.3 1,105,614 IF p_long = dup_sides(j).long
32 AND
33 p_short = dup_sides(j).short
34 THEN
35 0.0 855 RETURN FALSE;
36 END IF;
37 END LOOP;
...
44 8.2 501,500 FOR short IN 1..long
45 LOOP
46 21.4 500,500 hyp := sqrt(long*long + short*short);
47 11.0 500,500 ihyp := floor(hyp);
48 10.5 500,500 IF hyp-ihyp < 0.01
49 THEN
50 0.2 10,325 IF ihyp*ihyp = long*long + short*short
51 THEN
52 0.1 1,034 IF sides_are_unique(long, short)
53 THEN
54 0.0 179 m := m+1;
55 0.0 179 unique_sides(m).long := long;
