避免動態SQL(一)

LuiseDalian發表於2014-01-16
SQL
動態SQL在執行過程中編譯，而普通SQL是在執行前就已經編譯過了。
案例1

點選(此處)摺疊或開啟

  1. --create.sql
  2. --建立Luise使用者和相應的優化指令碼包
  3. conn / as sysdba;

  5. drop user luise cascade;
  6. CREATE USER luise IDENTIFIED BY luise;
  7. GRANT DBA TO luise;
  8. grant select any dictionary to luise;

  10. conn luise/luise

  12. CREATE OR REPLACE VIEW STATS AS
  13. SELECT \'STAT...\' || a.name AS name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic#
  14. UNION ALL
  15. SELECT \'LATCH.\' || name, gets FROM v$latch;

  17. ---------------------------------------------------------------------------------------
  18. DROP TABLE run_stats;
  19. CREATE GLOBAL TEMPORARY TABLE run_stats
  20. (
  21.   runid VARCHAR2(15),
  22.   name VARCHAR2(80),
  23.   value INT
  24. )ON COMMIT PRESERVE ROWS;

  26. --------------------------------------------------------------------------------------
  27. CREATE OR REPLACE PACKAGE runstats_pkg
  28. AS
  29. PROCEDURE rs_start;
  30. PROCEDURE rs_middle;
  31. PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0);
  32. END;
  33. /
  34.     
  35. ----------------------------------------------------------------------------------------
  36. CREATE OR REPLACE PACKAGE BODY runstats_pkg
  37. AS
  38.   g_start NUMBER;
  39.   g_run1 NUMBER;
  40.   g_run2 NUMBER;

  42.   PROCEDURE rs_start
  43.   IS
  44.   BEGIN
  45.     DELETE FROM run_stats;
  46.     INSERT INTO run_stats
  47.     SELECT \'before\', stats.* FROM stats;
  48.     g_start := dbms_utility.get_time;
  49. END;


  52.   PROCEDURE rs_middle
  53.   IS
  54.   BEGIN
  55.     g_run1 := (dbms_utility.get_time - g_start);
  56.     INSERT INTO run_stats SELECT \'after 1\', stats.* FROM stats;
  57.     g_start := dbms_utility.get_time;
  58.   END;

  60.   PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0)
  61.   IS
  62.   BEGIN
  63.     g_run2 := (dbms_utility.get_time - g_start);
  64.     dbms_output.put_line(\'Run1 ran in \' || g_run1 || \'hsec\');
  65.     dbms_output.put_line(\'Run2 ran in \' || g_run2 || \'hsec\');
  66.     dbms_output.put_line(\'run 1 ran in \' || round(g_run1/g_run2 * 100, 2)|| \'% of the time\');
  67.     dbms_output.put_line(chr(9));
  68.  
  69.     INSERT INTO run_stats SELECT \'after 2\',stats.* FROM STATS;
  70.     dbms_output.put_line(rpad(\'Name\',30) || lpad(\'Run1\',10)|| lpad(\'Run2\',10) || lpad(\'Diff\',10));

  72.     FOR x IN(SELECT rpad(a.name,30) || to_char(b.value - a.value,\'9,999,999\') ||
  73.                          to_char(c.value-b.value,\'9,999,999\') ||
  74.                          to_char(((c.value - b.value)-(b.value - a.value)),\'9,999,999\') AS data
  75.              FROM run_stats a, run_stats b, run_stats c
  76.              WHERE a.name = b.name
  77.                AND b.name=c.name
  78.                AND a.runid=\'before\'
  79.                AND b.runid=\'after 1\'
  80.                AND c.runid=\'after 2\'
  81.                AND (c.value - a.value) > 0
  82.                AND abs((c.value - b.value) - (b.value - a.value)) > p_difference_threshold
  83.              ORDER BY abs((c.value - b.value) - (b.value - a.value)))
  84.     LOOP
  85.       dbms_output.put_line(x.data);
  86.     END LOOP;

  88.     dbms_output.put_line(chr(9));
  89.     dbms_output.put_line(\'Run1 latches total versus runs ----difference and pct\');
  90.     dbms_output.put_line(lpad(\'Run1\',10) || lpad(\'Run2\',10) || lpad(\'Diff\',10) || lpad(\'Pct\',8));

  92.     FOR x IN(SELECT to_char(run1,\'9,999,999\') || to_char(run2,\'9,999,999\') ||
  93.                     to_char(diff,\'9,999,999\') || to_char(round(run1/run2*100, 2), \'999.99\') || \'%\' AS DATA
  94.              FROM (SELECT sum(b.value-a.value) AS run1, sum(c.value-b.value) AS run2,
  95.                      sum((c.value - b.value) - (b.value - a.value)) AS diff
  96.                    FROM run_stats a, run_stats b, run_stats c
  97.                    WHERE a.name = b.name
  98.                    AND b.name = c.name
  99.                    AND a.runid = \'before\'
  100.                    AND b.runid = \'after 1\'
  101.                    AND c.runid = \'after 2\'
  102.                    AND a.name LIKE \'LATCH%\'))
  103.     LOOP
  104.       dbms_output.put_line(x.data);
  105.     END LOOP;
  106.   END;
  107. END;
  108. /
  109. --下面的實驗是為了說明動態SQL在效能上不如靜態SQL
  110. CREATE TABLE t(col1 NUMBER(10));
  111. INSERT INTO t SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 100000;
  112. COMMIT;

  114. set serveroutput on;
  115. luise@TESTDB11>exec runstats_pkg.rs_start;
  116. --動態SQL
  117. BEGIN
  118.   EXECUTE IMMEDIATE \'DELETE FROM t\';
  119.   COMMIT;
  120. END;
  121. /

  123. alter system flush shared_pool;
  124. alter system flush buffer_cache;
  125. truncate table t;
  126. INSERT INTO t SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 100000;
  127. COMMIT;

  129. luise@TESTDB11>exec runstats_pkg.rs_middle;
  130. --靜態SQL
  131. BEGIN
  132.   DELETE FROM t;
  133.   COMMIT;
  134. END;
  135. /

  137. luise@TESTDB11>exec runstats_pkg.rs_stop(5000);
  138. Run1 ran in 32048hsec
  139. Run2 ran in 6504hsec
  140. run 1 ran in 492.74% of the time
  141.         
  142. Name Run1 Run2 Diff
  143. STAT...buffer is not pinned co 5,447 306 -5,141
  144. STAT...buffer is not pinned co 5,447 173 -5,274
  145. STAT...buffer is not pinned co 5,447 173 -5,274
  146. STAT...buffer is not pinned co 5,447 173 -5,274
  147. STAT...sorts (rows) 6,264 852 -5,412
  148. STAT...consistent gets 7,108 1,666 -5,442
  149. STAT...consistent gets from ca 7,108 1,666 -5,442
  150. STAT...sorts (rows) 6,264 751 -5,513
  151. LATCH.enqueue hash chains 7,070 1,251 -5,819
  152. STAT...consistent gets from ca 7,108 1,209 -5,899
  153. STAT...consistent gets 7,108 1,209 -5,899
  154. STAT...recursive calls 6,641 704 -5,937
  155. LATCH.enqueue hash chains 7,070 1,078 -5,992
  156. STAT...sorts (rows) 6,264 216 -6,048
  157. LATCH.SQL memory manager worka 7,575 1,457 -6,118
  158. STAT...consistent gets from ca 7,108 977 -6,131
  159. STAT...consistent gets 7,108 977 -6,131
  160. STAT...recursive calls 6,641 504 -6,137
  161. STAT...recursive calls 6,641 495 -6,146
  162. STAT...recursive calls 6,641 459 -6,182
  163. STAT...sorts (rows) 6,264 7 -6,257
  164. LATCH.enqueue hash chains 7,070 788 -6,282
  165. LATCH.enqueue hash chains 7,070 689 -6,381
  166. LATCH.SQL memory manager worka 7,575 1,178 -6,397
  167. STAT...consistent gets from ca 7,108 617 -6,491
  168. STAT...consistent gets 7,108 617 -6,491
  169. LATCH.SQL memory manager worka 7,575 830 -6,745
  170. LATCH.SQL memory manager worka 7,575 673 -6,902
  171. LATCH.shared pool simulator 6,952 48 -6,904
  172. LATCH.shared pool simulator 6,952 40 -6,912
  173. LATCH.shared pool simulator 6,952 34 -6,918
  174. LATCH.shared pool simulator 6,952 17 -6,935
  175. STAT...file io wait time 8,172 1,166 -7,006
  176. STAT...file io wait time 8,172 555 -7,617
  177. STAT...file io wait time 8,172 555 -7,617
  178. STAT...file io wait time 8,172 267 -7,905
  179. LATCH.cache buffers lru chain 11,348 3,372 -7,976
  180. LATCH.cache buffers lru chain 11,348 3,356 -7,992
  181. LATCH.cache buffers lru chain 11,348 3,343 -8,005
  182. LATCH.cache buffers lru chain 11,348 3,330 -8,018
  183. LATCH.object queue header oper 27,193 5,798 -21,395
  184. LATCH.object queue header oper 27,193 5,778 -21,415
  185. LATCH.object queue header oper 27,193 5,758 -21,435
  186. LATCH.object queue header oper 27,193 5,744 -21,449
  187. LATCH.shared pool 60,100 1,563 -58,537
  188. LATCH.shared pool 60,100 1,394 -58,706
  189. LATCH.shared pool 60,100 1,165 -58,935
  190. LATCH.shared pool 60,100 531 -59,569
  191. STAT...session pga memory max 65,536 0 -65,536
  192. STAT...session pga memory max 65,536 0 -65,536
  193. STAT...session pga memory max 65,536 0 -65,536
  194. STAT...session pga memory max 65,536 0 -65,536
  195. LATCH.row cache objects 84,484 1,689 -82,795
  196. LATCH.row cache objects 84,484 1,424 -83,060
  197. LATCH.row cache objects 84,484 1,008 -83,476
  198. LATCH.row cache objects 84,484 593 -83,891
  199. STAT...session logical reads 115,455 208,500 93,045
  200. STAT...session logical reads 115,455 208,901 93,446
  201. STAT...session logical reads 115,455 209,180 93,725
  202. STAT...session logical reads 115,455 209,678 94,223
  203. STAT...redo entries 103,487 201,299 97,812
  204. STAT...redo entries 103,487 201,314 97,827
  205. STAT...redo entries 103,487 201,329 97,842
  206. STAT...redo entries 103,487 201,344 97,857
  207. STAT...buffer is pinned count 100,965 199,710 98,745
  208. STAT...buffer is pinned count 100,965 199,710 98,745
  209. STAT...buffer is pinned count 100,965 199,710 98,745
  210. STAT...buffer is pinned count 100,965 199,710 98,745
  211. STAT...HSC Heap Segment Block 101,118 200,015 98,897
  212. STAT...HSC Heap Segment Block 101,118 200,030 98,912
  213. STAT...HSC Heap Segment Block 101,118 200,045 98,927
  214. STAT...HSC Heap Segment Block 101,118 200,060 98,942
  215. STAT...db block gets 108,347 207,883 99,536
  216. STAT...db block gets from cach 108,347 207,883 99,536
  217. STAT...db block gets from cach 108,347 207,924 99,577
  218. STAT...db block gets 108,347 207,924 99,577
  219. STAT...db block gets from cach 108,347 207,971 99,624
  220. STAT...db block gets 108,347 207,971 99,624
  221. STAT...db block gets from cach 108,347 208,012 99,665
  222. STAT...db block gets 108,347 208,012 99,665
  223. STAT...table scan rows gotten 122,040 229,260 107,220
  224. STAT...session uga memory max 130,952 0 -130,952
  225. STAT...session uga memory max 130,952 0 -130,952
  226. STAT...session uga memory max 130,952 0 -130,952
  227. STAT...session uga memory max 130,952 0 -130,952
  228. STAT...table scan rows gotten 122,040 272,388 150,348
  229. STAT...session uga memory 158,352 0 -158,352
  230. STAT...table scan rows gotten 122,040 301,140 179,100
  231. STAT...session uga memory 158,352 -38,096 -196,448
  232. STAT...session uga memory 158,352 -38,096 -196,448
  233. STAT...session uga memory 158,352 -38,096 -196,448
  234. STAT...db block changes 206,351 404,116 197,765
  235. STAT...db block changes 206,351 404,176 197,825
  236. STAT...db block changes 206,351 404,246 197,895
  237. STAT...db block changes 206,351 404,305 197,954
  238. STAT...table scan rows gotten 122,040 337,080 215,040
  239. STAT...session pga memory 262,144 0 -262,144
  240. STAT...redo synch time (usec) 458,442 148,810 -309,632
  241. STAT...redo synch time (usec) 458,442 148,810 -309,632
  242. STAT...redo synch time (usec) 458,442 148,810 -309,632
  243. STAT...redo synch time (usec) 458,442 148,810 -309,632
  244. LATCH.cache buffers chains 727,405 1,221,577 494,172
  245. LATCH.cache buffers chains 727,405 1,222,490 495,085
  246. LATCH.cache buffers chains 727,405 1,223,807 496,402
  247. LATCH.cache buffers chains 727,405 1,224,848 497,443
  248. STAT...cell physical IO interc 2,646,016 212,992-2,433,024
  249. STAT...physical read bytes 2,646,016 212,992-2,433,024
  250. STAT...physical read total byt 2,646,016 212,992-2,433,024
  251. STAT...physical read total byt 2,646,016 131,072-2,514,944
  252. STAT...physical read total byt 2,646,016 131,072-2,514,944
  253. STAT...physical read bytes 2,646,016 131,072-2,514,944
  254. STAT...physical read bytes 2,646,016 131,072-2,514,944
  255. STAT...cell physical IO interc 2,646,016 131,072-2,514,944
  256. STAT...cell physical IO interc 2,646,016 131,072-2,514,944
  257. STAT...physical read bytes 2,646,016 73,728-2,572,288
  258. STAT...physical read total byt 2,646,016 73,728-2,572,288
  259. STAT...cell physical IO interc 2,646,016 73,728-2,572,288
  260. STAT...undo change vector size##############################
  261. STAT...undo change vector size##############################
  262. STAT...undo change vector size##############################
  263. STAT...undo change vector size##############################
  264. STAT...redo size ##############################
  265. STAT...redo size ##############################
  266. STAT...redo size ##############################
  267. STAT...redo size ##############################
  268. STAT...logical read bytes from##############################
  269. STAT...logical read bytes from##############################
  270. STAT...logical read bytes from##############################
  271. STAT...logical read bytes from##############################
  272.         
  273. Run1 latches total versus runs ----difference and pct
  274. Run1 Run2 Diff Pct
  275. 3,879,348 5,027,245 1,147,897 77.17%

  277. PL/SQL procedure successfully completed.
  278. --結論: 動態SQL的時間為靜態SQL的492.74%,還可以看出動態SQL在栓鎖、物理讀等的消耗上大大超過了靜態
  279. --     SQL語句. 因此要儘可能地尋找將動態SQL轉換為靜態SQL的機會,即使因此導致程式碼更復雜也是值得的.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1071366/，如需轉載，請註明出處，否則將追究法律責任。

相關文章