dbms_profiler 调整PL/SQL的性能

        dbms_profiler 调整PL/SQL的性能       

        分类：            DBMS包2014-01-19 15:3921人阅读评论(0)收藏举报

目录(?)[+]

1. DBMS_PROFILER 介绍
2. DBMS_PROFILER 安装
3. 应用实例

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表和序列的同义词

[sql] view plaincopyprint?

1. sys@ORCL> CREATEUSER profiler IDENTIFIED BY profiler; 
2.  
3. 用户已创建。 
4.  
5. sys@ORCL> grant connect,resourceto profiler; 
6.  
7. 授权成功。 
8.  
9. sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs; 
10.  
11. 同义词已创建。 
12.  
13. sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units; 
14.  
15. 同义词已创建。 
16.  
17. sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data; 
18.  
19. 同义词已创建。 
20.  
21. sys@ORCL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber; 
22.  
23. 同义词已创建。 

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表和序列，并赋权

[sql] view plaincopyprint?

1. sys@ORCL> conn profiler/profiler 
2. 已连接。 
3. profiler@ORCL> @?/rdbms/admin/proftab.sql 
4. <span style="font-size: 12px;"><span style="font-family: 宋体, Arial;">... ...</span></span> 
5.  
6. profiler@ORCL> GRANT SELECTON plsql_profiler_runnumber TOPUBLIC; 
7.  
8. 授权成功。 
9.  
10. profiler@ORCL> GRANT SELECT,INSERT,UPDATE,DELETEON plsql_profiler_data TOPUBLIC; 
11.  
12. 授权成功。 
13.  
14. profiler@ORCL> GRANT SELECT,INSERT,UPDATE,DELETEON plsql_profiler_units TOPUBLIC; 
15.  
16. 授权成功。 
17.  
18. profiler@ORCL> GRANT SELECT,INSERT,UPDATE,DELETEON plsql_profiler_runs TOPUBLIC; 
19.  
20. 授权成功。 

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唯一运行编号的序列
[sql] view plaincopyprint?

1. profiler@ORCL> select o.OBJECT_NAME,o.OBJECT_TYPEfrom user_objects o orderby o.OBJECT_TYPE 
2.   2  / 
3.  
4. OBJECT_NAME                    OBJECT_TYPE 
5. ------------------------------ ------------------- 
6. SYS_C0023499                   INDEX 
7. SYS_C0023502                   INDEX 
8. SYS_C0023497                   INDEX 
9. PLSQL_PROFILER_RUNNUMBER       SEQUENCE 
10. PLSQL_PROFILER_UNITS           TABLE 
11. PLSQL_PROFILER_DATA            TABLE 
12. PLSQL_PROFILER_RUNS            TABLE 
13.  
14. 已选择7行。 

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、应用实例

[sql] view plaincopyprint?

1. u1@ORCL> conn u1/u1 
2. 已连接。 
3. u1@ORCL>  create table tab_test (a int); 
4.  
5. 表已创建。 
6.  
7. u1@ORCL> CREATE ORREPLACE PROCEDURE sp_testAS 
8.   2  BEGIN 
9.   3    FOR I IN 1 .. 100000 LOOP 
10.   4      INSERT INTO tab_testVALUES (I); 
11.   5    END LOOP; 
12.   6    COMMIT; 
13.   7  END; 
14.   8  / 
15.  
16. 过程已创建。 
17.  
18. u1@ORCL> DECLARE 
19.   2    v_run_number integer; 
20.   3  BEGIN 
21.   4    --启动profiler 
22.   5    sys.DBMS_PROFILER.start_profiler(run_number => v_run_number); 
23.   6    --显示当前跟踪的运行序号(后面查询要用) 
24.   7    DBMS_OUTPUT.put_line('run_number:' || v_run_number); 
25.   8    sp_test;跟踪的PLSQL 
26.   --运行要跟踪的PLSQL 
27.   9    sp_test; 
28. 10    --停止profiler 
29. 11    sys.DBMS_PROFILER.stop_profiler; 
30. 12  END; 
31. 13  / 
32. run_number:2 
33.  
34. PL/SQL 过程已成功完成。 

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 过程已成功完成。

