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', 15 ) ||

49 lpad( 'Run2', 15 ) || lpad( 'Diff', 15 ) );

50

51 for x in

52 ( select rpad( a.name, 30 ) ||

53 to_char( b.value-a.value, '999,999,999,999' ) ||

54 to_char( c.value-b.value, '999,999,999,999' ) ||

55 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,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', 15 ) || lpad( 'Run2', 15 ) ||

75 lpad( 'Diff', 15 ) || lpad( 'Pct', 8 ) );

76

77 for x in

78 ( select to_char( run1, '999,999,999,999' ) ||

79 to_char( run2, '999,999,999,999' ) ||

80 to_char( diff, '999,999,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.

自定義效能統計儲存過程包runstats

相關文章