SQL profiler 剖析结果

使用PL/SQL PROFILER 剖析PL/SQL代码是快速定位PL/SQL代码段最有效的方法。在上一篇文章使用PL/SQL PROFILER 定位 PL/SQL 瓶颈代码中描述了安装PROFILER，并给出了剖析的示例。本文参照了Tom大师的代码来对比剖析前后的性能并附上其代码。使用PL/SQL PROFILER 定位 PL/SQL 瓶颈代码 http://www.linuxidc.com/Linux/2013-09/90647.htm1、用于实施剖析的存储过程--环境
sys@USBO> select * from v$version where rownum<2;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 : http://www.linuxidc.com--清除剖析表中的历史数据（每次剖析对比前执行）
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> @profsum2 rows updated.PL/SQL procedure successfully completed.=
=
====================
total timeGRAND_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 runsUNIT_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 HSECS PCT UNIT_OWNER UNIT_NAME LINE# TEXT
--------- ------- ----------- -------------- ------ ---------------------
5221.18 88.6 SCOTT LITERALS 8 execute immediate
502.97 8.5 SCOTT BINDS 8 insert into t1 values
（v_num,v_num）; 73.04 1.2 SCOTT LITERALS 7 v_num :=
dbms_random.random;PL/SQL procedure successfully completed.=
=
====================
Number of lines actually executed in different units （by unit_name）UNIT_OWNER UNIT_NAME LINES_EXECUTED LINES_PRESENT PCT
----------- -------------- -------------- ------------- -------
SCOTT LITERALS 4 7 57.1
SCOTT BINDS 4 7 57.1=
=
====================
Number of lines actually executed for all unitsLINES_EXECUTED
--------------
8=
=
====================
total number of lines in all unitsLINES_PRESENT
-------------
14推荐阅读：使用PL/SQL执行java存储来获得MAC地址 http://www.linuxidc.com/Linux/2013-04/83498.htm在SQL中使用PL/SQL函数存在的问题 http://www.linuxidc.com/Linux/2013-07/87186.htm 更多精彩内容请看下一页：http://www.linuxidc.com/Linux/2013-09/90671p2.htm

使用 PL/SQL PROFILER 定位 PL/SQL 瓶颈代码关于 Private strand flush not complete相关资讯 PL/SQL SQL profiler

PL/SQL之存储过程和函数（今 14:09）
PL/SQL Developer连接本地Oracle （07月27日）
【PL/SQL系列】Oracle存储过程使用（04月23日）

PL/SQL Developer 使用技巧分享（09月16日）
PL/SQL实现Java中的split（）方法的（07月10日）
从一个案例看PL/SQL代码片的编译与（03月04日）

本文评论查看全部评论（0）