PL/SQL是Oracle提供的过程化数据库语言，因其与Oracle数据库、SQL语言之间的无缝连接，一直在数据库开发领域扮演着很重要的地位。
我们使用PL/SQL开发的程序代码，无论是封装过的存储过程sp，还是专门的匿名PL/SQL块，很多时候都需要进行性能评测，来发现潜在的错误调用或者优化空间。在Oracle11g里，我们可以使用dbms_hprof包来实现对pl/sql代码的动态分析。
1、 DBMS_HPROF浅析
DBMS_HPROF是Oracle 11g中提供的性能调试程序包，提供进行PL/SQL程序调试性能的接口。该包程序对PL/SQL进行层次数据收集，进行底层调用分析和性能信息生成。
PL/SQL程序往往是建立在层次调用的基础之上。代码A调用B和C，C又调用D，D又调用E。在各种嵌套的层次调用关系中，我们只管的感觉往往是代码A执行速度慢，却并不知道具体是那个部分消耗。使用dbms_hprof程序包，可以帮助我们进行pl/sql层次调用分析。
Dbms_hprof包括两个组件：数据收集组件（Data Collection Component）和分析组件（Analyzer Component）。
下面我们一起进行dbms_hprof程序包的调试实验。
2、 相应环境准备
首先我们准备一个实验存储过程，作为实验对象。
SQL> create or replace procedure P_PROF_TEST is
2 i number;
3 procedure p1 is
4 i number;
5 begin
6 select count(*) into i
7 from all_tables;
8 end;
9
10 procedure p2 is
11 i number;
12 begin
13 select count(*) into i
14 from all_tab_columns;
15 end;
16
17 begin
18 for i in 1..10 loop
19 p2;
20 end loop;
21
22 for i in 1..100 loop
23 p1;
24 end loop;
25 end P_PROF_TEST;
26 /
Procedure created
我们构造了一个存储过程p_prof_test，存储过程在sys用户下。此外进行profile分析还需要一个directory目录对象。
--建立一个test目录
[oracle@oracle11g test01]$ pwd
/test01
[oracle@oracle11g test01]$ ls -l
total 0
SQL> sho user
User is "SYS"
SQL> create directory prof_out as '/test01';
Directory created
同时，保证用户具有执行dbms_hprof包的execute权限。
3、使用dbms_hprof进行数据收集
下面进行调试实验。
--开始调试过程
SQL> exec dbms_hprof.start_profiling(location => 'PROF_OUT',filename => 't.trc');
PL/SQL procedure successfully completed
Executed in 0 seconds
--执行实验的存储过程
SQL> exec p_prof_test;
PL/SQL procedure successfully completed
Executed in 11.468 seconds
SQL> exec dbms_hprof.stop_profiling;
PL/SQL procedure successfully completed
Executed in 0 seconds
Dbms_hprof的存储过程start_profiling是开始进行调试度量的过程。该方法的调用需要指定一个directory目录对象，还有生成trace文件的名称。Dbms_hprof将分析的结果存放在磁盘的跟踪文件里。
[oracle@oracle11g test01]$ ls -l
total 20
-rw-r--r-- 1 oracle oinstall 19626 Jul 18 14:28 t.trc
跟踪文件中的内容，都是执行过程中的一些调用关系。如下为片段：
P#C PLSQL."SYS"."DBMS_HPROF"::11."STOP_PROFILING"#980980e97e42f8ec #59
P#R
P#R
P#R
P#! PL/SQL Timer Stopped
一系列的调用关系，形成跟踪文件t.trc。我们通过直接阅读的手段是可以读取到的。不过dbms_hprof提供了一系列的工具，可以帮助我们分析执行结果。
4、分析跟踪数据结果
Dbms_hprof的跟踪结果有两种方式，数据表存储和html格式报告。我们下面分别进行介绍。
ü 数据表存储
对分析的结果，我们可以使用数据表来进行存储。首先需要在分析的数据Schema下构建系列数据表。
SQL> sho user
USER is "SYS"
SQL> @?/rdbms/admin/dbmshptab.sql
使用脚本可以帮助创建一系列的分析结果容纳数据表。
SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'DBMSHP_%';
TABLE_NAME
------------------------------
DBMSHP_FUNCTION_INFO
DBMSHP_PARENT_CHILD_INFO
DBMSHP_RUNS
Executed in 0.063 seconds
我们需要调用dbms_hprof分析组件方法。
SQL> var mid number;
SQL> begin
2 :mid := dbms_hprof.analyze(location => 'PROF_OUT',filename => 't.trc',run_comment=>'Run Dev');
3 end;
4 /
PL/SQL procedure successfully completed
Executed in 0.078 seconds
mid
---------
1
返回的mid表示分析结果的唯一标记。通过这个标记可以从hprof系列结果表中获取到结果数据。
其中，视图dbmshp_runs表示进行分析的次数信息，每次进行分析，都会对应一条记录。其中最重要的字段就是runid和total_elapsed_time，分别表示该次分析的序列号和分析执行时间。
SQL> select runid, TOTAL_ELAPSED_TIME, RUN_COMMENT from dbmshp_runs;
RUNID TOTAL_ELAPSED_TIME RUN_COMMENT
---------- ------------------------------ --------------------
1 7568868 Run Dev
Executed in 0.016 seconds
其次第二个视图就是DBMSHP_FUNCTION_INFO，记录着pl/sql代码相关的函数和调用对象信息。
SQL> select runid, symbolid, module, type, function, FUNCTION_ELAPSED_TIME from dbmshp_function_info;
RUNID SYMBOLID MODULE TYPE FUNCTION FUNCTION_E
----- ---------- -------------------- --------------- ------------------------------ ----------
1 1 __anonymous_block 142
1 2 __plsql_vm 35
1 3 DBMS_HPROF PACKAGE BODY STOP_PROFILING 0
1 4 DBMS_TRANSACTION PACKAGE BODY LOCAL_TRANSACTION_ID 26
1 5 P_PROF_TEST PROCEDURE P_PROF_TEST 453
1 6 P_PROF_TEST PROCEDURE P_PROF_TEST.P1 4400
1 7 P_PROF_TEST PROCEDURE P_PROF_TEST.P2 192
1 8 P_PROF_TEST PROCEDURE __static_sql_exec_line13 1871195
1 9 P_PROF_TEST PROCEDURE __static_sql_exec_line6 5692425
9 rows selected
Executed in 0.032 seconds
其中包括的内容，是该段pl/sql代码相关所有相关函数内容。各列中的symbolid表示的就是相关函数在评测结果中的表示信息。
最后就是dbmshp_parent_child_info，表示前后调用函数关系和每个函数的消耗时间。
SQL> select runid, parentsymid, childsymid, SUBTREE_ELAPSED_TIME from dbmshp_parent_child_info;
RUNID PARENTSYMID CHILDSYMID SUBTREE_ELAPSED_TIME
----- ----------- ---------- ---------------------------------------
1 1 5 7568665
1 1 4 26
1 1 3 0
1 2 1 7568833
1 5 6 5696825
1 5 7 1871387
1 6 9 5692425
1 7 8 1871195
8 rows selected
Executed in 0.016 seconds
除了将分析结果保存在数据表外，还可以选择使用html报表方式显示。
ü Html报表格式
分析t.trc文件，获取最后的分析报告。我们可以选择可读性更好的html格式。
[oracle@oracle11g test01]$ pwd
/test01
[oracle@oracle11g test01]$ ls
t.trc
[oracle@oracle11g test01]$
分析使用的工具软件是plshprof。
[oracle@oracle11g test01]$ plshprof -output m t.trc
PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
[9 symbols processed]
[Report written to 'm.html']
其中，m表示生成的报告文件主文件是m.html。t.trc为分析的数据源跟踪文件名称。
此时观察生成目录，可以知道对应的文件是多个。
[oracle@oracle11g test01]$ ls
m_2c.html m_fn.html m_mf.html m_nsf.html m_tc.html m_ts.html
m_2f.html m.html m_ms.html m_nsp.html m_td.html t.trc
m_2n.html m_md.html m_nsc.html m_pc.html m_tf.html
其中只有m.html才是报告的主文件。下面考虑将文件获取到可以打开的系统下。
[oracle@oracle11g test01]$ zip -r load.zip *.*
adding: m_2c.html (deflated 69%)
adding: m_2f.html (deflated 71%)
adding: m_2n.html (deflated 68%)
adding: m_fn.html (deflated 80%)
adding: m.html (deflated 77%)
adding: m_md.html (deflated 80%)
adding: m_mf.html (deflated 80%)
adding: m_ms.html (deflated 80%)
adding: m_nsc.html (deflated 63%)
adding: m_nsf.html (deflated 64%)
adding: m_nsp.html (deflated 60%)
adding: m_pc.html (deflated 88%)
adding: m_tc.html (deflated 80%)
adding: m_td.html (deflated 80%)
adding: m_tf.html (deflated 80%)
adding: m_ts.html (deflated 79%)
adding: t.trc (deflated 94%)
生成zip压缩文件。
[oracle@oracle11g test01]$ ls
load.zip m_2n.html m_md.html m_nsc.html m_pc.html m_tf.html
m_2c.html m_fn.html m_mf.html m_nsf.html m_tc.html m_ts.html
m_2f.html m.html m_ms.html m_nsp.html m_td.html t.trc
5、结论
本篇中，我们详细介绍了dbms_hprof程序包的使用和安装。以及通过一个小实例来验证我们对pl/sql程序的调用。
对pl/sql而言，Oracle11g推出的这个新特性可以帮助我们解决PL/SQL代码调优问题，解决一些层次性能评定问题。
在下篇中，我们打算着重介绍一些生成评测结果的观察方法。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-702694/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-702694/
扫一扫
800
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