[sql] view plaincopyprint?

1. --获得本次prof的基本运行信息： 
2. u1@ORCL> select runid,run_owner,run_date,run_total_timefrom plsql_profiler_runs where runid = 2; 
3.  
4.      RUNID RUN_OWNER                        RUN_DATE            RUN_TOTAL_TIME 
5. ---------- -------------------------------- ------------------- -------------- 
6.          2 U1                               2014-01-19 19:37:47      130000000 
7.  
8. --根据运行号和单元名（即测试的存储过程名）获得本次prof的单元信息： 
9. u1@ORCL> select unit_number,unit_type,unit_owner,unit_name,unit_timestamp,total_timefrom plsql_profiler_units where runid = 2and unit_name = 'SP_TEST'; 
10.  
11. UNIT_NUMBER UNIT_TYPE       UNIT_OWNER UNIT_NAME       UNIT_TIMESTAMP      TOTAL_TIME 
12. ----------- --------------- ---------- --------------- ------------------- ---------- 
13.           2 PROCEDURE       U1         SP_TEST         2014-01-19 19:35:23          0 
14.  
15. --根据运行号获得该存储过程每行运行的统计信息： 
16. u1@ORCL> select runid,unit_number,line#,total_occur,total_time,min_time,max_timefrom plsql_profiler_data where runid = 2; 
17.  
18.      RUNID UNIT_NUMBER      LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME 
19. ---------- ----------- ---------- ----------- ---------- ---------- ---------- 
20.          2           1          1           0          0          0          0 
21.          2           1          5           0       4080       4080       4080 
22.          2           1          7           1      77535      77535      77535 
23.          2           1          9           2      32646       2040      30606 
24.          2           1         11           1       3060       3060       3060 
25.          2           1         12           0          0          0          0 
26.          2           2          1           0       3060       3060       3060 
27.          2           2          3         101     136707       1020       2040 
28.          2           2          4         100   11547733      49990    5504021 
29.          2           2          6           1     106101     106101     106101 
30.          2           2          7           1       3060       3060       3060 
31.  
32. 已选择11行。 

--获得本次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更加直观展示：
[sql] view plaincopyprint?

1. u1@ORCL> SELECT d.line#,--代码行号 
2.   2         s.text, --源代码 
3.   3         round(d.total_time / 1000000000, 2) total_time,--总共运行时间(单位秒) 
4.   4         d.total_occur, --总共运行次数 
5.   5         round(d.min_time / 1000000000, 2) min_time, --最小运行时间 
6.   6         round(d.max_time / 1000000000, 2) max_time --最大运行时间            
7.   7    FROM plsql_profiler_data d, sys.all_source s, plsql_profiler_units u 
8.   8   WHERE d.runid = 7 --运行号 
9.   9     and u.unit_name = 'SP_TEST' --单元名，即被测试的存储过程名 
10. 10     AND u.runid = d.runid 
11. 11     AND d.unit_number = u.unit_number 
12. 12     AND d.total_occur <> 0 
13. 13     AND s.TYPE(+) = u.unit_type 
14. 14     AND s.owner(+) = u.unit_owner 
15. 15     AND s.name(+) = u.unit_name 
16. 16     AND d.line# = NVL(s.line, d.line#) 
17. 17   ORDER BY u.unit_number, d.line#; 
18.  
19.      LINE# TEXT                                                         TOTAL_TIME TOTAL_OCCUR   MIN_TIME   MAX_TIME 
20. ---------- ------------------------------------------------------------ ---------- ----------- ---------- ---------- 
21.          1 PROCEDURE sp_testAS                                                  0           1          0          0 
22.          2 BEGIN                                                                 0           1          0          0 
23.          3   FOR I IN 1 .. 100000 LOOP                                         .15      100001          0          0 
24.          4     INSERT INTO tab_testVALUES (I);                               6.38      100000          0        .34 
25.          5   END LOOP;                                                           0           1          0          0 
26.          6   COMMIT;                                                             0           1          0          0 
27.          7 END;                                                                  0           1          0          0 
28.  
29. 已选择7行。 

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

更多0

上一篇：第10周 hadoop与关系型数据库交换数据