dbms_profiler 调整PL/SQL的性能

最新推荐文章于 2021-04-09 02:50:43 发布

事后诸葛亮

最新推荐文章于 2021-04-09 02:50:43 发布

阅读量1.9k

点赞数

分类专栏： DBMS包

本文链接：https://blog.csdn.net/zq9017197/article/details/18502287

版权

DBMS包专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1、DBMS_PROFILER 介绍

如果需要针对PL/SQL进行跟踪，找出最耗时间的语句块，可以使用oracle提供的DBMS_PROFILER进行跟踪。

检查DBMS_PROFILER包是否安装 sys@ORCL> desc dbms_profiler

如果没有安装可以运行$ORACLE_HOME/rdbms/admin/profload.sql 脚本安装

默认情况下DBMS_PROFILER包已经安装，但是想要在某个schema下使用dbms_profiler，需要创建相应的表。

sql在$ORACLE_HOME/rdbms/admin/proftab.sql 执行即可

2、DBMS_PROFILER 安装

desc dbms_profiler 可以看见dbms_profiler 包已经安装，然后创建一个用于存放跟踪信息的用户，及其prof表和序列的同义词

sys@ORCL> CREATE USER profiler IDENTIFIED BY profiler;

用户已创建。

sys@ORCL> grant connect,resource to profiler;

授权成功。

sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;

同义词已创建。

sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;

同义词已创建。

sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;

同义词已创建。

sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

同义词已创建。

用profiler用户创建prof表和序列，并赋权

sys@ORCL> conn profiler/profiler
已连接。
profiler@ORCL> @?/rdbms/admin/proftab.sql
... ...

profiler@ORCL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;

授权成功。

profiler@ORCL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_data TO PUBLIC;

授权成功。

profiler@ORCL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_units TO PUBLIC;

授权成功。

profiler@ORCL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs TO PUBLIC;

授权成功。

备注：
plsql_profiler_runs        --prof运行信息
plsql_profiler_units        --prof每个单元信息
plsql_profiler_data        --prof每个单元的详细数据
plsql_profiler_runnumber    --用来生成prof唯一运行编号的序列

profiler@ORCL> select o.OBJECT_NAME,o.OBJECT_TYPE from user_objects o order by o.OBJECT_TYPE
  2  /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_C0023499                   INDEX
SYS_C0023502                   INDEX
SYS_C0023497                   INDEX
PLSQL_PROFILER_RUNNUMBER       SEQUENCE
PLSQL_PROFILER_UNITS           TABLE
PLSQL_PROFILER_DATA            TABLE
PLSQL_PROFILER_RUNS            TABLE

已选择7行。

清楚旧数据:

delete from profiler.plsql_profiler_data;
delete from profiler.plsql_profiler_units;
delete from profiler.plsql_profiler_runs;

3、应用实例

u1@ORCL> conn u1/u1
已连接。
u1@ORCL>  create table tab_test (a int);

表已创建。

u1@ORCL> CREATE OR REPLACE PROCEDURE sp_test AS
  2  BEGIN
  3    FOR I IN 1 .. 100000 LOOP
  4      INSERT INTO tab_test VALUES (I);
  5    END LOOP;
  6    COMMIT;
  7  END;
  8  /

过程已创建。

u1@ORCL> DECLARE
  2    v_run_number integer;
  3  BEGIN
  4    --启动profiler
  5    sys.DBMS_PROFILER.start_profiler(run_number => v_run_number);
  6    --显示当前跟踪的运行序号(后面查询要用)
  7    DBMS_OUTPUT.put_line('run_number:' || v_run_number);
  8    sp_test;跟踪的PLSQL
  --运行要跟踪的PLSQL
  9    sp_test;
 10    --停止profiler
 11    sys.DBMS_PROFILER.stop_profiler;
 12  END;
 13  /
run_number:2

PL/SQL 过程已成功完成。

