使用PL/SQL PROFILER定位PL/SQL瓶颈代码

使用PL/SQL PROFILER定位PL/SQL瓶颈代码

 

      对于SQL的优化，可以借助于SQL tuning advisor实现自动SQL优化与调整。而对于PL/SQL代码而言，既有SQL代码，又有PL/SQL代码，仅仅使用10046事件是远远不够的，因为可能SQL时间很短，而整个包或过程执行时间N久，而且包或过程中又嵌套有其他包，过程，函数。看得你头皮发麻。尽管没有工具可以直接作用于PL/SQL代码进行优化，但借助于PL/SQL PROFILER来定位你的代码块中哪些部分是性能瓶颈就已经达到了事半功倍的效果。本文首先描述了安装PROFILER，接下给出在PL/SQL块中使用字面量与绑定变量时定义瓶颈块以及对比的情形，最后部分列出一些相关脚本。

  

1、配置PROFILER及演示环境

[sql] 
--演示环境  
sys@USBO> select * from v$version where rownum<2;  
  
BANNER  
------------------------------------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  
a、使用需要进行plsql剖析的schema执行脚本proftab.sql(也可以使用一个账户创建然后创建同义词并授权给public)  
--首次使用时执行，会创建相应的表存储profiler信息，即plsql_profiler_runs,plsql_profiler_units,plsql_profiler_data  
sys@USBO> conn scott/tiger;  
Connected.  
scott@USBO> @?/rdbms/admin/proftab.sql  
  
b、使用sysdba帐户安装包DBMS_PROFILER，执行脚本profload.sql   
scott@USBO> conn / as sysdba  
Connected.  
sys@USBO> @?/rdbms/admin/profload.sql  
  
c、如果需要，创建plan_table,执行脚本utlxplan.sql  
sys@USBO> @?/rdbms/admin/utlxplan.sql  
  
sys@USBO> GRANT ALL ON sys.plan_table TO public;  
  
sys@USBO> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;  
  
sys@USBO> conn scott/tiger;  
Connected.  
  
--创建演示表  
scott@USBO> create table t1(id number,val number);  
  
--创建一个基于字面量的过程  
scott@USBO> create or replace procedure literals  
  2  is  
  3   v_num number;  
  4  begin  
  5   for i in 1..100000 loop  
  6     v_num := dbms_random.random;                 
  7     execute immediate   
  8      'insert into t1 values ('||v_num||','||v_num||')';  
  9   end loop;  
 10   end;  
 11  /  
  
Procedure created.  

 

2、使用PROFILER剖析PLSQL代码(法一)

[sql] 
a、启动profiler，调用过程start_profiler  
scott@USBO> execute dbms_profiler.start_profiler('literals');  
  
b、执行你需要剖析的代码(包，过程，匿名块等)  
scott@USBO> exec literals;  
  
c、停止profiler，调用过程stop_profiler  
scott@USBO> execute dbms_profiler.stop_profiler;  
  
d、查看profiler报告  
scott@USBO> @chk_profile  
Enter value for input_comment_name: literals  
Enter value for input_sp_name: literals  
  
TEXT                                                    TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME  
------------------------------------------------------- ----------- ---------- -------- --------  
procedure literals                                                1         .0       .0       .0  
procedure literals                                                3         .0       .0       .0  
procedure literals                                                0         .0       .0       .0  
 for i in 1..100000 loop                                     100001         .2       .0       .0  
   v_num := dbms_random.random;                              100000         .8       .0       .0  
   execute immediate                                         100000       49.9       .0       .0  
 end;                                                             1         .0       .0       .0  
procedure literals                                                2         .0       .0       .0  

 

  

--上面的结果可以看出整个过程中execute immediate耗用49s中，也即是说，如果能够降低该行代码时间，则整个性能会大幅提升  

3、使用PROFILER剖析PLSQL代码(法二)

[sql] 
--这个方法实际也没有太多的变化，只不过将需要剖析的代码和启用profiler与停止profiler封装到一个sql中  
--下面创建一个使用绑定变量的示例来进行剖析  
scott@USBO> create or replace procedure binds  
  2  is  
  3   v_num number;  
  4  begin  
  5   for i in 1..100000 loop  
  6     v_num := dbms_random.random;  
  7     insert into t1 values (v_num,v_num);  
  8   end loop;  
  9  end;  
 10  /  
  
Procedure created.  
  
--直接调用call_profiler.sql(该代码封装了启动profiler,停止profiler)  
scott@USBO> @call_profiler  
Profiler started  
  
PL/SQL procedure successfully completed.  
  
Profiler stopped  
  
Profiler flushed  
  
runid:4  
  
--Author : Leshami  
--Blog   : http://blog.csdn.net/leshami  
  
--查看profiler报告  
scott@USBO> @evaluate_profiler_results  
Enter value for runid: 4  
Enter value for name: binds  
Enter value for owner: scott  
  
      Line      Occur        sec Text  
---------- ---------- ---------- ------------------------------------------------------------  
         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、evaluate_profiler_results.sql   
--file_name:evaluate_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(+)