Oracle性能诊断包DBMS_PROFILER帮助调优PL/SQL 存储过程

Oracle为我们提供了DBMS_PROFILER包, 对于诊断包、存储过程和函数的性能非常有用。通过这个包，我们可以知道哪句SQL耗费的时间最多, 然后找出来优化。

 

一．DBMS_PROFILER包

DBMS_PROFILER 是一个数据库包，默认已经安装了。

可以通过desc dbms_profiler 查看包的信息。如果没有安装，可以执行脚本$ORACLE_HOME/rdbms/admin/profload.sql 安装。

 

 

二．DBMS_PROFILER 包相关的表

我们需要先创建DBMS_PROFILER包需要的三个表和序列，直接执行脚本$ORACLE_HOME/rdbms/admin/proftab.sql即可。

 表创建之后，再创建同义词，并授权给其它用户。

 

  

三．准备存储过程

工作中需要调优的存储过程，早就创建了。这里作为例子，我们创建一个存储过程，随后我们会对它进行性能诊断。

drop table emp_tab;

create table emp_tab
(

 id integer,
 name varchar2(50),
 hire_date date
);

create or replace procedure test_profiler
as
  cnt integer;
begin
   for i in 1..1000 loop
      insert into emp_tab
      values(i,dbms_random.string('p',10), sysdate - 1000 + i);
   end loop;  

   select count(*) into cnt
    from   emps
    where  emp_id in (select emp_id
                      from   employee2
                      where  emp_dept = 'RD1')
           or emp_id = 600000;
end;

 

四．性能诊断

写个很简单的PL/SQL脚本，开启性能诊断，调用需要诊断的存储过程，然后关闭性能诊断。start_profiler 过程有好几个重载方法，第1个参数是注释，按自己的喜欢随便写都行，主要是方便后面通过注释查询runid。

begin

  dbms_profiler.start_profiler('tosska',systimestamp);

  test_profiler;

  dbms_profiler.stop_profiler;

end;

 

直接看三个性能诊断表的数据，很难和存储过程的代码对应。因此，可以执行下面的SQL，结果中可以非常清楚地看到哪行SQL执行时间最多。注意，默认的时间单位是纳秒，我换算成了毫秒。

SELECT a.*,
  d.total_occur/1000000 total_occur_ms,
  d.total_time /1000000 total_time_ms,
  d.min_time   /1000000 min_time_ms,
  d.max_time   /1000000 max_time_ms
FROM
  (SELECT s.line,
    s.text,
    u.runid,
    U.UNIT_NUMBER
  FROM sys.all_source s,
    plsql_profiler_units u
  WHERE s.TYPE = u.unit_type
  AND s.owner  = u.unit_owner
  AND s.name   = u.unit_name
  AND u.runid  = 6
  ) a
LEFT JOIN plsql_profiler_data d
ON a.runid       = d.runid
AND a.unit_number=d.unit_number
AND a.line       =d.line#;

 

 五．优化

很容易看出来，第10行的SQL语句耗费的时间最多。复制出来，然后粘贴到 Tosska SQL Tuning Expert for Oracle 进行调优。最后，将优化后的SQL代替存储过程中的源SQL，重新编译存储过程即可。

 

  存储过程的性能，得到了很大的提高。从69秒下降到0.58秒。