--获得本次prof的基本运行信息：
u1@ORCL> select runid,run_owner,run_date,run_total_time from plsql_profiler_runs where runid = 2;

     RUNID RUN_OWNER                        RUN_DATE            RUN_TOTAL_TIME
---------- -------------------------------- ------------------- --------------
         2 U1                               2014-01-19 19:37:47      130000000

--根据运行号和单元名（即测试的存储过程名）获得本次prof的单元信息：
u1@ORCL> select unit_number,unit_type,unit_owner,unit_name,unit_timestamp,total_time from plsql_profiler_units where runid = 2 and unit_name = 'SP_TEST';

UNIT_NUMBER UNIT_TYPE       UNIT_OWNER UNIT_NAME       UNIT_TIMESTAMP      TOTAL_TIME
----------- --------------- ---------- --------------- ------------------- ----------
          2 PROCEDURE       U1         SP_TEST         2014-01-19 19:35:23          0

--根据运行号获得该存储过程每行运行的统计信息：
u1@ORCL> select runid,unit_number,line#,total_occur,total_time,min_time,max_time from plsql_profiler_data where runid = 2;

     RUNID UNIT_NUMBER      LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- ----------- ---------- ----------- ---------- ---------- ----------
         2           1          1           0          0          0          0
         2           1          5           0       4080       4080       4080
         2           1          7           1      77535      77535      77535
         2           1          9           2      32646       2040      30606
         2           1         11           1       3060       3060       3060
         2           1         12           0          0          0          0
         2           2          1           0       3060       3060       3060
         2           2          3         101     136707       1020       2040
         2           2          4         100   11547733      49990    5504021
         2           2          6           1     106101     106101     106101
         2           2          7           1       3060       3060       3060

已选择11行。

以上信息都不怎么直观，通过下面的sql更加直观展示：

u1@ORCL> SELECT d.line#, --代码行号
  2         s.text, --源代码
  3         round(d.total_time / 1000000000, 2) total_time, --总共运行时间(单位秒)
  4         d.total_occur, --总共运行次数
  5         round(d.min_time / 1000000000, 2) min_time, --最小运行时间
  6         round(d.max_time / 1000000000, 2) max_time --最大运行时间           
  7    FROM plsql_profiler_data d, sys.all_source s, plsql_profiler_units u
  8   WHERE d.runid = 7 --运行号
  9     and u.unit_name = 'SP_TEST' --单元名，即被测试的存储过程名
 10     AND u.runid = d.runid
 11     AND d.unit_number = u.unit_number
 12     AND d.total_occur <> 0
 13     AND s.TYPE(+) = u.unit_type
 14     AND s.owner(+) = u.unit_owner
 15     AND s.name(+) = u.unit_name
 16     AND d.line# = NVL(s.line, d.line#)
 17   ORDER BY u.unit_number, d.line#;

     LINE# TEXT                                                         TOTAL_TIME TOTAL_OCCUR   MIN_TIME   MAX_TIME
---------- ------------------------------------------------------------ ---------- ----------- ---------- ----------
         1 PROCEDURE sp_test AS                                                  0           1          0          0
         2 BEGIN                                                                 0           1          0          0
         3   FOR I IN 1 .. 100000 LOOP                                         .15      100001          0          0
         4     INSERT INTO tab_test VALUES (I);                               6.38      100000          0        .34
         5   END LOOP;                                                           0           1          0          0
         6   COMMIT;                                                             0           1          0          0
         7 END;                                                                  0           1          0          0

已选择7行。

更多内容参考：

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

http://blog.csdn.net/leshami/article/details/12100235

对比 PL/SQL profiler 剖析结果

http://blog.csdn.net/leshami/article/details/12110195

使用PL/SQL Developer剖析PL/SQL代码

http://blog.csdn.net/leshami/article/details/12187115

PL/SQL Profiler 剖析报告生成html

http://blog.csdn.net/leshami/article/details/12426469