runstats_pkg PACKAGE
runstats_pkg能对同一件事情的两个不同方法进行比较，得出孰优孰劣的结果。
runstats只是测量3个要素
1、wall lock or elapsed_time,即墙上时钟或耗用时间
2、系统统计结果：会并排的显示每个方法去做某件事情（例如解析调用）的次数，并展示二者之差。
3、闩定（latching）：这是这个报告的关键输出。
闩（latch）是一种轻量级锁。锁是一种串行话设备。而串行化设备不支持并发。因此，较少的闩，能获得较好的并发扩展。
runstats最好独立使用，在一个单用户数据库上运行。我们会测量各种运行方式的结果已经闩定活动。因此尽量避免其他任务对系统的负载或闩定产生影响。
runstats_pkg的建立
--如果测试用户没有这几个视图的查询权限以sys登陆，取得四个视图的权限.
grant select on v_$latch to user_name;
grant select on v_$mystat to user_name;
grant select on V_$timer to user_name;
grant select on v_$statname to user_name;
--可以以USER_NAME 登陆创建自己的统计视图
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
union all
select 'STAT...Elapsed time', hsecs from v$timer;--注：v$为v_$的同义词，v_$才是实际的底层视图，之前授权的就是v_$
一旦建立视图，接下来需要创建一个小表，来收集统计结果。
--创建run_stats临时表
create global temporary table run_stats
( runid varchar2(15),
name varchar2(80),
value int
)on commit preserve rows;
--创建runstat包
create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0); --控制打印量，默认输出全部
end;
create or replace package body runstats_pkg
as
g_start number;
g_run1 number;
g_run2 number;
procedure rs_start
is
begin
delete from run_stats;
insert into run_stats select 'before',stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_cpu_time-g_start);
insert into run_stats select 'after 1', stats.* from stats;
g_start := dbms_utility.get_cpu_time;
end;
procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_cpu_time-g_start);
dbms_output.put_line('Run1 ran in '||g_run1||' cpu hsecs');
dbms_output.put_line('Run2 ran in '||g_run2||' cpu hsecs');
if (g_run2 <> 0)
then
dbms_output.put_line('Run 1 ran in '||round(g_run1/g_run2*100,2)||' % of the time');
end if;
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',12)||lpad('Run2',12)||lpad('Diff',12));
for x in
(select rpad(a.name,30)||to_char(b.value-a.value,'999,999,999')||to_char(c.value-b.value,'999,999,999')||to_char((c.value-b.value)-(b.value-a.value),'999,999,999') 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 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
( 'Run1 latches total versus runs -- difference and pct' );
dbms_output.put_line
( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
for x in
( select to_char( run1, '999,999,999' ) ||
to_char( run2, '999,999,999' ) ||
to_char( diff, '999,999,999' ) ||
to_char( round( run1/run2*100,2 ), '99,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;
end;
/测试使用：--创建测试表
SQL> create table test_a (id_no number);
Table created.
Elapsed: 00:00:00.06
SQL> create table test_b (id_no number);
Table created.
Elapsed: 00:00:00.03
--首先执行rs_start。清空保存统计结果的表，并填入“上一次”得到的统计结果和闩定信息，然后获得当前的定时器值，这是一种时钟，用于计算耗费时间（精确到百万分之一秒）。
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
SQL> begin
2 for i in 1..10000 loop
3 insert into test_a values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.45
---记录下一次运行的开始时间。
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> begin
2 for i in 1..10000 loop
3 execute immediate 'insert into test_b values ('||i||')';
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.92
--最后运行runstat_pkg.rs_stop生成报表。
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> exec runstats_pkg.rs_stop;
Run1 ran in 311 cpu hsecs
Run2 ran in 1583 cpu hsecs
Run 1 ran in 19.65 % of the time
Name Run1 Run2 Diff
LATCH.kwqbsn:qsga 5 4 -1
LATCH.KWQMN job cache list lat 0 1 1
LATCH.JS broadcast autostart l 2 3 1
LATCH.space background state o 0 1 1
LATCH.resmgr:actses change sta 2 1 -1
LATCH.Shared B-Tree 6 5 -1
LATCH.Event Group Locks 13 14 1
LATCH.job workq parent latch 3 4 1
LATCH.hash table modification 1 2 1
LATCH.compile environment latc 7 8 1
LATCH.kcbtsemkid latch 0 1 1
LATCH.FAL Queue 5 4 -1
LATCH.redo copy 1 2 1
LATCH.transaction allocation 12 11 -1
LATCH.dummy allocation 15 16 1
LATCH.post/wait queue 52 51 -1
LATCH.process allocation 15 14 -1
LATCH.session switching 8 7 -1
LATCH.process group creation 10 9 -1
LATCH.client/application info 26 25 -1
LATCH.resmgr:free threads list 16 15 -1
LATCH.channel handle pool latc 11 10 -1
LATCH.ksv allocation latch 6 5 -1
LATCH.OS process: request allo 10 9 -1
LATCH.ksz_so allocation latch 10 9 -1
LATCH.queued dump request 0 1 1
STAT..session cursor cache cou 0 -1 -1
STAT..heap block compress 9 8 -1
STAT..free buffer requested 107 108 1
STAT..redo buffer allocation r 0 1 1
STAT..redo log space requests 0 1 1
STAT..messages sent 2 1 -1
STAT..db block gets 10,309 10,310 1
STAT..db block gets from cache 10,309 10,310 1
STAT..db block changes 20,252 20,253 1
LATCH.PL/SQL warning settings 13 15 2
LATCH.resmgr:actses change gro 7 5 -2
LATCH.job_queue_processes free 4 6 2
LATCH.archive process latch 15 13 -2
LATCH.managed standby latch 5 3 -2
LATCH.alert log latch 0 2 2
LATCH.reservation so alloc lat 0 2 2
LATCH.list of block allocation 5 7 2
LATCH.KTF sga latch 2 0 -2
LATCH.sort extent pool 6 4 -2
LATCH.lob segment hash table l 1 3 2
LATCH.ksv class latch 5 3 -2
STAT..calls to kcmgas 33 31 -2
STAT..redo ordering marks 30 28 -2
LATCH.space background task la 152 149 -3
LATCH.archive control 2 5 3
LATCH.ksuosstats global area 15 12 -3
LATCH.enqueues 0 3 3
LATCH.OS process 46 43 -3
STAT..workarea executions - op 2 5 3
STAT..redo log space wait time 0 3 3
LATCH.qmn task queue latch 20 16 -4
LATCH.parameter list 16 12 -4
LATCH.job_queue_processes para 33 29 -4
LATCH.parallel query alloc buf 21 17 -4
LATCH.Reserved Space Latch 0 4 4
LATCH.ktm global data 5 9 4
STAT..cluster key scans 1 5 4
STAT..cluster key scan block g 1 5 4
LATCH.JS mem alloc latch 5 0 -5
LATCH.JS queue access latch 6 1 -5
LATCH.sequence cache 6 11 5
LATCH.session idle bit 62 67 5
LATCH.object stats modificatio 1 6 5
LATCH.message pool operations 5 10 5
STAT..sorts (memory) 0 5 5
LATCH.session state list latch 14 20 6
LATCH.resmgr:active threads 45 39 -6
LATCH.session timer 47 41 -6
LATCH.KMG MMAN ready and start 47 41 -6
LATCH.Change Notification Hash 47 41 -6
LATCH.longop free list parent 48 42 -6
STAT..sorts (rows) 0 6 6
LATCH.messages 1,461 1,468 7
STAT..workarea memory allocate 0 -8 -8
LATCH.FIB s.o chain latch 0 8 8
LATCH.In memory undo latch 129 137 8
STAT..change write time 47 38 -9
LATCH.parameter table manageme 30 39 9
LATCH.session allocation 41 50 9
LATCH.ASM db client latch 94 83 -11
STAT..undo change vector size 684,144 684,156 12
STAT..non-idle wait count 4 18 14
LATCH.FOB s.o list latch 22 8 -14
LATCH.Real-time plan statistic 51 35 -16
STAT..rows fetched via callbac 0 17 17
LATCH.threshold alerts latch 22 2 -20
STAT..bytes received via SQL*N 1,600 1,625 25
STAT..CCursor + sql area evict 0 25 25
LATCH.active checkpoint queue 54 80 26
LATCH.object queue header heap 55 81 26
STAT..index fetch by key 1 28 27
STAT..index scans kdiixs1 2 31 29
LATCH.file cache latch 34 3 -31
LATCH.mostly latch-free SCN 54 85 31
LATCH.lgwr LWN SCN 54 85 31
STAT..opened cursors cumulativ 10,011 10,043 32
LATCH.OS process allocation 310 273 -37
LATCH.active service list 311 273 -38
STAT..table fetch continued ro 0 39 39
STAT..hot buffers moved to hea 79 119 40
STAT..execute count 10,009 10,051 42
LATCH.Consistent RBA 15 60 45
STAT..scheduler wait time 0 52 52
STAT..calls to get snapshot sc 7 61 54
STAT..non-idle wait time 0 55 55
LATCH.simulator lru latch 7 67 60
LATCH.channel operations paren 696 629 -67
LATCH.cache buffers lru chain 120 198 78
LATCH.checkpoint queue latch 975 1,054 79
STAT..consistent gets - examin 25 128 103
LATCH.DML lock allocation 42 150 108
LATCH.hash table column usage 0 117 117
LATCH.JS queue state obj latch 1,008 864 -144
LATCH.redo writing 198 348 150
STAT..table fetch by rowid 4 159 155
LATCH.undo global data 206 373 167
LATCH.redo allocation 162 335 173
LATCH.call allocation 32 219 187
LATCH.shared pool sim alloc 1 225 224
LATCH.simulator hash latch 845 1,078 233
STAT..no work - consistent rea 7 279 272
LATCH.object queue header oper 391 686 295
LATCH.resmgr:resource group CP 5 373 368
LATCH.resmgr:schema config 226 600 374
LATCH.resmgr group change latc 205 582 377
LATCH.SQL memory manager worka 3,184 2,752 -432
STAT..buffer is not pinned cou 14 473 459
STAT..redo size 2,458,952 2,459,908 956
STAT..parse time cpu 0 991 991
STAT..recursive cpu usage 240 1,418 1,178
STAT..parse time elapsed 3 1,229 1,226
STAT..CPU used when call start 318 1,582 1,264
STAT..CPU used by this session 311 1,587 1,276
STAT..DB time 355 1,895 1,540
STAT...Elapsed time 14,102 12,318 -1,784
STAT..sql area evicted 4 9,924 9,920
STAT..session cursor cache hit 10,005 27 -9,978
STAT..calls to kcmgcs 56 10,055 9,999
STAT..enqueue releases 22 10,030 10,008
STAT..enqueue requests 23 10,031 10,008
STAT..parse count (hard) 4 10,012 10,008
STAT..parse count (total) 4 10,032 10,028
STAT..consistent gets from cac 31 10,301 10,270
STAT..consistent gets from cac 59 10,433 10,374
STAT..consistent gets 59 10,433 10,374
STAT..session logical reads 10,368 20,743 10,375
LATCH.enqueue hash chains 2,500 22,543 20,043
LATCH.cache buffers chains 56,088 85,038 28,950
STAT..recursive calls 10,035 40,423 30,388
LATCH.shared pool simulator 13 40,242 40,229
STAT..session pga memory 0 65,536 65,536
LATCH.row cache objects 1,972 94,303 92,331
LATCH.shared pool 10,895 455,448 444,553
STAT..logical read bytes from 84,934,656 169,926,656 84,992,000
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
83,602 712,059 628,457 11.74%
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
