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代碼不應該忽略，相反應該調整到最佳的結果.
關鍵字：PL/SQL、Oracle

【 頂部 】 【 關閉 】