tom的runstats_pkg工具包關於批次插入和繫結變數分析-02

tom大師利用上述runstats_pkg工具包可以測試兩種方法所使用的時間差 統計資訊和latch等。

例如批次插入和逐條插入資料到table中，我們更自然的想象的是批次插入效能優於逐條插入，利用runstats_pkg更可以從時間 統計資訊 latch批次插入的優勢。

SQL> set serveroutput on size 999999

SQL> execute runstats_pkg.rs_start;

PL/SQL procedure successfully completed

SQL> insert into t select * from t;

100094 rows inserted

SQL> commit;

Commit complete

--批次插入

SQL> execute runstats_pkg.rs_middle;

PL/SQL procedure successfully completed

SQL> declare

2 begin

3 for x in(select * from t where rownum<=100094) loop

4 insert into t values x;

5 end loop

6 ;

7 commit;

8 end;

9 /

PL/SQL procedure successfully completed

--逐條插入

SQL> execute runstats_pkg.rs_stop;

Run1 ran in 23725

Run1 ran in 12820

run 1 ran in 185.06% of the time

Name Run1 Run2 Diff

LATCH...FAL subheap alocation 5 6 1

STAT...IMU Flushes 0 1 1

STAT...redo synch writes 2 1 -1

STAT...leaf node splits 329 328 -1

STAT...cluster key scan block 94 95 1

LATCH...lgwr LWN SCN 118 119 1

LATCH...mostly latch-free SCN 118 119 1

LATCH...FAL request queue 5 6 1

LATCH...transaction allocation 10 9 -1

LATCH...transaction branch all 4 3 -1

LATCH...parameter list 0 1 1

LATCH...KTF sga latch 0 1 1

LATCH...resmgr:actses change g 4 3 -1

STAT...commit cleanouts succes 2,408 2,407 -1

STAT...workarea executions - o 8 9 1

STAT...parse time elapsed 2 3 1

STAT...sorts (memory) 8 7 -1

LATCH...process allocation 8 7 -1

LATCH...session switching 4 3 -1

LATCH...process group creation 8 7 -1

LATCH...parameter table alloca 7 6 -1

LATCH...event group latch 4 3 -1

LATCH...channel handle pool la 8 7 -1

LATCH...OS process: request al 8 7 -1

LATCH...ncodef allocation latc 4 3 -1

STAT...transaction rollbacks 2 3 1

LATCH...session state list lat 12 13 1

STAT...commit batch/immediate 2 3 1

STAT...commit immediate reques 2 3 1

STAT...commit batch/immediate 2 3 1

STAT...commit immediate perfor 2 3 1

STAT...commit cleanout failure 4 3 -1

STAT...commit cleanouts 2,418 2,416 -2

STAT...cluster key scans 47 49 2

STAT...table fetch continued r 2 0 -2

STAT...commit txn count during 344 346 2

STAT...rollback changes - undo 4 6 2

STAT...heap block compress 6 4 -2

LATCH...sort extent pool 6 4 -2

LATCH...job_queue_processes pa 7 5 -2

LATCH...resmgr:free threads li 14 11 -3

LATCH...threshold alerts latch 5 2 -3

LATCH...JS slv state obj latch 9 6 -3

LATCH...list of block allocati 148 145 -3

LATCH...dummy allocation 14 11 -3

LATCH...ktm global data 4 1 -3

LATCH...sequence cache 15 12 -3

LATCH...resmgr:actses active l 14 11 -3

STAT...active txn count during 1,720 1,724 4

STAT...parse time cpu 1 5 4

STAT...redo log space requests 0 4 4

LATCH...post/wait queue 26 22 -4

STAT...deferred (CURRENT) bloc 61 65 4

LATCH...kwqbsn:qsga 9 4 -5

STAT...SQL*Net roundtrips to/f 17 12 -5

STAT...shared hash latch upgra 9 4 -5

STAT...index scans kdiixs1 9 4 -5

LATCH...Shared B-Tree 9 4 -5

STAT...write clones created in 0 5 5

STAT...immediate (CURRENT) blo 1,019 1,024 5

STAT...redo buffer allocation 0 5 5

LATCH...resmgr group change la 17 11 -6

LATCH...OS process 30 24 -6

LATCH...ksuosstats global area 16 9 -7

STAT...cleanout - number of kt 1,735 1,742 7

LATCH...parallel query alloc b 28 20 -8

STAT...parse count (hard) 2 11 9

STAT...user calls 31 22 -9

LATCH...library cache lock all 103 93 -10

STAT...redo synch time 0 10 10

STAT...space was found by tune 0 11 11

STAT...drop segment calls in s 0 11 11

STAT...tune down retentions in 0 11 11

STAT...steps of tune down ret. 0 11 11

STAT...local undo segment hint 0 11 11

STAT...rollbacks only - consis 57 45 -12

LATCH...FIB s.o chain latch 0 12 12

STAT...CR blocks created 57 45 -12

LATCH...messages 1,904 1,917 13

LATCH...FOB s.o list latch 6 19 13

