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

对于SQL的优化，可以借助于SQL tuning advisor实现自动SQL优化与调整。而对于PL/SQL代码而言，既有SQL代码，又有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及演示环境

--演示环境
sys@USBO> select * from v$version where rownum

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代码(法一)

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代码(法二)