也可以利用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语句的解析原比这复杂的多！