Home >Database >Mysql Tutorial >对比 PL/SQL profiler 剖析结果

对比 PL/SQL profiler 剖析结果

WBOY
WBOYOriginal
2016-06-07 17:32:28988browse

使用PL/SQL PROFILER 剖析PL/SQL代码是快速定位PL/SQL代码段最有效的方法。在上一篇文章使用PL/SQL PROFILER 定位 PL/SQL 瓶颈代

使用PL/SQL PROFILER 剖析PL/SQL代码是快速定位PL/SQL代码段最有效的方法。在上一篇文章使用PL/SQL PROFILER 定位 PL/SQL 瓶颈代码中描述了安装PROFILER,并给出了剖析的示例。本文参照了Tom大师的代码来对比剖析前后的性能并附上其代码。

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

1、用于实施剖析的存储过程

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

BANNER
------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--用于实施剖析的原过程,下面是使用字面量的过程,注意代码中包含了启用与停止profiler
scott@USBO> create or replace procedure binds
  2  is
  3  v_num number;
  4  begin
  5  dbms_profiler.start_profiler('binds');
  6  for i in 1..100000 loop
  7    v_num := dbms_random.random;
  8    insert into t1 values (v_num,v_num);
  9  end loop;
 10  dbms_profiler.stop_profiler;
 11  end;
 12  /

Procedure created.

--用于实施剖析的原过程,下面是使用绑定量的过程,,注意代码中包含了启用与停止profiler
scott@USBO> create or replace procedure literals
  2  is
  3  v_num number;
  4  begin
  5  dbms_profiler.start_profiler('literals');
  6  for i in 1..100000 loop
  7    v_num := dbms_random.random;             
  8    execute immediate
  9      'insert into t1 values ('||v_num||','||v_num||')';
 10  end loop;
 11  dbms_profiler.stop_profiler;
 12  end;
 13  /

Procedure created.

--Author : Leshami
--Blog  :

--清除剖析表中的历史数据(每次剖析对比前执行)
scott@USBO> delete from plsql_profiler_data;

scott@USBO> delete from plsql_profiler_units;

scott@USBO> delete from plsql_profiler_runs;

--分别执行两个不同的过程
scott@USBO> exec literals;

scott@USBO> exec binds;

2、提取剖析对比结果

scott@USBO> @profsum

2 rows updated.

PL/SQL procedure successfully completed.

=
=
====================
total time

GRAND_TOTAL
-----------   
      58.93

=
=
====================
total time spent on each run

  RUNID RUN_COMMENT      SECS
------- ----------- ---------
      7 literals        53.19
      8 binds            5.75

=
=
====================
percentage of time in each module, for each run separately

  RUNID RUN_COMMENT UNIT_OWNER  UNIT_NAME          SECS PERCEN
------- ----------- ----------- -------------- --------- ------
      7 literals    SCOTT      LITERALS          53.19  100.0
      8 binds      SCOTT      BINDS              5.75  100.0

=
=
====================
percentage of time in each module, summarized across runs

UNIT_OWNER  UNIT_NAME          SECS PERCENTAG
----------- -------------- --------- ---------
SCOTT      LITERALS          53.19    90.25
SCOTT      BINDS              5.75      9.75

=
=
====================
lines taking more than 1% of the total time, each run separate

  RUNID    HSECS    PCT OWNER      UNIT_NAME      LINE# TEXT
------- --------- ------- ----------- -------------- ------ ---------------------
      7  5221.18    88.6 SCOTT      LITERALS            8 execute immediate
      8    502.97    8.5 SCOTT      BINDS              8 insert into t1 values
                                                            (v_num,v_num);

      7    73.04    1.2 SCOTT      LITERALS            7 v_num :=
                                                            dbms_random.random;

=
=
====================
most popular lines (more than 1%), summarize across all runs

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn