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

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

--演示环境
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代码(法一)

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

--这个方法实际也没有太多的变化,只不过将需要剖析的代码和启用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、示例中用到的脚本

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(+)
       AND s.name = p.unit_name(+)
       AND s.TYPE = p.unit_type(+)
       AND s.line = p.line#(+)
       AND s.name = UPPER ( '&&name' )
       AND s.owner = UPPER ( '&&owner' )
ORDER BY s.line;

SELECT exec.cnt / total.cnt * 100 "Code% coverage"
FROM   (SELECT COUNT ( 1 ) cnt
        FROM   plsql_profiler_data d, plsql_profiler_units u
        WHERE      d.runid = &&runid
               AND u.runid = d.runid
               AND u.unit_number = d.unit_number
               AND u.unit_name = UPPER ( '&&name' )
               AND u.unit_owner = UPPER ( '&&owner' )) total
     , (SELECT COUNT ( 1 ) cnt
        FROM   plsql_profiler_data d, plsql_profiler_units u
        WHERE      d.runid = &&runid
               AND u.runid = d.runid
               AND u.unit_number = d.unit_number
               AND u.unit_name = UPPER ( '&&name' )
               AND u.unit_owner = UPPER ( '&&owner' )
               AND d.total_occur > 0) exec;

undef runid
undef owner
undef name

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL创建表失败的问题

今天有一个朋友问我一个MySQL的建表问题,问题的现象是创建表失败,根据他的反馈,问题比较奇怪, CREATE TABLE XXX ..此处省略260多个字...

4157
来自专栏杨建荣的学习笔记

sqlldr批量导入导出数据测试(86天)

sqlldr是在处理大数据量的操作中建议采用的方式,它有许多性能想关的开关,能最大程度的减少redo,undo的生成,控制数据的处理方式(insert,appe...

4619
来自专栏漫漫全栈路

Oracle 学习笔记

前言 本贴内容纪录Oracle课程中的学习笔记,和Oracle的课后作业,以及数据库相关课程的学习笔记,笔记部分使用实例代码记录,不记详细语法。 用户管理...

3555
来自专栏GreenLeaves

Oracle TM锁和TX锁

CREATE TABLE "TEST6" ( "ID" VARCHAR2(30), "NAME" VARCHAR2(30), "...

2247
来自专栏程序猿

Mssql高级注入笔记

暴库特殊技巧::%5c='\' 或者把/和\ 修改%5提交 and 0<>(select count(*) from master.dbo.sysdatabas...

3847
来自专栏FreeBuf

新手科普 | MySQL手工注入之基本注入流程

MySQL手工注入的基本步骤以及一些技巧的记录,当出现学习手工注入的时候,网上的文章参差不齐,导致很长一段时间对手工注入的理解一直处于一知半解的状态,特此记录本...

2317
来自专栏数据和云

返璞归真:实例会话RAC条件下的笛卡尔积种参数可能性

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 参数文件是Or...

29910
来自专栏蓝天

Oracle 系统表大全

数据字典dict总是属于Oracle用户sys的。   1、用户:    select username from dba_users;   改口令    al...

1002
来自专栏乐沙弥的世界

MySQL数据类型 -- 日期时间型

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/art...

1702
来自专栏谢庆玲的专栏

MySQL 查询分析

本文主要由一个案例引发对 MySQL 性能问题的思考,主要讲述 MySQL 慢查询和 explain 工具这两个定位 MySQL 性能瓶颈的方法。

1.3K1

扫码关注云+社区

领取腾讯云代金券