PLSQL_性能优化效能跟踪工具DBMS_PROFILER分析（案例）

 1     DECLARE
 2 
 3         x_to_currecny GL_DAILY_RATES.TO_CURRENCY%TYPE;
 4 
 5         x_from_currency GL_DAILY_RATES.FROM_CURRENCY%TYPE;
 6 
 7         x_conversion_date GL_DAILY_RATES.CONVERSION_DATE%TYPE;
 8 
 9         x_rate NUMBER;
10 
11     BEGIN
12 
13         DBMS_PROFILER.START_PROFILER('BXJ_PLSQL_DBMS_PROFILER');
14 
15         SELECT to_currency, from_currency, conversion_date
16 
17            INTO x_to_currecny, x_from_currency, x_conversion_date
18 
19          FROM gl_daily_rates
20 
21        WHERE ROWNUM <= 1;
22 
23         x_rate := bxj_get_rate.get_rate_1(x_to_currecny,
24 
25         x_from_currency,
26 
27         x_conversion_date);
28 
29         DBMS_OUTPUT.PUT_LINE('x_rate=' || x_rate);
30 
31         DBMS_PROFILER.STOP_PROFILER;
32 
33     END;

 1     select * from PLSQL_PROFILER_RUNS
 2 
 3         where run_comment= 'BXJ_PLSQL_DBMS_PROFILER'
 4 
 5  
 6 
 7     select * from PLSQL_PROFILER_UNITS
 8 
 9         where runid = 15
10 
11  
12 
13     select * from PLSQL_PROFILER_DATA
14 
15         where runid = 15
16 
17         order by line#

 1     SELECT d.line#, s.text,d.total_time, d.total_occur, d.min_time,d.max_time
 2 
 3      FROM plsql_profiler_data d, sys.all_source s, plsql_profiler_units u
 4 
 5     WHERE d.runid = 15
 6 
 7         AND u.unit_name = 'BXJ_GET_RATE'
 8 
 9         AND u.runid = d.runid
10 
11         AND d.unit_number = u.unit_number
12 
13         AND d.total_occur <> 0
14 
15         AND s.TYPE(+) = u.unit_type
16 
17         AND s.owner(+) = u.unit_owner
18 
19         AND s.name(+) = u.unit_name
20 
21         AND d.line# = NVL (s.line, d.line#)
22 
23     ORDER BY u.unit_number, d.line#;