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脚本评估数据（在<oracle_home>\plsql\demo\目录下）,下面的两个简单脚本就是上面用到的，用来检查程序单元的执行时间.执行时间以毫秒存储
　　
　　-----------------------------------------------------------
　　Script: call_profiler.sql
　　-----------------------------------------------------------
　　
　　set head off
　　
　　set pages 0
　　
　　select decode(dbms_profiler.start_profiler, '0', 'Profiler started', 'Profiler error')
　　
　　from　 dual;
　　
　　--< place your routine in the below block >--
　　
　　declare
　　
　　l_status varchar2(200);
　　
　　begin
　　
　　am_perf_chk(2, l_status);
　　
　　dbms_output.put_line(l_status);
　　
　　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
　　
　　-----------------------------------------------------------
　　Script: evaluate_profiler_results.sql
　　-----------------------------------------------------------
　　
　　undef runid
　　
　　undef owner
　　
　　undef name
　　
　　set verify off
　　
　　select s.line "Line", p.total_occur "Occur", p.total_time "Msec", s.text "Text"
　　
　　from　 all_source s, (select u.unit_owner, u.unit_name, u.unit_type, d.line#,
　　
　　d.total_occur, d.total_time/1000000 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
　　
　　结论
　　
　　DBMS_PROFILER是非常强大的工具，其一就是可以识别PL/SQL的性能问题.这个工具最好用在开发时期,用来调整基于各种应用的情景的代码，它也能用很好的调整已在使用中的例程并且采取显而易见的时间去执行。总之，这个工具可以给每一行代码给予性能统计，它可以帮助我们评估和调整到一个出色的水平,当检查SQL语句的性能问题时，PL/SQL代码不应该忽略，相反应该调整到最佳的结果.

查看本文来源