STAT...sorts (rows) 2,428 2,414 -14

LATCH...archive control 5 19 14

STAT...redo ordering marks 7 21 14

STAT...consistent changes 85 71 -14

STAT...index fetch by key 80 62 -18

LATCH...PL/SQL warning setting 44 26 -18

LATCH...qmn task queue latch 36 16 -20

STAT...data blocks consistent 65 45 -20

STAT...calls to kcmgas 444 465 21

STAT...rows fetched via callba 29 5 -24

LATCH...library cache pin allo 64 38 -26

LATCH...client/application inf 85 55 -30

LATCH...Consistent RBA 67 99 32

LATCH...session timer 84 49 -35

LATCH...archive process latch 84 48 -36

LATCH...KMG MMAN ready and sta 79 42 -37

LATCH...OS process allocation 99 58 -41

STAT...calls to kcmgcs 1,376 1,421 45

STAT...redo log space wait tim 0 52 52

STAT...consistent gets - exami 2,303 2,250 -53

LATCH...redo allocation 547 621 74

LATCH...kks stats 160 82 -78

STAT...messages sent 35 116 81

STAT...table fetch by rowid 92 7 -85

STAT...buffer is not pinned co 322 227 -95

LATCH...compile environment la 122 234 112

STAT...enqueue conversions 0 131 131

STAT...table scans (short tabl 61 197 136

STAT...session cursor cache hi 116 259 143

LATCH...file cache latch 64 208 144

STAT...parse count (total) 133 280 147

LATCH...session idle bit 747 599 -148

LATCH...In memory undo latch 604 456 -148

LATCH...library cache load loc 248 98 -150

LATCH...active checkpoint queu 173 330 157

STAT...opened cursors cumulati 129 289 160

LATCH...redo writing 541 719 178

LATCH...active service list 510 275 -235

LATCH...cache buffer handles 2,160 1,872 -288

STAT...consistent gets from ca 6,155 6,444 289

STAT...consistent gets 6,155 6,444 289

LATCH...enqueues 4,945 4,597 -348

LATCH...dml lock allocation 938 575 -363

LATCH...channel operations par 1,419 948 -471

STAT...change write time 721 1,347 626

LATCH...enqueue hash chains 6,641 5,952 -689

STAT...enqueue releases 798 1,495 697

STAT...enqueue requests 797 1,495 698

STAT...calls to get snapshot s 2,221 1,521 -700

STAT...bytes received via SQL* 3,329 2,529 -800

LATCH...JS queue state obj lat 1,728 900 -828

STAT...bytes sent via SQL*Net 3,023 2,116 -907

STAT...free buffer requested 3,108 4,221 1,113

LATCH...library cache lock 3,061 1,921 -1,140

STAT...IMU undo allocation siz 2,036 880 -1,156

STAT...no work - consistent re 1,670 2,892 1,222

LATCH...undo global data 3,670 4,904 1,234

STAT...table scan blocks gotte 1,568 2,840 1,272

STAT...CPU used when call star 758 2,229 1,471

STAT...CPU used by this sessio 756 2,230 1,474

STAT...DB time 922 2,540 1,618

LATCH...session allocation 5,808 7,777 1,969

STAT...recursive cpu usage 12 2,055 2,043

LATCH...SQL memory manager wor 5,375 2,846 -2,529

LATCH...checkpoint queue latch 3,919 7,319 3,400

LATCH...row cache objects 15,438 6,645 -8,793

LATCH...cache buffers lru chai 606 10,351 9,745

LATCH...object queue header op 7,291 17,447 10,156

STAT...IMU Redo allocation siz 312 11,072 10,760

LATCH...simulator lru latch 5,442 18,132 12,690

LATCH...simulator hash latch 6,000 18,881 12,881

STAT...table scan rows gotten 106,689 183,795 77,106

LATCH...shared pool 3,870 101,844 97,974

STAT...buffer is pinned count 98,806 0 -98,806

STAT...execute count 132 100,369 100,237

STAT...recursive calls 1,743 104,092 102,349

STAT...redo entries 76,214 212,432 136,218

LATCH...library cache 8,941 205,890 196,949

LATCH...library cache pin 5,550 203,923 198,373

STAT...db block gets from cach 180,834 422,516 241,682

STAT...db block gets 180,834 422,516 241,682

STAT...session logical reads 186,989 428,960 241,971

STAT...session pga memory -65,536 196,608 262,144

STAT...db block changes 147,366 418,525 271,159

LATCH...cache buffers chains 708,774 1,715,175 1,006,401

STAT...undo change vector size 9,042,264########## 8,449,836

STAT...redo size ##############################

Latches :

Run1 Run2 Diff PCT

808,661 2,344,648 1,535,987 34%

PL/SQL procedure successfully completed

從執行時間上可以很自然的看出批次插入的優勢，從latch著手：library cache ，library cache pin，cache buffers chains等常用latch看出逐條插入對latch使用競爭太大，在繁忙的生產庫中即有可能嚴重影響效能。

[@more@]