使用profiler 评估一段plsql 代码的执行时间

1740阅读 0评论2014-10-01 wuxiaobo_2009
分类:WINDOWS


点击(此处)折叠或打开

  1. 有时候我们在执行一段比较长的plsql程序的时候,发现执行过程比较慢,但又不知道是哪部分出现了性能问题,这时候应该如何快速找到最耗费时间的部分呢?当然有很多方法,但这里推荐一种方法,可以使用dbms_profiler包来观察每一行代码的执行次数和时间。

  2. 一、操作步骤

  3. 1. 登录到SYS用户,检查dbms_profiler包有没有安装。没有的话运行$ORACLE_HOME/rdbms/admin/profload.sql来创建该包。

  4. 2. 将该包的执行权限赋予待调试的用户。

  5. 3. 登录到调试用户,运行$ORACLE_HOME/rdbms/admin/proftab.sql创建相关表。

  6. 4. 在待优化的PL/SQL程序前后加上运行dbms_profiler包的代码,如下:

  7. BEGIN

  8.   DBMS_PROFILER.START_PROFILER('any comment');

  9.   ...'your pl/sql code'

  10.   DBMS_PROFILER.STOP_PROFILER;

  11.   ...

  12. END;

  13. 5. 运行该PL/SQL程序,然后检查PLSQL_PROFILER_DATA表内容,可以从中看到每一行代码的执行次数,每行代码总共的执行时间。

  14. 二、具体的例子:

  15. SQL> conn / as sysdba;

  16. Connected.

  17. 1.检查是否安装了dbms_profiler包

  18. SQL> desc dbms_profiler;

  19. ERROR:

  20. ORA-04043: object dbms_profiler does not exist

  21. --如果没有安装的话,执行下面的语句安装

  22. SQL> @/disk_dev/oracle/product/9.2.0/rdbms/admin/profload.sql;

  23. Package created.

  24. Grant succeeded.

  25. Synonym created.

  26. Library created.

  27. Package body created.

  28. Testing for correct installation

  29. SYS.DBMS_PROFILER successfully loaded.

  30. PL/SQL procedure successfully completed.

  31. 2.把dbms_profiler的执行权限赋于oauser

  32. SQL> grant execute on dbms_profiler to oauser;

  33. Grant succeeded.

  34. 3.使用oauser登录,执行proftab.sql,此文件实际是创建plsql_profiler_data、plsql_profiler_units、plsql_profiler_runs三个表,具体proftab.sql的内容如下:

  35. proftab.sql:

  36. drop table plsql_profiler_data cascade constraints;

  37. drop table plsql_profiler_units cascade constraints;

  38. drop table plsql_profiler_runs cascade constraints;

  39. drop sequence plsql_profiler_runnumber;

  40. create table plsql_profiler_runs

  41. (

  42.   runid number primary key, -- unique run identifier,

  43.                                        -- from plsql_profiler_runnumber

  44.   related_run number, -- runid of related run (for client/

  45.                                        -- server correlation)

  46.   run_owner varchar2(32), -- user who started run

  47.   run_date date, -- start time of run

  48.   run_comment varchar2(2047), -- user provided comment for this run

  49.   run_total_time number, -- elapsed time for this run

  50.   run_system_info varchar2(2047), -- currently unused

  51.   run_comment1 varchar2(2047), -- additional comment

  52.   spare1 varchar2(256) -- unused

  53. );

  54.  

  55. comment on table plsql_profiler_runs is

  56.         'Run-specific information for the PL/SQL profiler';

  57. create table plsql_profiler_units

  58. (

  59.   runid number references plsql_profiler_runs,

  60.   unit_number number, -- internally generated library unit #

  61.   unit_type varchar2(32), -- library unit type

  62.   unit_owner varchar2(32), -- library unit owner name

  63.   unit_name varchar2(32), -- library unit name

  64.   -- timestamp on library unit, can be used to detect changes to

  65.   -- unit between runs

  66.   unit_timestamp date,

  67.   total_time number DEFAULT 0 NOT NULL,

  68.   spare1 number, -- unused

  69.   spare2 number, -- unused

  70.   --

  71.   primary key (runid, unit_number)

  72. );

  73. comment on table plsql_profiler_units is

  74.         'Information about each library unit in a run';

  75. create table plsql_profiler_data

  76. (

  77.   runid number, -- unique (generated) run identifier

  78.   unit_number number, -- internally generated library unit #

  79.   line# number not null, -- line number in unit

  80.   total_occur number, -- number of times line was executed

  81.   total_time number, -- total time spent executing line

  82.   min_time number, -- minimum execution time for this line

  83.   max_time number, -- maximum execution time for this line

  84.   spare1 number, -- unused

  85.   spare2 number, -- unused

  86.   spare3 number, -- unused

  87.   spare4 number, -- unused

  88.   --

  89.   primary key (runid, unit_number, line#),

  90.   foreign key (runid, unit_number) references plsql_profiler_units

  91. );

  92. comment on table plsql_profiler_data is

  93.         'Accumulated data from all profiler runs';

  94. create sequence plsql_profiler_runnumber start with 1 nocache;

  95. SQL> conn oauser/oauser;

  96. Connected.

  97. SQL> @/disk_dev/oracle/product/9.2.0/rdbms/admin/proftab.sql;

  98. 4.创建一个测试的过程,在代码中加入dbms_profiler代码,如下:

  99. create or replace procedure p_dbms_profiler_test

  100.  is

  101.  i number :=1 ;

  102.  v_str varchar2(4000);

  103. begin

  104.   dbms_profiler.start_profiler('测试一下');

  105.   dbms_output.put_line(i);

  106.   for i in 1..100 loop

  107.    exit when length(v_str)>100;

  108.     v_str := v_str||i;

  109.   end loop;

  110.   dbms_output.put_line(v_str);

  111.   dbms_profiler.stop_profiler;

  112. end p_dbms_profiler_test;

  113.  

  114. SQL> exec p_dbms_profiler_test;

  115. PL/SQL procedure successfully completed

  116. SQL> exec p_dbms_profiler_test;

  117. PL/SQL procedure successfully completed

  118. SQL> exec p_dbms_profiler_test;

  119. PL/SQL procedure successfully completed

  120. 执行一次p_dbms_profiler_test过程,就会分别在plsql_profiler_units和plsql_profiler_runs插入一条信息,在plsql_profiler_data插入具体的过程执行数据。

  121. 5.查看结果

  122. 通过下面的SQL语句我们可以知道每一行代码的执行次数,时间等等。

  123. SQL> select t1.line#,t3.text,t1.total_occur,t1.total_time,min_time, max_time

  124.   2 from plsql_profiler_data t1,plsql_profiler_units t2,user_source t3

  125.   3 where t1.runid = t2.runid

  126.   4 and t2.unit_name='P_DBMS_PROFILER_TEST'

  127.   5 and t2.unit_name = t3.name

  128.   6 and t1.line#=t3.line

  129.   7 and t1.runid=3

  130.   8 order by t1.line#

  131.   9 /

  132.  

  133. LINE# TEXT TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME

  134. ---------- -------------------------------------------------------------------------------- --

  135.     3 i number :=1 ; 0 0 0 0

  136.     6 dbms_profiler.start_profiler('测试一下'); 0 0 0 0

  137.     7 dbms_output.put_line(i); 1 17000 17000 17000

  138.     8 for i in 1..100 loop 56 222000 2000 6000

  139.     9 exit when length(v_str)>100; 56 232000 3000 7000

  140.    10 v_str := v_str||i; 55 254000 3000 16000

  141.    12 dbms_output.put_line(v_str); 1 5000 5000 5000

  142.    13 dbms_profiler.stop_profiler; 1 5000 5000 5000

  143.    14 end p_dbms_profiler_test; 0 0 0 0

  144. 9 rows selected

可以在 Metalink上Note:243755.1中可以下载的PROF.zip   下载 prof  分析脚本  -- 带实验

上一篇:10046 tkprof 分析 sql 语句
下一篇:oracle 游标