profiler的脚本放在$ORACLE_HOME/rdbms/admin下面有proftab.sql和profload.sql俩脚本。
首先在系统用户下安装profload.sql脚本,然后在要测试的PL/SQL的用户下安装proftab.sql脚本。
安装好脚本后,就可以使用profiler工具进行PL/SQL的性能测试了。代码如下
declare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER(to_char(sysdate,'dd-Mon-YYYY hh:mi:ss')) ;
这中间执行要测试的PL/SQL程序。
err:=DBMS_PROFILER.STOP_PROFILER;
end;
例如:
declare
2 err number;
3 n1 number;
4 n2 number;
5 n3 number;
6 begin
7 err:=DBMS_PROFILER.START_PROFILER(to_char(sysdate,'dd-Mon-YYYY hh:mi:ss')) ;
8 n1:=&n1;
9 n2:=&n2;
10 n3:=n1/n2;
11 dbms_output.put_line(n3);
12 err:=DBMS_PROFILER.STOP_PROFILER;
13 end;
14 /
Enter value for n1: 8
old 8: n1:=&n1;
new 8: n1:=8;
Enter value for n2: 4
old 9: n2:=&n2;
new 9: n2:=4;
PL/SQL procedure successfully completed.
然后执行下面代码找到runid:
select runid,run_date,RUN_COMMENT from plsql_profiler_runs order by runid
RUNID RUN_DATE RUN_COMMENT
---------- --------- ----------------------------------------
1 11-DEC-12 11-Dec-2012 04:11:02
然后根据runid查看运行结果:
select p.unit_name,p.occured,p.tot_time,p.line# line,substr(s.text,1,75) xt from (select u.unit_name,d.total_occur occured,(d.total_time/10000000000) to t_time,d.line# from plsql_profiler_units u,plsql_profiler_data d where d.runid= u.runid and d.unit_number = u.unit_number and d.total_occur >0 and u.runid =
2 &run_id) p, user_source s where p.unit_name = s.name(+) and p.line# = s.li ne(+) order by p.unit_name ,p.line#;
Enter value for run_id: 1
old 2: &run_id) p, user_source s where p.unit_name = s.name(+) and p.line# = s.line(+) order by p.unit_name ,p.line#
new 2: 1) p, user_source s where p.unit_name = s.name(+) and p.line# = s.line (+) order by p.unit_name ,p.line#
UNIT_NAME OCCURED TOT_TIME LINE
--------------- ------- ----------- ----------
TEXT
------------------------------------------------------------------------------- -
1 .000005 11
1 .000000 12
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27668565/viewspace-750915/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27668565/viewspace-750915/