在平时运维过程中，看见很多存储过程使用动态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语句效率更高。