自定义性能统计存储过程包runstats

runstats是一个工具,能对做同一件事的两个不同方法进行比较,得出孰优孰劣的结果。只需提供两个不同的方法, 余下的事情都由runstats负责。runstats只是测量3个要素:
? 耗用时间(elapsed time):知道耗用时间很有用,不过这不是最重要的信息。
? 系统统计结果:会并排显示每个方法做某件事(如执行一个解析调用)的次数,并展示二者之差。
? 闩锁(latching):这是这个报告的关键输出。
闩锁(latch)是一种轻量级的锁。锁(lock)是一种串行化设备,而串行化设备不支持并发。如果应用不支 持并发,可扩缩性就比较差,只能支持较少的用户,而且需要更多的资源。构建应用时,我们往往希望应用能很好地扩缩,也就是说,为1位用户服务与为1,000或10,000位用户服务应该是一样的。应用中使用的闩锁越少,性能就越好。如果一种方法从耗用时间来看运行时间较长,但是只使用了另一种方法10%的闩锁,我可能会选择前者。因为与使用更多闩锁的方法相比,使用较少闩锁的方法能更好地扩缩。
runstats最后独立使用,也就是说,最好在一个单用户数据库上运行。它会测量各个方法的统计结果和闩锁(锁定)活动。 runstats在运行过程中,不希望其他任务对系统的负载或闩锁产生影响。只需一个小的测试数据库就能很好地完成这些测试。要使用runstats,需要能访问几个V$视图,并创建一个表来存储统计结果,还要创建runstats包。为此,需要访问3个V$表:V$STATNAME、V$MYSTAT和V$LATCH。以下是我使用的视图:

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;

如果你能得到V$STATNAME、V$MYSTAT、V$LATCH和V$TIMER的直接授权,就能直接对这些表执行SELECT操作(相应地可以自行创建 视图);否则,可以由其他人对这些表执行SELECT操作为你创建视图,并授予你在这个视图上执行SELECT的权限。
一旦建立视图,接下来只需要一个小表来收集统计结果:

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

Table created.

最后,需要创建runstats包。其中包含3个简单的API调用:
runstats测试开始时调用RS_STAT(runstats开始)。
正如你想象的,RS_MIDDLE会在测试之间调用。
完成时调用RS_STOP,打印报告。
创建runstats包的规范如下:

sys@DEVELOP> 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  /

Package created.

参数P_DIFFERENCE_THRESHOLD用于控制最后打印的数据量。runstats会收集并得到每次运行的统计结果和闩信息,然后打印一个 报告,说明每次测试(每个方法)使用了多少资源,以及不同测试(不同方法)的结果之差。可以使用这个输入参数来控制只查 看差值大于这个数的统计结果和闩信息。由于这个参数默认为0,所以默认情况下可以看到所有输出。
下面我们逐一分析包体中的过程。包前面是一些全局变量,这些全局变量用于记录每次运行的耗用时间:
RS_START过程。这个过程只是清空保存统计结果的表,并填入“上一次”(before)得到的统计结果和闩信息。然后获得 当前定时器值,这是一种时钟,可用于计算耗用时间(单位百分之一秒):

接下来是RS_MIDDLE过程。这个过程只是把第一次测试运行的耗用时间记录在G_RUN1中。然后插入当前的一组统计结果和闩信息。 如果把这些值与先前在RS_START中保存的值相减,就会发现第一个方法使用了多少闩,以及使用了多少游标(一种统计结果), 等等。
最后,记录下一次运行的开始时间:

完整的包体创建语句如下:

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

Package body created.

下面可以使用runstats了。我们将通过例子来说明如何使用runstats对批量插入(INSERT)和逐行处理进行比较,
看看哪种方法效率更高。首先建立两个表,要在其中插入795,680行记录:

sys@DEVELOP> create table t1 as select * from hnsic.ac01 where 1=0;

Table created.

sys@DEVELOP> create table t2 as select * from hnsic.ac01 where 1=0;

Table created.

接下来使用第一种方法插入记录,也就是使用单独一条SQL语句完成批量插入。首先调用RUNSTATS_PKG.RS_START:

sys@DEVELOP> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

sys@DEVELOP> insert into t1 select * from hnsic.ac01;

795680 rows created.

sys@DEVELOP> commit;

Commit complete.

下面准备执行第二种方法,即逐行地插入数据:

sys@DEVELOP> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

sys@DEVELOP> begin
  2   for x in (select * from hnsic.ac01)
  3   loop
  4     insert into t2 values x;
  5   end loop;
  6   commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

最后生成报告:

sys@DEVELOP> exec runstats_pkg.rs_stop;
Run1 ran in 4835 hsecs
Run2 ran in 14497 hsecs
run 1 ran in 33.35% of the time

Name                                                                                            Run1              

  Run2
