使用PL/SQL PROFILER定位PL/SQL瓶颈代码(二)
---------------------------
1 0 0 procedure binds
2 is
3 v_num number;
4 begin
5 100001 .182 for i in 1..100000 loop
6 100000 .498 v_num := dbms_random.random;
7 100000 3.756 insert into t1 values (v_num,v_num);
8 end loop;
9 1 0 end;
9 rows selected.
Code% coverage
--------------
80
--从上面的报告可知，当改用使用绑定变量后，原来执行insert语句的时间由49.9s下降到3.756s
--对于这个事例仅仅是演示定位瓶颈代码，并改用绑定变量以提高性能，对于其他情形，具体的如何修改瓶颈代码应具体分析
4、示例中用到的脚本
[sql]
a、chk_profile.sql
--file_name: chk_profile.sql
set linesize 190
column text format a100 wrap
column total_time format 99999.9
column min_time format 99999.9
column max_time format 99999.9
select s.text ,
p.total_occur ,
p.total_time/1000000000 total_time,
p.min_time/1000000000 min_time,
p.max_time/1000000000 max_time
from plsql_profiler_data p, user_source s, plsql_profiler_runs r
where p.line# = s.line
and p.runid = r.runid
and r.run_comment = '&input_comment_name'
and s.name =upper('&input_sp_name');
b、call_profiler
--file_name:call_profiler.sql
SET HEAD OFF
SET PAGES 0
SELECT DECODE (DBMS_PROFILER.start_profiler, '0', 'Profiler started', 'Profiler error') FROM DUAL;
-------you can put you plsql code in below block------------
begin
binds;
end;
/
---------------------------------------------------------------
SELECT DECODE (DBMS_PROFILER.stop_profiler, '0', 'Profiler stopped', 'Profiler error') FROM DUAL;
SELECT DECODE (DBMS_PROFILER.flush_data, '0', 'Profiler flushed', 'Profiler error') FROM DUAL;
SELECT 'runid:' || plsql_profiler_runnumber.CURRVAL FROM DUAL;
SET HEAD ON
SET PAGES 200
c、eva luate_profiler_results.sql
--file_name:eva luate_profiler_results.sql
undef runid
undef owner
undef name
set verify off
col text format a60 wrap
SELECT s.line "Line"
, p.total_occur "Occur"
, p.total_time "sec"
, s.text "Text"
FROM all_source s
, (SELECT u.unit_owner
, u.unit_name
, u.unit_type
, d.line#
, d.total_occur
, round(d.total_time / 1000000000,3) total_time
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE u.runid = &&runid AND u.runid = d.runid AND u.unit_number = d.unit_number) p
WHERE s.owner = p.unit_owner(+)
AND s.name = p.unit_name(+)
AND s.TYPE = p.unit_type(+)
AND s.line = p.line#(+)
AND s.name = UPPER ( '&&name' )
AND s.owner = UPPER ( '&&owner' )
ORDER BY s.line;
SELECT exec.cnt / total.cnt * 100 "Code% coverage"
FROM (SELECT COUNT ( 1 ) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = &&runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_name = UPPER ( '&&name' )
AND u.unit_owner = UPPER ( '&&owner' )) total
, (SELECT COUNT ( 1 ) cnt
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = &&runid
AND u.runid = d.runid
AND u.unit_number = d.unit_number
AND u.unit_name = UPPER ( '&&name' )
AND u.unit_owner = UPPER ( '&&owner' )
AND d.total_occur >
0) exec;
undef runid
undef owner
undef name