Oracle’s DBMS_Profiler:PL/SQL 性能调整
** DBMS_PROFILER ** ** 包举例 **
** ** 下面是我提供的怎样使用配置的简单例子 , 运行配置文件来测试下面例程的性能 . 例程用到的自定义脚本紧随其后 .
1. 创建过程 .
create or replace procedure am_perf_chk (pi_seq in number,
pio_status in out nocopy varchar2) is
l_dat date := sysdate;
begin
if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
pio_status := 'OK';
else
pio_status := 'Invalid tape loaded';
end if;
exception
when others then
pio_status := 'Error in am_perf_chek';
end;
2. 用配置文件调用例程
替换上面的例程, 执行call_profiler.sql脚本(脚本代码参见下面),传入pi_seq=2
SQL> @d:\am\call_profiler.sql
Profiler started
Invalid tape loaded
PL/SQL procedure successfully completed.
Profiler stopped
Profiler flushed
runid:8
3. 评估执行时间 :
执行 eavluate_profiler_results.sql 脚本 , 得到时间统计
SQL> @d:\am\evaluate_profiler_results.sql
Enter value for runid: 8
Enter value for name: am_perf_chk
Enter value for owner: scott
Line Occur Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
1 procedure am_perf_chk (pi_seq in number,
2 pio_status in out nocopy varchar2) is
3 2 43.05965 l_dat date := sysdate;
4 begin
5 1 86.35732 if trunc(l_dat) = '21-sep-02' and pi_seq = 1 then
6 0 0 pio_status := 'OK';
7 else
8 1 8.416151 pio_status := 'Invalid tape loaded';
9 end if;
10 exception
11 when others then
12 0 0 pio_status := 'Error in am_perf_chek';!
13 1 2.410361 end;
13 rows selected.
Code% coverage
--------------
66.6666667
4. 正如你看到的 , 第三行执行时间提高到 86 毫秒 . 但是改变 if 语句 , 重新执行上面的过程 , 将会得到新的结果 :
Line Occur Msec Text
---------- ---------- ---------- -------------------------------------------------------------------
1 procedure am_perf_chk (pi_seq in number,
2 pio_status in out nocopy varchar2) is
3 2 17.978816 l_dat date := sysdate;
4 begin
5 1 8.419503 if pi_seq = 1 and trunc(l_dat) = '21-sep-02' then
6 0 0 pio_status := 'OK';
7 else
8 1 7.512684 pio_status := 'Invalid tape loaded';
9 end if;
10 exception
11 when others then
12 0 0 pio_status := 'Error in !am_perf_chek';
13 1 .731657 end;
13 rows selected.
Code% coverage
--------------
66.6666667
5. 正如你看到的 , 这种情境下第三行执行时间从 86 毫秒减少到 8 毫秒 , 多余的时间是由于内置 trunc() 函数引起 ., 这种情境下如果第一个条件为 false, 则不会执行 trunc() 函数 . 这仅仅是个简单的例子 , 当你测试的例程越大 , 你面临的挑战更大 .
这个配置结果也证明了执行期间代码被覆盖多少行 , 从而让我们知道处于性能监视中的代码范围。如果任何 PL/SQL 块性能出现问题，它也能提炼出各种不同情景的正在在执行的代码并检查配置结果，从而查明问题所在。
6. 对于一个特定的情景，如果执行一段特殊的代码段，可以得到合理的分析，即使代码根本一点都不能运行。
** 环境的创建 **
** ** 默认安装或数据库的创建状态下， DBMS_PROFILER 包不会自动安装，请 DBA 用 profload.sql 脚本创建它 . 用一个权限较大的或一个单独的用户，创建存储统计信息的表。如果
用如 SYS 用户创建，则给其它用户授予 DML 权限，并且对这些表创建一个共同的简写名 .
创建表的如下 :
PLSQL_PROFILER_RUNS 表 :PL/SQL 配置的运行细节 .
PLSQL_PROFILER_UNITS 表 : 运行中每一个库单元的信息 .
PLSQL_PROFILER_DATA 表 : 所有配置文件运行时的数据累积 .
PLSQL_PROFILER_RUNNUMBER 序列提供了 RUNID
** 运行和解释配置数据 **
** ORACLE ** 提供了三个表来统计，填充 RUNID 。有许多第三方的工具可以提供自定义的基于这些数据的报告， ORACLE 提供 profrep.sql 脚本评估数据（在
1<oracle_home>\plsql\demo\ 目录下） , 下面的两个简单脚本就是上面用到的，用来检查程序单元的执行时间 . 执行时间以毫秒存储
2
3
4 **-----------------------------------------------------------**
5
6
7 **Script: call_profiler.sql**
8
9
10 **-----------------------------------------------------------**
11
12
13 set head off
14
15
16 set pages 0
17
18
19 select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
20
21
22 from dual;
23
24
25
26
27
28 --< place your routine in the below block >--
29
30
31 declare
32
33
34 l_status varchar2(200);
35
36
37 begin
38
39
40 am_perf_chk(2, l_status);
41
42
43 dbms_output.put_line(l_status);
44
45
46 end;
47
48
49 /
50
51
52
53
54
55 select decode(dbms_profiler.stop_profiler, '0', 'Profiler stopped', 'Profiler error')
56
57
58 from dual;
59
60
61 select decode(dbms_profiler.flush_data, '0', 'Profiler flushed', 'Profiler error')
62
63
64 from dual;
65
66
67 select 'runid:' || plsql_profiler_runnumber.currval
68
69
70 from dual;
71
72
73 set head on
74
75
76 set pages 200
77
78
79
80
81
82 **-----------------------------------------------------------**
83
84
85 **Script: evaluate_profiler_results.sql**
86
87
88 **-----------------------------------------------------------**
89
90
91 undef runid
92
93
94 undef owner
95
96
97 undef name
98
99
100 set verify off
101
102
103 select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
104
105
106 from all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
107
108
109 d.total_occur, d.total_time/1000000 total_time
110
111
112 from plsql_profiler_data d, plsql_profiler_units u
113
114
115 where u.runid = &&runid
116
117
118 and u.runid = d.runid
119
120
121 and u.unit_number = d.unit_number) p
122
123
124 where s.owner = p.unit_owner (+)
125
126
127 and s.name = p.unit_name (+)
128
129
130 and s.type = p.unit_type (+)
131
132
133 and s.line = p.line# (+)
134
135
136 and s.name = upper('&&name')
137
138
139 and s.owner = upper('&&owner')
140
141
142 order by s.line;
143
144
145 select exec.cnt/total.cnt * 100 "Code% coverage"
146
147
148 from (select count(1) cnt
149
150
151 from plsql_profiler_data d, plsql_profiler_units u
152
153
154 where d.runid = &&runid
155
156
157 and u.runid = d.runid
158
159
160 and u.unit_number = d.unit_number
161
162
163 and u.unit_name = upper('&&name')
164
165
166 and u.unit_owner = upper('&&owner')) total,
167
168
169 (select count(1) cnt
170
171
172 from plsql_profiler_data d, plsql_profiler_units u
173
174
175 where d.runid = &&runid
176
177
178 and u.runid = d.runid
179
180
181 and u.unit_number = d.unit_number
182
183
184 and u.unit_name = upper('&&name')
185
186
187 and u.unit_owner = upper('&&owner')
188
189
190 and d.total_occur > 0) exec;
191
192
193 undef runid
194
195
196 undef owner
197
198undef name
199
200** 结论 **
201
202** ** DBMS_PROFILER 是非常强大的工具，其一就是可以识别 PL/SQL 的性能问题 . 这个工具最好用在开发时期 , 用来调整基于各种应用的情景的代码，它也能用很好的调整已在使用中的例程并且采取显而易见的时间去执行。总之，这个工具可以给每一行代码给予性能统计，它可以帮助我们评估和调整到一个出色的水平 , 当检查 SQL 语句的性能问题时， PL/SQL 代码不应该忽略，相反应该调整到最佳的结果 .</oracle_home>