你的位置:首页 > 数据库

[数据库]oracle编程艺术

runstats工具是《 oracle database 9i/10g/11g编程艺术 深入数据库体系结构》作者写的一个统计性能工具,能对做同一件事的两个方法进行比较,得到孰优孰劣的结果。

(看到runstats想到了db2 里有runstats命令收集统计信息)

runststs工具主要测量三个要素

  • 墙上时钟(wall clock) 或耗用时间(elapsed time)
  • 系统统计结果,会并排地显示每个方法做某件事(如执行一个解析调用)的次数,并展示出二者之差
  • 闩定(latch)这个是报告的关键输出

要使用该工具,需要能访问V$视图,并创建一个表来存储统计结果,还需要创建runstats包,下面是在scott用户下创建该工具,以下试验在ORACLE 11.2.0.1.0上进行

使用SYS用户登录,执行以下语句

--默认scott无创建视图权限,创建视图时会报ORA-01031: insufficient privilegesgrant create view to scott;--将以下4个动态性能视图原表SELECT权限赋给scottgrant SELECT on v_$statname to scott ;grant SELECT on v_$mystat to scott ;grant SELECT on v_$latch to scott ;grant SELECT on v_$timer to scott ;


scott用户下登录,执行以下语句

--创建统计结果表create or replace view statsas 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; --创建临时表收集统计结果create global temporary table run_stats(runid varchar2(15), name varchar2(80), value int) on commit preserve rows;   --创建runstats包-- runstats包含3个API,runstats测试开始时调用rs_start,rs_middle会在测试中调用,完成时调用rs_stop,打印报告-- rs_stop的p_difference_threshold参数,用来控制最后打印的数据量,输入这个参数可只查看差值大于参数的统计结果和闩信息,默认为0全部显示create or replace package runstats_pkgas    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;  --打印每次运行累计CPU时间,分别打印两次运行的统计结果和闩值(只打印超过p_difference_threshold的结果)  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;  /

工具创建好了之后,可以拿个例子来测试一下,把下面语句写入test.sql,做成一个SQL文件,sqlplus中执行

drop table testStat;create table testStat(id varchar2(10));exec runstats_pkg.rs_start;exec dbms_output.put_line('rs_start....');insert into testStat select level from dual connect by level <=500000;commit;exec dbms_output.put_line('insert completed....');exec runstats_pkg.rs_middle;exec dbms_output.put_line('rs_middle....');begin for i in 1 .. 500000  loop  insert into testStat values (i);  end loop;  commit;  end;  /exec dbms_output.put_line('loop insert....');exec runstats_pkg.rs_stop(0);

结果如下:

