OLTP系統中儘量使用繫結變數

LuiseDalian發表於2014-01-16
變數

在OLAP系統中不宜使用繫結變數，但如果是OLTP系統，就要儘量考慮使用繫結變數。

因為OLTP系統的特點是SQL執行非常頻繁，並且用時非常短。

此時，重用shared pool中的執行計劃，減少硬解析所節省的時間就非常可觀了。

點選(此處)摺疊或開啟

  1. DROP TABLE t;
  2. CREATE TABLE t(col1 VARCHAR2(10));
  3.     
  4. INSERT INTO t SELECT to_char(ROWNUM) FROM dual CONNECT BY LEVEL <= 100000;


  7. EXECUTE runstats_pkg.rs_start;

  9. --未使用繫結變數
  10. DECLARE
  11.   TYPE rc IS REF CURSOR;
  12.   l_cursor rc;
  13. BEGIN
  14.   FOR i IN 1 .. 500 LOOP
  15.   OPEN l_cursor FOR \'SELECT col1 FROM t WHERE col1 = \' || to_char(i);
  16.   CLOSE l_cursor;
  17. END LOOP;
  18. END;
  19. /



  23. EXECUTE runstats_pkg.rs_middle;

  25. --使用繫結變數
  26. DECLARE
  27.   TYPE rc IS REF CURSOR;
  28.   l_cursor rc;
  29. BEGIN
  30.   FOR i IN 1 .. 500 LOOP
  31.   OPEN l_cursor FOR \'SELECT col1 FROM t WHERE col1 = :x\' USING i;
  32.   CLOSE l_cursor;
  33. END LOOP;
  34. END;
  35. /

  37. set serveroutput on size 10000;
  38. EXECUTE runstats_pkg.rs_stop(500);

  40. Run1 ran in 2936hsec
  41. Run2 ran in 1520hsec
  42. run 1 ran in 193.16% of the time
  43.         
  44. Name Run1 Run2 Diff
  45. STAT...enqueue releases 509 8 -501
  46. STAT...enqueue requests 509 8 -501
  47. STAT...db block gets from cach 557 50 -507
  48. STAT...db block gets 557 50 -507
  49. STAT...bytes received via SQL* 763 1,365 602
  50. LATCH.shared pool simulator 790 13 -777
  51. LATCH.call allocation 1,009 19 -990
  52. LATCH.session allocation 1,006 15 -991
  53. LATCH.enqueue hash chains 1,418 302 -1,116
  54. STAT...bytes sent via SQL*Net 342 1,459 1,117
  55. LATCH.simulator hash latch 1,501 33 -1,468
  56. STAT...recursive calls 3,506 1,519 -1,987
  57. STAT...calls to kcmgcs 6,022 37 -5,985
  58. LATCH.row cache objects 13,610 658 -12,952
  59. LATCH.shared pool 28,744 546 -28,198
  60. STAT...buffer is not pinned co 29,501 68 -29,433
  61. STAT...table scan blocks gotte 29,500 59 -29,441
  62. STAT...no work - consistent re 29,501 59 -29,442
  63. STAT...consistent gets from ca 34,509 78 -34,431
  64. STAT...consistent gets from ca 35,010 93 -34,917
  65. STAT...consistent gets 35,010 93 -34,917
  66. STAT...session logical reads 35,567 143 -35,424
  67. STAT...session uga memory 65,488 0 -65,488
  68. LATCH.cache buffers chains 70,767 1,214 -69,553
  69. STAT...table scan rows gotten ########## 31,680##########
  70. STAT...logical read bytes from########## 1,171,456##########
  71.         
  72. Run1 latches total versus runs ----difference and pct
  73. Run1 Run2 Diff Pct
  74. 121,445 4,247 -117,198#######%

  76. PL/SQL procedure successfully completed.

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

相關文章