Diff
LATCH.space background state object latch                                                1         0        -1
LATCH.file cache latch                                                                  93        94         1
LATCH.ktm global data                                                                    5         4        -1
LATCH.KTF sga latch                                                                      1         0        -1
LATCH.dml lock allocation                                                                1         2         1
LATCH.tablespace key chain                                                               1         2         1
LATCH.deferred cleanup latch                                                             1         2         1
LATCH.kcbtsemkid latch                                                                   2         1        -1
LATCH.threshold alerts latch                                                             1         2         1
LATCH.cp sga latch                                                                       1         2         1
LATCH.hash table modification latch                                                      1         2         1
STAT...parse count (hard)                                                                1         2         1
STAT...parse count (total)                                                               5         4        -1
STAT...sql area evicted                                                                  1         0        -1
LATCH.ASM network state latch                                                            1         2         1
STAT...write clones created in foreground                                                3         2        -1
STAT...shared hash latch upgrades - no wait                                             93        94         1
LATCH.ncodef allocation latch                                                            1         2         1
STAT...deferred (CURRENT) block cleanout applications                                    1         2         1
STAT...commit txn count during cleanout                                                  0         1         1
STAT...IMU Flushes                                                                       0         1         1
STAT...table fetch by rowid                                                             94        95         1
STAT...index scans kdiixs1                                                              94        95         1
STAT...Requests to/from client                                                           6         4        -2
STAT...session cursor cache count                                                        5         3        -2
STAT...redo log space wait time                                                          2         0        -2
STAT...redo log space requests                                                           4         2        -2
LATCH.ksv class latch                                                                    3         5         2
STAT...non-idle wait time                                                                2         0        -2
STAT...calls to get snapshot scn: kcmgss                                               184       186         2
STAT...SQL*Net roundtrips to/from client                                                 6         4        -2
LATCH.MinActiveScn Latch                                                                 0         2         2
STAT...db block gets from cache (fastpath)                                             103       105         2
LATCH.ksv allocation latch                                                               4         6         2
STAT...redo entries                                                                849,543   849,545         2
LATCH.message pool operations parent latch                                               4         1        -3
STAT...parse time cpu                                                                    1         4         3
STAT...user calls                                                                        9         6        -3
LATCH.kwqbsn:qsga                                                                        2         5         3
LATCH.Shared B-Tree                                                                      3         6         3
LATCH.interrupt manipulation                                                             1         5         4
LATCH.SQL memory manager latch                                                           1         5         4
LATCH.kokc descriptor allocation latch                                                   2         6         4
STAT...redo buffer allocation retries                                                    5         1        -4
LATCH.multiblock read objects                                                            6         2        -4
LATCH.object stats modification                                                          8        13         5
STAT...enqueue releases                                                              3,711     3,716         5
STAT...enqueue requests                                                              3,710     3,716         6
STAT...redo subscn max counts                                                       23,411    23,405        -6
LATCH.corrupted undo seg latch                                                          18        12        -6
STAT...heap block compress                                                               7         0        -7
STAT...free buffer requested                                                        23,406    23,414         8
LATCH.ksuosstats global area                                                             5        14         9
STAT...calls to kcmgas                                                              10,299    10,289       -10
STAT...redo ordering marks                                                          10,201    10,191       -10
STAT...calls to kcmgcs                                                              33,174    33,187        13
LATCH.loader state object freelist                                                      14        28        14
LATCH.qmn task queue latch                                                              15        30        15
LATCH.job_queue_processes parameter latch                                               11        31        20
LATCH.parallel query alloc buffer                                                        7        31        24
STAT...consistent changes                                                               31        56        25
STAT...db block gets from cache                                                    870,280   870,307        27
STAT...db block gets                                                               870,280   870,307        27
STAT...db block changes                                                          1,672,435 1,672,465        30
STAT...active txn count during cleanout                                             16,027    15,995       -32
STAT...cleanout - number of ktugct calls                                            16,027    15,995       -32
STAT...consistent gets - examination                                                16,032    16,000       -32
LATCH.session timer                                                                     16        48        32
LATCH.Change Notification Hash table latch                                              16        48        32
LATCH.KMG MMAN ready and startup request latch                                          16        49        33
STAT...workarea memory allocated                                                        40        -5       -45
STAT...messages sent                                                                   370       322       -48
LATCH.sort extent pool                                                                   2        61        59
STAT...undo change vector size                                                  ####################        60
LATCH.ASM db client latch                                                               36        99        63
LATCH.simulator lru latch                                                            6,903     6,977        74
LATCH.parameter list                                                                    36       125        89
LATCH.FOB s.o list latch                                                                 6        97        91
LATCH.active checkpoint queue latch                                                    143       237        94
LATCH.global tx hash mapping                                                            19       129       110
LATCH.transaction branch allocation                                                     32       162       130
LATCH.space background task latch                                                       89       270       181
LATCH.post/wait queue                                                                   68       251       183
LATCH.SGA IO buffer pool latch                                                         193       442       249
STAT...change write time                                                             1,393     1,648       255
LATCH.Real-time plan statistics latch                                                  383       763       380
LATCH.shared pool simulator                                                            106       508       402
STAT...bytes sent via SQL*Net to client                                              1,438       948      -490
LATCH.JS queue state obj latch                                                         360     1,044       684
STAT...bytes received via SQL*Net from client                                        2,313     1,623      -690
LATCH.lgwr LWN SCN                                                                     401     1,402     1,001
LATCH.Consistent RBA                                                                   399     1,401     1,002
LATCH.session switching                                                                  7     1,009     1,002
LATCH.resmgr:actses change group                                                         9     1,018     1,009
LATCH.resmgr group change latch                                                          7     1,016     1,009
LATCH.compile environment latch                                                          8     1,017     1,009
LATCH.global KZLD latch for mem in SGA                                                   7     1,016     1,009
LATCH.mostly latch-free SCN                                                            408     1,427     1,019
LATCH.PL/SQL warning settings                                                           53     1,086     1,033
LATCH.In memory undo latch                                                             298     1,793     1,495
LATCH.channel handle pool latch                                                         15     2,024     2,009
LATCH.dummy allocation                                                                  14     2,024     2,010
LATCH.ksz_so allocation latch                                                           13     2,023     2,010
LATCH.OS process: request allocation                                                    13     2,023     2,010
LATCH.resmgr:active threads                                                             13     2,023     2,010
LATCH.resmgr:free threads list                                                          13     2,023     2,010
LATCH.process allocation                                                                14     2,024     2,010
LATCH.process group creation                                                            13     2,023     2,010
LATCH.list of block allocation                                                       1,665     3,678     2,013
LATCH.Event Group Locks                                                                 17     2,035     2,018
LATCH.session state list latch                                                          13     2,042     2,029
LATCH.transaction allocation                                                            24     2,079     2,055
STAT...IMU undo allocation size                                                      3,256     1,080    -2,176
LATCH.DML lock allocation                                                               73     2,251     2,178
LATCH.parameter table management                                                        69     2,249     2,180
LATCH.OS process allocation                                                            113     2,315     2,202
LATCH.redo allocation                                                                2,664     5,249     2,585
LATCH.sequence cache                                                                    21     3,048     3,027
LATCH.undo global data                                                              23,400    26,617     3,217
LATCH.active service list                                                              146     3,396     3,250
LATCH.cache buffer handles                                                              42     3,356     3,314
LATCH.redo writing                                                                   1,389     4,733     3,344
LATCH.channel operations parent latch                                                  273     3,729     3,456
STAT...CPU used when call started                                                    2,405     6,330     3,925
STAT...CPU used by this session                                                      2,403     6,330     3,927
STAT...DB time                                                                       2,416     6,351     3,935
LATCH.OS process                                                                        29     4,041     4,012
LATCH.call allocation                                                                   26     4,252     4,226
LATCH.session allocation                                                               200     4,567     4,367
STAT...free buffer inspected                                                         9,080    13,902     4,822
LATCH.messages                                                                       1,938     7,030     5,092
STAT...hot buffers moved to head of LRU                                              3,719     9,033     5,314
STAT...recursive cpu usage                                                               6     5,494     5,488
LATCH.client/application info                                                           52     7,115     7,063
STAT...consistent gets from cache                                                   49,254    57,049     7,795
STAT...consistent gets                                                              49,254    57,049     7,795
STAT...table scan blocks gotten                                                     16,318    24,129     7,811
STAT...no work - consistent read gets                                               16,507    24,320     7,813
STAT...buffer is not pinned count                                                   16,695    24,510     7,815
STAT...session logical reads                                                       919,534   927,356     7,822
STAT...consistent gets from cache (fastpath)                                        33,128    40,954     7,826
LATCH.enqueue hash chains                                                            8,891    17,620     8,729
LATCH.object queue header heap                                                      12,561    21,765     9,204
STAT...session cursor cache hits                                                        97     9,347     9,250
STAT...IMU Redo allocation size                                                        476    11,228    10,752
STAT...redo size                                                                ####################    17,552
LATCH.session idle bit                                                               5,575    23,796    18,221
LATCH.cache buffers lru chain                                                       18,487    42,303    23,816
LATCH.checkpoint queue latch                                                        17,631    49,092    31,461
LATCH.object queue header operation                                                122,486   188,006    65,520
STAT...session uga memory max                                                      123,488    31,848   -91,640
LATCH.simulator hash latch                                                         237,377   443,767   206,390
LATCH.SQL memory manager workarea list latch                                         1,219   237,704   236,485
STAT...session pga memory max                                                            0   262,144   262,144
STAT...execute count                                                                   101   795,782   795,681
STAT...opened cursors cumulative                                                       102   795,789   795,687
STAT...recursive calls                                                                 848   804,498   803,650
LATCH.shared pool                                                                    7,037   884,303   877,266
LATCH.row cache objects                                                             25,994 1,205,408 1,179,414
LATCH.cache buffers chains                                                      #################### 7,690,411
STAT...logical read bytes from cache                                            ##############################

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
##############################  53.64%

PL/SQL procedure successfully completed.

本文参考Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions

发表评论

电子邮件地址不会被公开。