dbms_profiler用来测试PL/SQL代码非常有用,比如找出哪一段代码比较耗时,也可以用来比较不同算法之间的差异。也得到了一些第三方工具的支持,如PLSQL DEVELOPER。
1、安装
1.1、以sys用户创建dbms_profiler包
SQL> conn /as sysdba
SQL> desc dbms_profiler --先确信dbms_profiler包是否存在,如果不存在则通过下面的方式创建
SQL> @?/rdbms/admin/profload.sql
包主要使用的函数是:
start_profiler --启动 profiler
stop_profiler --停止 profiler
1.2、创建一个用于存放跟踪信息的用户,及其prof表和序列的同义词
SQL> CREATE USER profiler IDENTIFIED BY iamwangnc;
SQL> grant connect,resource to profiler;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;
1.3、用profiler用户创建prof表和序列,并赋权
SQL> conn profiler/iamwangnc
SQL> @?/rdbms/admin/proftab.sql
SQL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_data TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_units TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs TO PUBLIC;
备注:
plsql_profiler_runs --prof运行信息
plsql_profiler_units --prof每个单元信息
plsql_profiler_data --prof每个单元的详细数据
plsql_profiler_runnumber --用来生成prof唯一运行编号的序列
2、应用实例
SQL> conn u_test/iamwangnc
SQL> create table tab_test (a int);
SQL> CREATE OR REPLACE PROCEDURE sp_test
AS
BEGIN
FOR I IN 1 .. 100
LOOP
INSERT INTO tab_test
VALUES (I);
END LOOP;
COMMIT;
END;
/
SQL> set serverout on
SQL> DECLARE
v_run_number integer;
v_temp1 integer;
BEGIN
--启动profiler
sys.DBMS_PROFILER.start_profiler (run_number => v_run_number);
--显示当前跟踪的运行序号(后面查询要用)
DBMS_OUTPUT.put_line ('run_number:' || v_run_number);
--运行要跟踪的PLSQL
sp_test;
--停止profiler
sys.DBMS_PROFILER.stop_profiler;
END;
/
run_number:1
记住输出的运行号。
3、查询结果
SQL> set lines 256 pages 100
获得本次prof的基本运行信息:
SQL> select runid,run_owner,run_date,run_total_time from plsql_profiler_runs;
RUNID RUN_OWNER RUN_DATE RUN_TOTAL_TIME
---------- -------------------------------- ------------------- --------------
1 U_TEST 2008-11-26.17:18:39 54215000
根据运行号和单元名(即测试的存储过程名)获得本次prof的单元信息:
SQL> select unit_number,unit_type,unit_owner,unit_name,unit_timestamp,total_time from plsql_profiler_units where runid = 1 and unit_name = 'SP_TEST';
UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME UNIT_TIMESTAMP TOTAL_TIME
----------- -------------------------------- -------------------------------- -------------------------------- ------------------- ----------
2 PROCEDURE U_TEST SP_TEST 2008-11-26.17:17:56 0
根据运行号和单元号获得该存储过程每行运行的统计信息:
SQL> select runid,unit_number,line#,total_occur,total_time,min_time,max_time from plsql_profiler_data where runid = 1 and unit_number = 2;
RUNID UNIT_NUMBER LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
---------- ----------- ---------- ----------- ---------- ---------- ----------
1 2 1 0 3000 3000 3000
1 2 4 101 179000 1000 3000
1 2 6 100 7084000 17000 1141000
1 2 9 1 336000 336000 336000
1 2 10 1 2000 2000 2000
以上信息都不怎么直观,通过下面的sql更加直观展示:
SQL> col text format a60
SQL> SELECT d.line#, --代码行号
s.text, --源代码
d.total_time, --总共运行时间(单位10000亿分之一秒)
d.total_occur, --总共运行次数
d.min_time, --最小运行时间
d.max_time --最大运行时间
FROM plsql_profiler_data d, sys.all_source s, plsql_profiler_units u
WHERE d.runid = 1 --运行号
and u.unit_name = 'SP_TEST' --单元名,即被测试的存储过程名
AND u.runid = d.runid
AND d.unit_number = u.unit_number
AND d.total_occur <> 0
AND s.TYPE(+) = u.unit_type
AND s.owner(+) = u.unit_owner
AND s.name(+) = u.unit_name
AND d.line# = NVL (s.line, d.line#)
ORDER BY u.unit_number, d.line#;
LINE# TEXT TOTAL_TIME TOTAL_OCCUR MIN_TIME MAX_TIME
---------- ------------------------------------------------------------ ---------- ----------- ---------- ----------
4 FOR I IN 1 .. 100 179000 101 1000 3000
6 INSERT INTO tab_test 7084000 100 17000 1141000
9 COMMIT; 336000 1 336000 336000
10 END; 2000 1 2000 2000
4、使用metalink里提供的profiler.sql脚本生成超文本测试结果信息
profiler.sql文件参见metalink的 Note:243755.1。
知道运行号的情况下:
SQL> @profiler.sql 1
或者不输入运行号:
SQL> @profiler
然后选择并输入运行号。
会在当前目录下生成一份名叫profiler_<runid>.html的超文本文件,很直观,如下:
5、在PLSQL Developer里运用Profiler
以上介绍的是通过手工方法应用Profiler,使用相对比较烦杂,下面将详细介绍在PLSQL DEVELOPER 应用Profiler。
5.1、打开test window
方法一、新建一个test window,在test window中输入你要执行的PLSQL脚本。
方法二、选择要跟踪运行的存储过程,右键快捷菜单选择Test,如果是Package那么先右键快捷菜单里选择View,在打开的Package的存储过程列表里右键快捷菜单选择Test,如下图所示:
5.2、进入调试窗口,在1处单击,打开profiler开关,在2处单击或按F8执行
5.3、执行完成后,切换到profiler选项卡
每列的详细意义如下:
unit --单元名称,即执行的存储过程,包括其调用的过程
line --代码行号
total time --此行执行时间(颜色长度表示本行代码的执行时间与最长代码执行时间的百分比图)
occurrences --此行执行次数
text --对应代码行,对于加密的代码,将不能显示
Average time —平均运行时间
maximum time --最大运行时间
minimum time --最小运行时间(以上三个时间默认不显示,可以通过配置对话框选择显示,参加4.4)
列表中显示的源代码只显示一行,如果要定位则可以在对应的行中打开右键,选择[Go to unit line] ,这样就会直接跳到对应的源代码位置。
Profiler面板的工具栏说明:
a、显示配置对话框
b、刷新
c、删除当前运行号的数据
d、Run 显示当前的系统的所有Profiler列表,缺省为当前的跟踪
e、Unit 显示本次跟踪的单元列表信息(执行时间),缺省为所有单元的执行时间
5.4、Profiler配置对话框
Available Columns --可用列
Selected Columns --选择要查看的列
Time units --时间单位(秒、毫秒、微秒)
Show 0 occurrences --是否显示执行0次的处理语句
Graphical time display --用图形显示处理时间的颜色深度百分比
--End--
1、安装
1.1、以sys用户创建dbms_profiler包
SQL> conn /as sysdba
SQL> desc dbms_profiler --先确信dbms_profiler包是否存在,如果不存在则通过下面的方式创建
SQL> @?/rdbms/admin/profload.sql
包主要使用的函数是:
start_profiler --启动 profiler
stop_profiler --停止 profiler
1.2、创建一个用于存放跟踪信息的用户,及其prof表和序列的同义词
SQL> CREATE USER profiler IDENTIFIED BY iamwangnc;
SQL> grant connect,resource to profiler;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;
1.3、用profiler用户创建prof表和序列,并赋权
SQL> conn profiler/iamwangnc
SQL> @?/rdbms/admin/proftab.sql
SQL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_data TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_units TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs TO PUBLIC;
备注:
plsql_profiler_runs --prof运行信息
plsql_profiler_units --prof每个单元信息
plsql_profiler_data --prof每个单元的详细数据
plsql_profiler_runnumber --用来生成prof唯一运行编号的序列
2、应用实例
SQL> conn u_test/iamwangnc
SQL> create table tab_test (a int);
SQL> CREATE OR REPLACE PROCEDURE sp_test
AS
BEGIN
FOR I IN 1 .. 100
LOOP
INSERT INTO tab_test
VALUES (I);
END LOOP;
COMMIT;
END;
/
SQL> set serverout on
SQL> DECLARE
v_run_number integer;
v_temp1 integer;
BEGIN
--启动profiler
sys.DBMS_PROFILER.start_profiler (run_number => v_run_number);
--显示当前跟踪的运行序号(后面查询要用)
DBMS_OUTPUT.put_line ('run_number:' || v_run_number);
--运行要跟踪的PLSQL
sp_test;
--停止profiler
sys.DBMS_PROFILER.stop_profiler;
END;
/
run_number:1
记住输出的运行号。
3、查询结果
SQL> set lines 256 pages 100
获得本次prof的基本运行信息:
SQL> select runid,run_owner,run_date,run_total_time from plsql_profiler_runs;
RUNID RUN_OWNER RUN_DATE RUN_TOTAL_TIME
---------- -------------------------------- ------------------- --------------
1 U_TEST 2008-11-26.17:18:39 54215000
根据运行号和单元名(即测试的存储过程名)获得本次prof的单元信息:
SQL> select unit_number,unit_type,unit_owner,unit_name,unit_timestamp,total_time from plsql_profiler_units where runid = 1 and unit_name = 'SP_TEST';
UNIT_NUMBER UNIT_TYPE UNIT_OWNER UNIT_NAME UNIT_TIMESTAMP TOTAL_TIME
----------- -------------------------------- -------------------------------- -------------------------------- ------------------- ----------
2 PROCEDURE U_TEST SP_TEST 2008-11-26.17:17:56 0
根据运行号和单元号获得该存储过程每行运行的统计信息:
SQL> select runid,unit_number,line#,total_occur,total_time,min_time,max_time from plsql_profiler_data where runid = 1 and unit_number = 2;
RUNID UNIT_NUMBER LINE# TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME
---------- ----------- ---------- ----------- ---------- ---------- ----------
1 2 1 0 3000 3000 3000
1 2 4 101 179000 1000 3000
1 2 6 100 7084000 17000 1141000
1 2 9 1 336000 336000 336000
1 2 10 1 2000 2000 2000
以上信息都不怎么直观,通过下面的sql更加直观展示:
SQL> col text format a60
SQL> SELECT d.line#, --代码行号
s.text, --源代码
d.total_time, --总共运行时间(单位10000亿分之一秒)
d.total_occur, --总共运行次数
d.min_time, --最小运行时间
d.max_time --最大运行时间
FROM plsql_profiler_data d, sys.all_source s, plsql_profiler_units u
WHERE d.runid = 1 --运行号
and u.unit_name = 'SP_TEST' --单元名,即被测试的存储过程名
AND u.runid = d.runid
AND d.unit_number = u.unit_number
AND d.total_occur <> 0
AND s.TYPE(+) = u.unit_type
AND s.owner(+) = u.unit_owner
AND s.name(+) = u.unit_name
AND d.line# = NVL (s.line, d.line#)
ORDER BY u.unit_number, d.line#;
LINE# TEXT TOTAL_TIME TOTAL_OCCUR MIN_TIME MAX_TIME
---------- ------------------------------------------------------------ ---------- ----------- ---------- ----------
4 FOR I IN 1 .. 100 179000 101 1000 3000
6 INSERT INTO tab_test 7084000 100 17000 1141000
9 COMMIT; 336000 1 336000 336000
10 END; 2000 1 2000 2000
4、使用metalink里提供的profiler.sql脚本生成超文本测试结果信息
profiler.sql文件参见metalink的 Note:243755.1。
知道运行号的情况下:
SQL> @profiler.sql 1
或者不输入运行号:
SQL> @profiler
然后选择并输入运行号。
会在当前目录下生成一份名叫profiler_<runid>.html的超文本文件,很直观,如下:
![](https://i-blog.csdnimg.cn/blog_migrate/0b61eff148f8e862fd0237f367c4226e.jpeg)
以上介绍的是通过手工方法应用Profiler,使用相对比较烦杂,下面将详细介绍在PLSQL DEVELOPER 应用Profiler。
5.1、打开test window
方法一、新建一个test window,在test window中输入你要执行的PLSQL脚本。
方法二、选择要跟踪运行的存储过程,右键快捷菜单选择Test,如果是Package那么先右键快捷菜单里选择View,在打开的Package的存储过程列表里右键快捷菜单选择Test,如下图所示:
![](https://i-blog.csdnimg.cn/blog_migrate/83da728dc7d25bf63eb3b6983886aec3.jpeg)
5.2、进入调试窗口,在1处单击,打开profiler开关,在2处单击或按F8执行
![](https://i-blog.csdnimg.cn/blog_migrate/e02329cada4e62b50a4d69a82c9bd743.jpeg)
![](https://i-blog.csdnimg.cn/blog_migrate/7bc8a3f1656e44d4761b24eaa9e75ac8.jpeg)
每列的详细意义如下:
unit --单元名称,即执行的存储过程,包括其调用的过程
line --代码行号
total time --此行执行时间(颜色长度表示本行代码的执行时间与最长代码执行时间的百分比图)
occurrences --此行执行次数
text --对应代码行,对于加密的代码,将不能显示
Average time —平均运行时间
maximum time --最大运行时间
minimum time --最小运行时间(以上三个时间默认不显示,可以通过配置对话框选择显示,参加4.4)
列表中显示的源代码只显示一行,如果要定位则可以在对应的行中打开右键,选择[Go to unit line] ,这样就会直接跳到对应的源代码位置。
Profiler面板的工具栏说明:
a、显示配置对话框
b、刷新
c、删除当前运行号的数据
d、Run 显示当前的系统的所有Profiler列表,缺省为当前的跟踪
e、Unit 显示本次跟踪的单元列表信息(执行时间),缺省为所有单元的执行时间
5.4、Profiler配置对话框
![](https://i-blog.csdnimg.cn/blog_migrate/5a5699413ba53bb4a10f4e92347412fa.jpeg)
Available Columns --可用列
Selected Columns --选择要查看的列
Time units --时间单位(秒、毫秒、微秒)
Show 0 occurrences --是否显示执行0次的处理语句
Graphical time display --用图形显示处理时间的颜色深度百分比
--End--