Runstats

最新推荐文章于 2023-08-21 11:01:52 发布

congga5951

最新推荐文章于 2023-08-21 11:01:52 发布

阅读量238

点赞数

整理一下tom大师的runstats检查工具包的脚本和使用示例，便于日后查看。运行runstats测试工具必须有访问V$STATNAME,V$MYSTAT,V$LATCH的权限，还必须有对SYS.V_$STATNAME,SYS.V_$MYSTAT,SYS.V_$LATCH的直接权限（不是通过角色授权）。

1.授权：

SQL> grant select on sys.v_$timer to jhondom;

Grant succeeded.

SQL> grant select on v_$mystat to jhondom;

Grant succeeded.

SQL> grant select on sys.v_$statname to jhondom;

Grant succeeded.

SQL> grant select on sys.v_$latch to jhondom;

Grant succeeded.

SQL> grant create view to jhondom;

Grant succeeded.

2.安装runstats包：

以jhondom用户登录执行以下操作：

(1)创建stats视图

SQL> create or replace view stats
2 as
3 select 'STAT..' || a.name name, b.value
4 from v$statname a, v$mystat b
5 where a.statistic# = b.statistic#
6 union all
7 select 'LATCH.' || name, gets
8 from v$latch;

View created.

(2)创建run_stats临时表

SQL> create global temporary table run_stats
2 ( runid varchar2(15),
3 name varchar2(80),
4 value int
5 )on commit preserve rows;

Table created.

(3)创建runstats包头

SQL> create or replace package runstats
2 as
3 procedure rs_start;
4 procedure rs_middle;
5 procedure rs_stop( p_difference_threshold in number default 0);
6 end;
7 /

Package created.

p_difference_threshold参数的含义是：前后两个相同的项的统计结果相差的数目的绝对值大于等于该参数设定的值，该统计项才会被显示。默认值是0表示所有的结果都显示。

(4)创建runstats包体

SQL> create or replace package body runstats
2 as
3 g_start number;
4 g_run1 number;
5 g_run2 number;
6 procedure rs_start
7 is
8 begin
9 delete from run_stats;
10 insert into run_stats
11 select 'before', stats.* from stats;
12 g_start := dbms_utility.get_time;
13 end;
14 procedure rs_middle
15 is
16 begin
17 g_run1 := (dbms_utility.get_time-g_start);
18 insert into run_stats
19 select 'after 1', stats.* from stats;
20 g_start := dbms_utility.get_time;
21 end;
22 procedure rs_stop(p_difference_threshold in number default 0)
23 is
24 begin
25 g_run2 := (dbms_utility.get_time-g_start);
26 --add a line here to avoid ora-20000
27 dbms_output.enable(480000);
28 dbms_output.put_line
29 ( 'Run1 ran in ' || g_run1 || ' hsecs' );
30 dbms_output.put_line
31 ( 'Run2 ran in ' || g_run2 || ' hsecs' );
32 dbms_output.put_line
33 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
34 '% of the time' );
35 dbms_output.put_line( chr(9) );
36 insert into run_stats
37 select 'after 2', stats.* from stats;
38 dbms_output.put_line
39 ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
40 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );
41 for x in
42 ( select rpad( a.name, 30 ) ||
43 to_char( b.value-a.value, '999,999,999' ) ||
44 to_char( c.value-b.value, '999,999,999' ) ||
45 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
46 from run_stats a, run_stats b, run_stats c
47 where a.name = b.name
48 and b.name = c.name
49 and a.runid = 'before'
50 and b.runid = 'after 1'
51 and c.runid = 'after 2'
52 -- and (c.value-a.value) > 0
53 and abs( (c.value-b.value) - (b.value-a.value) )
54 > p_difference_threshold
55 order by abs( (c.value-b.value)-(b.value-a.value))
56 ) loop
57 dbms_output.put_line( x.data );
58 end loop;
59 dbms_output.put_line( chr(9) );
60 dbms_output.put_line
61 ( 'Run1 latches total versus runs -- difference and pct' );
62 dbms_output.put_line
63 ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
64 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );
65 for x in
66 ( select to_char( run1, '999,999,999' ) ||
67 to_char( run2, '999,999,999' ) ||
68 to_char( diff, '999,999,999' ) ||
69 to_char( round( run1/run2*100,2 ), '99,999.99' ) || '%' data
70 from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
71 sum( (c.value-b.value)-(b.value-a.value)) diff
72 from run_stats a, run_stats b, run_stats c
73 where a.name = b.name
74 and b.name = c.name
75 and a.runid = 'before'
76 and b.runid = 'after 1'
77 and c.runid = 'after 2'
78 and a.name like 'LATCH%'
79 )
80 ) loop
81 dbms_output.put_line( x.data );
82 end loop;
83 end;
84 end;
85 /

Package body created.

至此runstats包安装完毕。

3.使用runstats包进行测试：

(1)创建测试表：

SQL> create table t(id number);

Table created.

(2)记录开始状态：
SQL> exec runstats.rs_start;