今天迎来本命年的工作第一天，学习氛围还算不错，充满神奇的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的使用优先于所用的时间。