OLTP系统中尽量使用绑定变量

在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;
   
5. 
   
6. 
   
7. EXECUTE runstats_pkg.rs_start;
   
8. 
   
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. /
    
20. 
    
21. 
    
22. 
    
23. EXECUTE runstats_pkg.rs_middle;
    
24. 
    
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. /
    
36. 
    
37. set serveroutput on size 10000;
    
38. EXECUTE runstats_pkg.rs_stop(500);
    
39. 
    
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#######%
    
75. 
    
76. PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1071430/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1071430/