tom的runstats_pkg工具包關於批次插入和繫結變數分析-01

今天迎來本命年的工作第一天，學習氛圍還算不錯，充滿神奇的2012這一年自身又將有著怎樣的變化，其實真是不得而知。給大家送一個遲來的新年快樂，當然今年可能以後很長一段時間都是堅持 習慣學習oracle，然後期待北漂尋求一份跟oracle開發管理相關的工作，認真的做下去，也期待遇見一個適齡的girl，一個人著實有點孤獨。嗚嗚！

Tom開發的一個用於比較統計方法的工具runstats_pkg

先建立檢視stats

create or replace view stats as

select 'STAT...'||a.name name,b.value from

v$statname a,v$mystat b

where a.statistic#=b.statistic#

union all

select 'LATCH...'||name,gets from v$latch

建立一個session臨時表

Create global temporary table sys.run_stats

(runid varchar2(40),

Name varchar2(80),

Value number)

On commit preserve rows;

--Runstats_pkg包頭：

create or replace package runstats_pkg

as

procedure rs_start;

procedure rs_middle;

procedure rs_stop(p_difference_threshold in number default 0);

end;

--Runstats_pkg包體：

create or replace package body runstats_pkg

as

g_start number;

g_run1 number;

g_run2 number;

procedure rs_start

as

begin

delete from run_stats;

insert into run_stats select 'before',stats.* from stats;

g_start:=dbms_utility.get_time;

end rs_start;

procedure rs_middle

as

begin

g_run1:=(dbms_utility.get_time-g_start);--執行第一中方法後的時間差

insert into run_stats select 'after 1',stats.* from stats;

g_start:=dbms_utility.get_time;--記錄當前時間g_start

end rs_middle;

procedure rs_stop(p_difference_threshold in number default 0)

as

begin

g_run2:=(dbms_utility.get_time-g_start);

dbms_output.put_line('Run1 ran in '||g_run1);

dbms_output.put_line('Run1 ran in '||g_run2);

dbms_output.put_line('run 1 ran in'||round(g_run1/g_run2*100,2)||'% of the time');

dbms_output.put_line(chr(9));

insert into run_stats select 'after 2',stats.* from stats;

dbms_output.put_line(rpad('Name',30)||lpad('Run1',10)||lpad('Run2',10)||lpad('Diff',10));

for x in (

select rpad(a.name,30)||

to_char(b.value-a.value,'9.999.999')||

to_char(c.value-b.value,'9.999.999')||

to_char(round(run1/run2*100,2),'999,99')||'%'data

from run_stats a,run_stats b,run_stats c

where a.name=b.name

and b.name=c.name

and a.runid='before'

and b.runid='after 1'

and c.runid='after 2'

and (c.value-a.value)>0

and abs((c.value-b.value)-(b.value-a.value))>p_difference_threshold order by abs((c.value-b.value)-(b.value-a.value))

) loop

dbms_output.put_line(x.data);

end loop;

dbms_output.put_line(chr(9));

dbms_output.put_line('Latches :');

dbms_output.put_line(lpad('Run1',10)||lpad('Run2',10)||lpad('Diff',10)||lpad('PCT',8));

for x in

(select to_char(run1,'9.999.999')||

to_char(run2,'9.999.999')||

to_char(diff,'9.999.999')||

to_char(round(run1/run2*100,2),'999.99')data

from

(select sum(b.value-a.value) run1,sum(c.value-b.value) run2,sum((c.value-b.value)-(b.value-a.value))diff

from run_stats a,run_stats b,run_stats c

where a.name=b.name

and b.name=c.name

and a.runid='before'

and b.runid='after 1'

and c.runid='after 2'

and a.name like 'LATCH%'

)

) loop

dbms_output.put_line(x.data);

end loop;

end rs_stop;

end;

利用上述runstats_pkg工具包可以用於區分統計兩種方法產生的時間差和latch使用情況，由於latch是一種低階的序列化鎖，用於保護記憶體不被同時修改，獲取和釋放都非常短，但生產中這種低階的序列化鎖很多時候是系統效能瓶頸所在，某些時候我們甚至更關注latch的使用優先於所用的時間。

[@more@]