oracle dbms_profiler,Oracle’s DBMS_Profiler:PL/SQL 性能调整 (转)

Oracle’s DBMS_Profiler:PL/SQL 性能调整 (转)[@more@]

’s MS_Profiler:调整

:namespace prefix = o ns = "urn:schemas--com::office" />

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:amcall_profiler.sql

Profiler started

Invalid tape loaded

PL/SQL procedure succesully completed.

Profiler stopped

Profiler flushed

runid:8

3.  评估执行时间:

执行eavluate_profiler_results.sql脚本,得到时间统计

SQL> @d:amevaluate_profiler_results.sql

Enter value for runid: 8

Enter value for name: am_perf_chk

Enter value for owner: tt

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 ed.

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包不会自动安装，请用profload.sql脚本创建它.用一个较大的或一个单独的，创建统计信息的表。如果

用如SYS用户创建，则给其它用户授予DML权限，并且对这些表创建一个共同的简写名.

创建表的如下:

PLSQL_PROFILER_RUNS表:PL/SQL配置的运行细节.

PLSQL_PROFILER_UNITS表:运行中每一个库单元的信息.

PLSQL_PROFILER_DATA表:所有配置文件运行时的数据累积.

PLSQL_PROFILER_RUNNUMBER序列提供了RUNID

运行和解释配置数据

ORACLE提供了三个表来统计，填充RUNID。有许多第三方的工具可以提供自定义的基于这些数据的报告，ORACLE提供profrep.sql脚本评估数据(在plsqldemo目录下),下面的两个简单脚本就是上面用到的，用来检查单元的执行时间.执行时间以毫秒存储

-----------------------------------------------------------

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_ 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代码不应该忽略，相反应该调整到最佳的结果.