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

[1] [2] [3]  下一页