0.權(quán)限設(shè)置

grant select on sys.v_$mystat to tang;

grant select on sys.v_$latch to tang;
grant select on sys.v_$timer to tang;

grant select on v_$statname to tang;

1.創(chuàng)建runstats所需的表和包等

sys@sec> @runstats

sys@sec> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

2.創(chuàng)建測試用表：
sys@sec> conn t/tang
sec_test@sec> create table t1 as select * from dba_objects;

Table created.

sec_test@sec> create table t2 as select * from dba_objects where 1=2;

Table created.

sec_test@sec> create table t3 as select * from dba_objects where 1=2;

Table created.

創(chuàng)建后的測試表數(shù)據(jù)如下：
sec_test@sec> select count(*) from t1;

? COUNT(*)
----------
???? 11334

sec_test@sec> select count(*) from t2;

? COUNT(*)
----------
???????? 0

sec_test@sec> select count(*) from t3;

? COUNT(*)
----------
???????? 0


3.看一看使用這個工作比較的結(jié)果
使用方法：
sys@sec> /*
sys@sec> exec runStats_pkg.rs_start;
sys@sec> exec runStats_pkg.rs_middle;
sys@sec> exec runStats_pkg.rs_stop;
sys@sec> */

1）先實行runStats_pkg.rs_start
sys@sec> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

2）執(zhí)行第一種插入方法：一次性插入
sys@sec> insert into sec_test.t2 select * from sec_test.t1;

11334 rows created.

sys@sec> commit;

Commit complete.

3）再執(zhí)行runStats_pkg.rs_middle
sys@sec> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

4）執(zhí)行第二種插入方法：逐行插入數(shù)據(jù)
SQL>
begin
?for x in (select * from t1 )
? loop
??? insert into t3 values x;
??? end loop;
??? commit;
??? end;

/

PL/SQL procedure successfully completed.

5）查看最后的比較結(jié)果，這里僅僅顯示出數(shù)值大于100000的信息
SQL> exec runStats_pkg.rs_stop(100000);
Run1 ran in 4673 hsecs
Run2 ran in 19254 hsecs
run 1 ran in 24.27% of the time
?? ?
Name????????????????????????????????? Run1??????? Run2??????? Diff
LATCH.shared pool??????????????????? 6,121???? 107,549???? 101,428
STAT...session uga memory????????? 130,976?????????? 0??? -130,976
STAT...session pga memory max????? 245,088?????????? 0??? -245,088
STAT...undo change vector size???? 291,280?????? 6,440??? -284,840
STAT...undo change vector size???? 291,280?????? 3,216??? -288,064
STAT...session pga memory????????? 245,088??? -131,072??? -376,160
LATCH.cache buffers chains???????? 258,977???? 853,810???? 594,833
STAT...session pga memory????????? 245,088??? -720,896??? -965,984
STAT...session pga memory max????? 245,088?? 1,310,720?? 1,065,632
STAT...session uga memory max????? 130,976?? 1,561,496?? 1,430,520
LATCH.cache buffers chains???????? 258,977?? 1,704,809?? 1,445,832
STAT...redo size???????????????? 8,861,664?????? 8,544? -8,853,120
STAT...redo size???????????????? 8,861,664?????? 4,268? -8,857,396
STAT...logical read bytes from 117,669,888?? 3,727,360-113,942,528
STAT...logical read bytes from 117,669,888???? 548,864-117,121,024
?? ?
Run1 latches total versus runs -- difference and pct
??????? Run1??????? Run2??????? Diff?????? Pct
???? 637,504?? 3,165,229?? 2,527,725???? 20.14%
PL/SQL procedure successfully completed




【附錄】runstats.sql內(nèi)容
set echo on

drop table run_stats;
create global temporary table run_stats
( runid varchar2(15),
? name varchar2(80),
? value int )
on commit preserve rows;

grant select any table to tang;
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;


delete from run_stats;
commit;

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_time;
end;

procedure rs_middle
is
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;

end;

procedure rs_stop(p_difference_threshold in number default 0)
is
begin
??? g_run2 := (dbms_utility.get_time-g_start);

??? dbms_output.put_line
??? ( 'Run1 ran in ' || g_run1 || ' hsecs' );
??? dbms_output.put_line
??? ( 'Run2 ran in ' || g_run2 || ' hsecs' );
??? 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', 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 (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
??? ( '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;
/

/*
exec runStats_pkg.rs_start;
exec runStats_pkg.rs_middle;
exec runStats_pkg.rs_stop;
*/

總結(jié)

以上是生活随笔為你收集整理的ORACLE 效率测试小工具 Runstats的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。