oracle信息统计脚本runstats的创建与验证
runstats是《Oracle Database 9i/10g/11g编程艺术：深入数据库体系结构》作者编写的一个工具，能对做同一件事情的两个不同方法进行比较，得出孰优孰劣的结果。我们只需要提供两个不同的方法，余下的事情都由runstats负责。runstats只负责测量3个要素：
- 墙上时钟或耗时时间：知道墙上时钟或耗时时间很有用，不过这不是最重要的信息。
- 系统统计结果：会并排地i显示每个方法做某件事（如执行一个解析调用）的次数，并展示出两者之差
- 闩定（latching）：这是这个报告的关键输出。
要使用runstats，需要能访问几个V$视图，并创建一个表来存储统计结果，还要创建runstats包。为此，需要访问4个V$表（就是那些神奇的动态性能表）：V$STATNAME、V$MYSTAT和V$LATCH和V$TIMER。这四个表其实是别名，真正对象的名称应为V_$STATNAME、V_$MYSTAT、 V_$LATCH、 V_$TIMER，并且都是在sys账户下。如果其他账户要访问这四张表， 需要进行授权。我们需要再scott下进行操作，因此需要将这四张表的select权限授予给scott账户。下面进行具体操作。
1在sys账户下
1.1将V_$表的查询权限授权给scott
View Code
--在sys账户下授权视图查询权限给scott grant SELECT on SYS.v_$statname to "SCOTT" ; grant SELECT on SYS.v_$mystat to "SCOTT" ; grant SELECT on SYS.v_$latch to "SCOTT" ; grant SELECT on SYS.v_$timer to "SCOTT" ;
2在scott账户下
2.1查询V_$表
View Code
--在scott账户下测试视图查询，发现不能使用别名查询，只能使用视图真名 select * from SYS.v_$statname--成功 select * from SYS.v$statname--失败
2.2创建视图
View Code
--在scott账户下创建视图 create or replace view stats as select 'STAT...' || a.name name, b.value from SYS.v_$statname a, SYS.v_$mystat b where a.statistic# = b.statistic# union all select 'LATCH.' || name, gets from SYS.v_$latch union all select 'STAT...Elapsed Time', hsecs from SYS.v_$timer;
2.3创建信息收集表
View Code
--创建信息收集表 create global temporary table run_stats ( runid varchar2(15), name varchar2(80), value int ) on commit preserve rows;
2.4创建runstats包
View Code
--创建包 create or replace package runstats_pkg as procedure rs_start; procedure rs_middle; procedure rs_stop( p_difference_threshold in number default 0 ); end; /
2.5创建包体
View Code
--创建包体 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/decode( run2, 0, to_number(0), 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; /
3.使用runstats
3.1创建表T
View Code
--创建表 create table t(x int);
3.2创建存储过程proc1，使用了一条带绑定变量的SQL语句
View Code
--创建存储过程proc1 create or replace procedure proc1 as begin for i in 1 .. 10000 loop execute immediate 'insert into t values(:x)' using i; end loop; end; /
3.3创建存储过程proc2，分别为要插入的每一行构造一条独立的SQL语句
View Code
--创建存储过程proc2 create or replace procedure proc2 as begin for i in 1 .. 10000 loop execute immediate 'insert into t values('||i||')'; end loop; end; /
3.4使dbms_output.put_line 生效
要使用dbms_output.put_line ，则必须在sqlplus中显式声明：
set serverout on
比如：
SQL> set serverout on SQL> exec dbms_output.put_line('asda'); asda--输出结果 PL/SQL procedure successfully completed
3.5执行runstats中的方法以及两个存储过程
View Code
exec runstats_pkg.rs_start; exec proc1; exec runstats_pkg.rs_middle; exec proc2; exec runstats_pkg.rs_stop(10000); /
输出结果为：
View Code
Run1 ran in 26 cpu hsecs Run2 ran in 267 cpu hsecs run 1 ran in 9.74% of the time Name Run1 Run2 Diff STAT...parse count (total) 15 10,016 10,001 STAT...session cursor cache hi 10,003 1 -10,002 STAT...consistent gets from ca 39 10,054 10,015 STAT...consistent gets from ca 70 10,087 10,017 STAT...consistent gets 70 10,087 10,017 STAT...db block gets 10,424 30,369 19,945 STAT...db block gets from cach 10,424 30,369 19,945 STAT...db block gets from cach 65 20,039 19,974 LATCH.cache buffers chains 51,209 71,216 20,007 LATCH.enqueue hash chains 60 20,122 20,062 LATCH.enqueues 44 20,109 20,065 STAT...session logical reads 10,494 40,456 29,962 STAT...recursive calls 10,131 40,144 30,013 LATCH.kks stats 3 33,343 33,340 STAT...session uga memory max 123,452 72,940 -50,512 LATCH.shared pool simulator 80 83,641 83,561 STAT...session pga memory 65,536 196,608 131,072 STAT...session uga memory 0 196,392 196,392 LATCH.row cache objects 228 210,126 209,898 LATCH.shared pool 20,151 339,848 319,697 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 73,042 780,963 707,921 9.35% PL/SQL 过程已成功完成。