[oracle@RHEL65 test]$ sqlplus scott/oracle@orcl @t.sqlSQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 30 16:54:09 2016Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsTable dropped.Table created.PL/SQL procedure successfully completed.rs_start....PL/SQL procedure successfully completed.500000 rows created.Commit complete.insert completed....PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.rs_middle....PL/SQL procedure successfully completed.PL/SQL procedure successfully completed.loop insert....PL/SQL procedure successfully completed.Run1 ran in 66 cpu hsecsRun2 ran in 2217 cpu hsecsrun 1 ran in 2.98% of the timeName                 Run1    Run2    DiffSTAT...opened cursors current      -1      0      1STAT...redo synch writes         2      1     -1STAT...commit txn count during      2      3      1STAT...IMU Flushes            2      1     -1STAT...rows fetched via callba      5      4     -1STAT...cursor authentications      0      1      1STAT...buffer is pinned count      1      2      1STAT...parse time elapsed        1      0     -1LATCH.channel handle pool latc      2      1     -1LATCH.queued dump request        0      1      1LATCH.MinActiveScn Latch         0      1      1LATCH.Shared B-Tree           1      2      1LATCH.hash table modification      1      0     -1LATCH.SQL memory manager latch      0      1      1LATCH.kwqbsn:qsga            0      1      1LATCH.threshold alerts latch       0      1      1STAT...IMU pool not allocated      0      2      2STAT...IMU- failed to get a pr      0      2      2STAT...SQL*Net roundtrips to/f     10      8     -2LATCH.ksuosstats global area       0      2      2LATCH.dml lock allocation        2      4      2STAT...user calls            15     12     -3STAT...sorts (memory)          11      8     -3STAT...sorts (rows)           5      2     -3LATCH.object stats modificatio      7      4     -3LATCH.kcbtsemkid latch          0      3      3LATCH.managed standby latch       0      3      3LATCH.parameter list           0      3      3LATCH.session state list latch      3      0     -3LATCH.session switching         1      5      4LATCH.ksv allocation latch        0      4      4LATCH.sort extent pool          0      4      4LATCH.deferred cleanup latch       0      4      4LATCH.cp sga latch            0      4      4LATCH.parallel query alloc buf      1      5      4LATCH.ncodef allocation latch      0      4      4LATCH.qmn task queue latch        0      4      4LATCH.ASM network state latch      0      4      4STAT...write clones created in      0      5      5STAT...immediate (CURRENT) blo     14      9     -5LATCH.resmgr:active threads       0      5      5LATCH.resmgr:schema config        0      5      5LATCH.job_queue_processes para      0      5      5STAT...table scans (short tabl      6     12      6STAT...table scan blocks gotte      4     10      6LATCH.FAL Queue             0      6      6LATCH.alert log latch          0      6      6LATCH.reservation so alloc lat      0      6      6LATCH.transaction allocation      15      8     -7LATCH.OS process allocation       0      9      9LATCH.KMG MMAN ready and start      0      9      9LATCH.Change Notification Hash      0      9      9LATCH.Real-time plan statistic      0      9      9STAT...redo buffer allocation      0     10     10STAT...physical read total IO      2     13     11STAT...physical reads          2     13     11STAT...physical reads cache       2     13     11STAT...physical read IO reques      2     13     11LATCH.cache buffer handles      1,064    1,076     12LATCH.archive control          0     12     12LATCH.Reserved Space Latch        0     12     12LATCH.session timer           0     12     12LATCH.kks stats             1     15     14LATCH.shared pool simulator       10     24     14STAT...Heap Segment Array Upda     23      8     -15STAT...switch current to new b     31     14     -17STAT...calls to get snapshot s     188     171     -17STAT...cluster key scans        51     34     -17STAT...cluster key scan block      51     34     -17STAT...index scans kdiixs1       88     105     17STAT...deferred (CURRENT) bloc     32     14     -18LATCH.FIB s.o chain latch        0     18     18STAT...consistent changes        63     44     -19STAT...table fetch by rowid       31     50     19STAT...shared hash latch upgra     50     70     20LATCH.archive process latch       0     21     21LATCH.space background task la      0     21     21STAT...consistent gets - exami     888     912     24STAT...index fetch by key        52     28     -24LATCH.FOB s.o list latch         2     26     24STAT...commit cleanouts        867     842     -25STAT...commit cleanouts succes     861     836     -25STAT...no work - consistent re     146     171     25STAT...workarea memory allocat     -46     -21     25LATCH.session idle bit         35     63     28STAT...hot buffers moved to he      0     29     29LATCH.In memory undo latch       15     45     30STAT...buffer is not pinned co     277     312     35STAT...redo log space requests      0     40     40LATCH.SGA IO buffer pool latch      2     45     43LATCH.DML lock allocation       139     93     -46LATCH.post/wait queue          3     51     48LATCH.active service list        0     51     51LATCH.file cache latch         46     108     62STAT...cleanout - number of kt     744     817     73STAT...active txn count during     743     816     73LATCH.call allocation          8     82     74LATCH.active checkpoint queue      9     84     75LATCH.session allocation        12     115     103LATCH.ASM db client latch        2     106     104LATCH.object queue header heap     22     132     110LATCH.Consistent RBA          15     129     114LATCH.lgwr LWN SCN           15     129     114LATCH.mostly latch-free SCN       15     131     116STAT...table scan rows gotten      50     176     126LATCH.message pool operations      4     130     126STAT...enqueue releases        280     409     129STAT...enqueue requests        280     409     129STAT...enqueue conversions        3     147     144LATCH.JS queue state obj latch      0     180     180STAT...messages sent          14     202     188STAT...file io wait time        66     276     210STAT...non-idle wait count       21     251     230STAT...redo log space wait tim      0     355     355STAT...IMU undo allocation siz     712    1,080     368STAT...change write time         3     403     400LATCH.redo writing           49     486     437STAT...bytes sent via SQL*Net    2,379    1,894    -485LATCH.row cache objects       1,062    1,577     515STAT...non-idle wait time        2     534     532LATCH.SQL memory manager worka      7     610     603LATCH.channel operations paren     15     694     679LATCH.redo allocation          52     751     699STAT...bytes received via SQL*    3,942    3,228    -714STAT...calls to kcmgcs        1,795    1,027    -768STAT...consistent gets from ca    2,060    1,290    -770STAT...consistent gets        2,060    1,290    -770STAT...consistent gets from ca    1,087     287    -800STAT...db block gets from cach    2,252    1,216   -1,036LATCH.messages             63    1,136    1,073LATCH.enqueues             348    1,422    1,074LATCH.enqueue hash chains       566    1,722    1,156STAT...recursive cpu usage        5    1,912    1,907STAT...CPU used when call star     66    2,217    2,151STAT...CPU used by this sessio     66    2,217    2,151STAT...Elapsed Time           68    2,751    2,683STAT...DB time             67    2,751    2,684STAT...Heap Segment Array Inse    2,779     14   -2,765STAT...free buffer requested     1,063    5,148    4,085STAT...redo subscn max counts    1,021    5,124    4,103STAT...calls to kcmgas         300    4,409    4,109STAT...redo ordering marks       233    4,369    4,136LATCH.simulator lru latch       114    4,520    4,406LATCH.undo global data        1,065    5,517    4,452STAT...free buffer inspected       0    4,479    4,479LATCH.cache buffers lru chain     187    5,401    5,214LATCH.checkpoint queue latch      221    6,512    6,291STAT...IMU Redo allocation siz     540   11,212   10,672LATCH.object queue header oper    2,563   25,027   22,464LATCH.simulator hash latch       949   36,561   35,612STAT...session cursor cache hi     78   41,324   41,246STAT...physical read bytes     16,384   106,496   90,112STAT...cell physical IO interc   16,384   106,496   90,112STAT...physical read total byt   16,384   106,496   90,112STAT...HSC Heap Segment Block    2,813   500,029   497,216STAT...redo entries         6,853   504,649   497,796STAT...recursive calls        1,443   501,196   499,753STAT...execute count          172   500,170   499,998STAT...opened cursors cumulati     159   500,162   500,003LATCH.shared pool           219   500,325   500,106STAT...session logical reads    12,488   516,953   504,465STAT...db block gets        10,428   515,663   505,235STAT...db block gets from cach   10,428   515,663   505,235STAT...session pga memory     524,288  -196,608  -720,896STAT...session uga memory     785,856  -196,464  -982,320STAT...db block changes       10,145  1,009,164   999,019LATCH.cache buffers chains     41,525  2,576,396  2,534,871STAT...session pga memory max  24,772,608      0 -24,772,608STAT...session uga memory max  24,843,448      0 -24,843,448STAT...undo change vector size  1,210,048 34,006,444 32,796,396STAT...redo size         8,857,832 124,592,660 115,734,828Run1 latches total versus runs -- difference and pctRun1    Run2    Diff    Pct50,679  3,171,932  3,121,253   1.60%PL/SQL procedure successfully completed.scott@ORCL>