sqlplus之runstats
时间：2019-01-15 10:04:01
手机看文章
扫描二维码
随时随地手机看文章
[导读]要使用runstats，需要能访问几个V$视图，并创建一个表来存储统计结果，还要创建runstats包。为此，需要访问4个V$表（就是那些神奇的动态性能表）：V$STATNAME、V$MYSTAT、V
要使用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账户。我们需要再scott下进行操作，因此需要将这四张表的select权限授予给scott账户
1.在sys账户下授权视图查询权限给scott
C:UsersAdministrator>sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on 星期五 3月 16 11:00:45 2018 Copyright (c) 1982, 2010, Oracle. All rights reserved. idle>conn /as sysdba 已连接。 sys@ORCL>grant select on sys.v_$statname to "SCOTT"; 授权成功。 sys@ORCL>grant select on sys.v_$mystat to "SCOTT"; 授权成功。 sys@ORCL>grant select on sys.v_$latch to "SCOTT"; 授权成功。 sys@ORCL>grant select on sys.v_$timer to "SCOTT"; 授权成功。 sys@ORCL>
2在scott账户下
2.1查询V_$表(不能使用别名查询，只能使用视图真名)
scott@ORCL>select * from sys.v_$statname; -- OK
scott@ORCL>select * from sys.v$statname; select * from sys.v$statname * 第 1 行出现错误: ORA-00942: 表或视图不存在
2.2在scott账户下创建视图
scott@ORCL>create or replace view stats 2 as select 'STAT...'||a.name name,b.value 3 from sys.v_$statname a,sys.v_$mystat b 4 where a.statistic# =b.statistic# 5 union all 6 select 'LATCH.'||name,gets 7 from sys.v_$latch 8 union all 9 select 'STAT...Elapsed Time',hsecs from sys.v_$timer; 视图已创建。
2.3创建信息收集表
scott@ORCL>create global temporary table run_stats 2 (runid varchar2(15), 3 name varchar2(80), 4 value int) 5 on commit preserve rows; 表已创建。
2.4创建runstats包
scott@ORCL>create or replace package runstats_pkg 2 as 3 procedure rs_start; 4 procedure rs_middle; 5 procedure rs_stop(p_difference_threshold in number default 0); 6 end; 7 8 / 程序包已创建。
p_difference_threshold用于控制最后打印的数据量。
runstats会收集并得到每次运行的统计信息+闩信息，然后打印一个报告，说明每次测试(每个方法)使用了多少资源，以及不同测试(不同方法)的结果之差。可以使用p_difference_threshold来控制只查看 差值大于这个数 的统计结果和闩信息。由于这个参数默认为0，所以默认情况下可以看到所有输出。
2.5创建包体
scott@ORCL>create or replace package body runstats_pkg 2 as 3 g_start number; #这3个全局变量 用于记录每次运行的耗用时间 4 g_run1 number; 5 g_run2 number;
6 #下面是rs_start例程，这个例程只是清空保存统计结果的表，并填入"上一次"(before)得到的统计结果+闩信息。
然后获得当前定时器值，这是一种时钟，可用于计算耗用时间(单位百分之一秒)
7 procedure rs_start 8 is 9 begin 10 delete from run_stats; 11 12 insert into run_stats 13 select 'before', stats.* from stats; 14 g_start := dbms_utility.get_cpu_time; 15 end;
16 # 接下来是rs_middle例程，这个例程只是把第一次测试运行的耗用时间记录在g_run1中。 然后插入当前的一组统计结果和闩信息。
#如果把这些值与先前在 rs_start中保存的值相减，就会发现第一个方法使用了多少闩，以及使用了多少游标(一种统计结果)，等等。
#最后，记录下一次运行的开始时间
17 procedure rs_middle 18 is 19 begin 20 g_run1 := (dbms_utility.get_cpu_time-g_start); 21 22 insert into run_stats 23 select 'after 1', stats.* from stats; 24 g_start := dbms_utility.get_cpu_time; 25 26 end; 27 28 procedure rs_stop(p_difference_threshold in number default 0) 29 is 30 begin 31 g_run2 := (dbms_utility.get_cpu_time-g_start); 32 33 dbms_output.put_line 34 ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' ); 35 dbms_output.put_line 36 ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' ); 37 if ( g_run2 <> 0 ) 38 then 39 dbms_output.put_line 40 ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) || 41 '% of the time' ); 42 end if; 43 dbms_output.put_line( chr(9) ); 44 45 insert into run_stats 46 select 'after 2', stats.* from stats; 47 48 dbms_output.put_line 49 ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) || 50 lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) ); 51 52 for x in 53 ( select rpad( a.name, 30 ) || 54 to_char( b.value-a.value, '999,999,999' ) || 55 to_char( c.value-b.value, '999,999,999' ) || 56 to_char( ( (c.value-b.value)-(b.value-a.value)), 57 '999,999,999' ) data 58 from run_stats a, run_stats b, run_stats c 59 where a.name = b.name 60 and b.name = c.name 61 and a.runid = 'before' 62 and b.runid = 'after 1'
63 and c.runid = 'after 2' 64 65 and abs( (c.value-b.value) - (b.value-a.value) ) 66 > p_difference_threshold 67 order by abs( (c.value-b.value)-(b.value-a.value)) 68 ) loop 69 dbms_output.put_line( x.data ); 70 end loop; 71 72 dbms_output.put_line( chr(9) ); 73 dbms_output.put_line 74 ( 'Run1 latches total versus runs -- difference and pct' ); 75 dbms_output.put_line 76 ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) || 77 lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) ); 78 79 for x in 80 ( select to_char( run1, '999,999,999' ) || 81 to_char( run2, '999,999,999' ) || 82 to_char( diff, '999,999,999' ) || 83 to_char( round( run1/decode( run2, 0, 84 to_number(0), run2) *100,2 ), '99,999.99' ) || '%' data 85 from ( select sum(b.value-a.value) run1, sum(c.value-b.value) ru n2, 86 sum( (c.value-b.value)-(b.value-a.value)) diff 87 from run_stats a, run_stats b, run_stats c 88 where a.name = b.name 89 and b.name = c.name 90 and a.runid = 'before' 91 and b.runid = 'after 1' 92 and c.runid = 'after 2' 93 and a.name like 'LATCH%' 94 ) 95 ) loop 96 dbms_output.put_line( x.data ); 97 end loop; 98 end; 99 100 end; 101 / 程序包体已创建。
3.使用runstats
3.1创建表T
16 #接下来是rs_middle例程，这个例程只是把第一次测试运行的耗用时间记录在g_run1中。 然后插入当前的一组统计结果和闩信息。
#如果把这些值与先前在 rs_start中保存的值相减，就会发现第一个方法使用了多少闩，以及使用了多少游标(一种统计结果)，等等。
#最后，记录下一次运行的开始时间。
scott@ORCL>create table t(x int); 表已创建。
3.2创建存储过程proc1，使用了一条带绑定变量的SQL语句
scott@ORCL>create or replace procedure proc1 2 as 3 begin 4 for i in 1 .. 10000 5 loop 6 execute immediate 7 'insert into t values(:x)'using i; 8 end loop; 9 end; 10 / 过程已创建。
3.3创建存储过程proc2，分别为要插入的每一行构造一条独立的SQL语句
scott@ORCL>create or replace procedure proc2
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'insert into t values('|| i ||')';
8 commit;
9 end loop;
10 end proc2;
11 /
过程已创建。3.4使dbms_output.put_line 生效
要使用dbms_output.put_line ，则必须在sqlplus中显式声明：
scott@ORCL>set serverout on
scott@ORCL>exec dbms_output.put_line('yinn');
yinn
PL/SQL 过程已成功完成。3.5执行runstats中的方法以及两个存储过程
scott@ORCL>exec runstats_pkg.rs_start; PL/SQL 过程已成功完成。 scott@ORCL> exec proc1; PL/SQL 过程已成功完成。 scott@ORCL> exec runstats_pkg.rs_middle; PL/SQL 过程已成功完成。 scott@ORCL> exec proc2; PL/SQL 过程已成功完成。 scott@ORCL> exec runstats_pkg.rs_stop(10000);
Run1 ran in 29 cpu hsecs Run2 ran in 546 cpu hsecs run 1 ran in 5.31% of the time Name Run1 Run2 Diff STAT...calls to get snapshot s 85 10,087 10,002 STAT...commit cleanouts succes 9 10,013 10,004 STAT...opened cursors cumulati 10,081 20,091 10,010 STAT...consistent gets from ca 273 10,284 10,011 STAT...consistent gets 273 10,284 10,011 STAT...parse count (total) 43 10,055 10,012 STAT...commit cleanouts 9 10,021 10,012 STAT...IMU Redo allocation siz 0 17,760 17,760 STAT...db block changes 20,323 40,182 19,859 STAT...db block gets from cach 81 20,041 19,960 LATCH.DML lock allocation 22 20,006 19,984 LATCH.enqueues 93 20,281 20,188 LATCH.redo writing 6 28,119 28,113 LATCH.messages 22 28,488 28,466 STAT...enqueue requests 58 30,026 29,968 STAT...enqueue releases 56 30,028 29,972 LATCH.session allocation 18 30,016 29,998 LATCH.In memory undo latch 3 40,020 40,017 LATCH.kks stats 25 47,406 47,381 LATCH.redo allocation 7 48,116 48,109 STAT...db block gets from cach 10,468 60,187 49,719 STAT...db block gets 10,468 60,187 49,719 STAT...recursive calls 11,218 60,937 49,719 LATCH.undo global data 157 50,201 50,044 STAT...session logical reads 10,741 70,471 59,730 LATCH.enqueue hash chains 141 60,348 60,207 LATCH.shared pool simulator 33 66,792 66,759 STAT...session uga memory max 168,592 93,360 -75,232 STAT...session uga memory 65,488 196,464 130,976 LATCH.row cache objects 694 180,385 179,691 LATCH.cache buffers chains 52,432 282,416 229,984 LATCH.shared pool 20,733 432,092 411,359 STAT...undo change vector size 645,592 1,323,420 677,828 STAT...redo size 2,385,696 5,111,572 2,725,876 STAT...IMU undo allocation siz 0 5,512,320 5,512,320 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 75,892 1,376,015 1,300,123 5.52% PL/SQL 过程已成功完成。
下载文档
