在平時運維過程中，看見很多儲存過程使用動態sql，那麼動態sql跟靜態sql有什麼區別呢，或者最大的差別是什麼？

靜態（普通）sql是在建立儲存過程時就編譯了。也就是執行過程之前已經編譯了。
 動態sql是需要再執行過程中進行編譯，如果有大量動態sql，且執行頻繁的話，效率會比較差
 
 舉例如下(測試版本19c)：

``` sql
SQL> create table test (id int);

Table created.

SQL> insert into test select rownum from dual connect by rownum<=100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> 
```

動態sql測試：
``` sql
--執行統計開始（toms大師指令碼，可參考本文其他部落格）
execute runstats_pkg.rs_start
--執行過程
begin
execute immediate ' delete from test' ;
commit;
end;
/
```

靜態sql測試：
``` sql
alter system flush shared_pool;
alter system flush buffer_cache;
truncate table test;
insert into test select rownum from dual connect by rownum<=100000;
commit;

--開始測試
execute runstats_pkg.rs_middle

begin
delete from test;
commit;
end;
/
```
 兩個語句進行對比
 ``` sql
 set lines 200
 set pages 999
 set serveroutput on
execute runstats_pkg.rs_stop(5000)

--結果顯示
SQL>  set lines 200
 set pages 999
 set serveroutput on
execute runstats_pkg.rs_stop(5000)SQL> SQL> SQL> 
Run1 ran in 343 cpu hsecs
Run2 ran in 26 cpu hsecs
run 1 ran in 1319.23% of the time
	
Name					  Run1		  Run2		  Diff
STAT...global enqueue releases		 5,531		   517		-5,014
STAT...global enqueue gets syn		 5,544		   516		-5,028
LATCH.enqueue hash chains		 7,798		 2,432		-5,366
LATCH.ges cached resource list		 6,787		   773		-6,014
STAT...no work - consistent re		 6,261		   237		-6,024
STAT...consistent gets pin (fa		 6,826		   264		-6,562
STAT...consistent gets pin		 7,434		   282		-7,152
STAT...recursive calls			 8,302		   181		-8,121
STAT...buffer is not pinned co		 8,617		    90		-8,527
STAT...KTFB alloc time (ms)		 3,775		13,054		 9,279
STAT...sorts (rows)			 9,481		     2		-9,479
STAT...KTFB commit time (ms)		10,422		     0	       -10,422
STAT...consistent gets			11,742		   370	       -11,372
STAT...consistent gets from ca		11,742		   370	       -11,372
LATCH.shared pool			12,371		   575	       -11,796
LATCH.ges group table			13,641		 1,552	       -12,089
LATCH.ges domain table			13,679		 1,560	       -12,119
STAT...session logical reads		16,145		 2,156	       -13,989
STAT...redo synch time (usec)		23,077		 8,335	       -14,742
LATCH.ges resource hash list		20,408		 2,323	       -18,085
LATCH.ges process parent latch		27,244		 3,103	       -24,141
LATCH.gcs partitioned table ha		43,527		16,649	       -26,878
LATCH.recovery domain hash buc		51,470		17,667	       -33,803
STAT...KTFB free time (ms)		34,468		     0	       -34,468
LATCH.cache buffers chains		44,912		 9,095	       -35,817
STAT...table scan disk non-IMC	       155,290	       100,000	       -55,290
STAT...table scan rows gotten	       155,290	       100,000	       -55,290
STAT...session uga memory		     0		65,480		65,480
STAT...undo change vector size	     1,509,532	     1,260,832	      -248,700
STAT...session pga memory		     0	      -262,144	      -262,144
STAT...redo size		     3,305,632	     1,650,568	    -1,655,064
STAT...KTFB alloc space (block	     4,128,768	     2,097,152	    -2,031,616
STAT...file io wait time	     5,331,209	       270,039	    -5,061,170
STAT...cell physical IO interc	    10,469,376	     2,187,264	    -8,282,112
STAT...physical read total byt	    10,469,376	     2,187,264	    -8,282,112
STAT...physical read bytes	    10,469,376	     2,187,264	    -8,282,112
STAT...logical read bytes from	   132,259,840	    17,661,952	  -114,597,888
	
Run1 latches total versus runs -- difference and pct
Run1		   Run2 	     Diff	 Pct
286,424 	   62,852	   -223,572    455.71%

PL/SQL procedure successfully completed.
 ```
 
 有以上可以看出靜態sql語句效率更高。

【SQL】Oracle避免動態SQL，提高過程執行效率

相關文章