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程式碼不應該忽略，相反應該調整到最佳的結果.