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

也可以利用runstats_pkg工具包用來檢測使用繫結變數與不適用繫結變數的從時間 latch爭用等區別。

SQL> create table t01(id number);

Table created

SQL>

SQL> create or replace procedure proc1

2 as

3 begin

4 for i in 1..10000 loop

5 execute immediate 'insert into t01 values(:x)' using i;

6 end loop;

7 commit;

8 end;

9 /

Procedure created

SQL>

SQL> create or replace procedure proc2

2 as

3 begin

4 for i in 1..10000 loop

5 execute immediate 'insert into t01 values('||i||')';

6 end loop;

7 commit;

8 end;

9 /

Procedure created

SQL> execute runstats_pkg.rs_start;

PL/SQL procedure successfully completed

SQL> execute proc1;

PL/SQL procedure successfully completed

SQL> execute runstats_pkg.rs_middle;

PL/SQL procedure successfully completed

SQL> execute proc2;

PL/SQL procedure successfully completed

SQL> execute runstats_pkg.rs_stop(1000);

Run1 ran in 1917

Run1 ran in 6152

run 1 ran in 31.16% of the time

Name Run1 Run2 Diff

LATCH...SQL memory manager wor 408 1,447 1,039

STAT...redo size 2,377,200 2,378,280 1,080

STAT...IMU undo allocation siz 52 1,760 1,708

STAT...undo change vector size 642,948 644,720 1,772

LATCH...session allocation 139 2,246 2,107

STAT...parse time cpu 0 2,198 2,198

STAT...parse time elapsed 0 2,247 2,247

STAT...recursive cpu usage 145 2,520 2,375

STAT...CPU used when call star 168 2,555 2,387

STAT...CPU used by this sessio 167 2,555 2,388

STAT...sorts (rows) 2,433 4,825 2,392

STAT...DB time 170 2,609 2,439

LATCH...simulator hash latch 765 10,178 9,413

LATCH...simulator lru latch 747 10,163 9,416

STAT...recursive calls 10,273 20,162 9,889

STAT...consistent gets 233 10,205 9,972

STAT...consistent gets from ca 233 10,205 9,972

STAT...enqueue releases 31 10,019 9,988

STAT...enqueue requests 31 10,019 9,988

STAT...session logical reads 10,541 20,529 9,988

STAT...parse count (hard) 3 10,002 9,999 –硬解析STAT...parse count (total) 31 10,071 10,040

STAT...bytes sent via SQL*Net 2,116 12,738 10,622

STAT...table scan rows gotten 28 13,150 13,122

STAT...bytes received via SQL* 2,431 18,174 15,743

LATCH...enqueues 372 21,085 20,713

LATCH...enqueue hash chains 444 21,310 20,866

STAT...IMU Redo allocation siz 0 22,040 22,040

LATCH...cache buffers chains 51,598 73,901 22,303 —-常用latch

LATCH...library cache pin 40,326 72,167 31,841 --常用latch

LATCH...kks stats 6 39,851 39,845

LATCH...library cache lock 75 61,117 61,042 —-常用latch

STAT...session uga memory 0 65,560 65,560

LATCH...row cache objects 188 91,563 91,375

LATCH...library cache 40,508 223,396 182,888 —-常用latch

LATCH...shared pool 20,273 204,911 184,638 —-常用latch

STAT...session pga memory max 262,144 851,968 589,824

STAT...session uga memory max 254,380 1,126,320 871,940

Latches :

Run1 Run2 Diff PCT

157,133 837,705 680,572 19%

PL/SQL procedure successfully completed

可以看出硬解析下latch使用競爭激烈，如果從原理上可以分析為一條sql語句首先會經常hash運算得到hash值，然後在共享池獲取library cache latch如果存在相同hash值的sql語句，此時會釋放library cache latch而以null模式進行library cache pin/lock ，如果找不到相同的hash值的sql，則會先釋放library cache latch，然後獲取shared pool latch用於在共享池的bucket中尋找空閒的記憶體塊chunk，就會將bucket上存放的記憶體塊細分為更散而細小的記憶體塊，此時也會佔用shared pool latch，存在大量硬解析下，記憶體塊會越來越細小，在bucket上尋找空閒記憶體塊會時間會越來越長，更會引起shared pool latch競爭，佇列會越排越長，等待事件也會增加，找到空閒記憶體塊後又會釋放shared pool latch，然後獲取library cache latch，把相應的sql的執行計劃等資訊存放在library cache上，最後釋放該latch，以null模式進行library cache pin/lock。其實對於sql語句的解析原比這複雜的多！

[@more@]