绑定变量

aaaaaaaa2000

1. SQL> var a varchar2(10);
   
2. SQL> exec :a:='INVALID';
   
3. 
   
4. PL/SQL procedure successfully completed.
   
5. 
   
6. SQL> set autotrace on
   
7. SQL> select object_id,status from t1 where status=:a;
   
8. OBJECT_ID STATUS
   
9. ---------- -------
   
10.         92 INVALID
    
11.         93 INVALID
    
12.         94 INVALID
    
13.         95 INVALID
    
14.         96 INVALID
    
15.         97 INVALID
    
16.         98 INVALID
    
17.         99 INVALID
    
18.        100 INVALID
    
19.      73871 INVALID
    
20.      73846 INVALID
    
21. 
    
22. 
    
23. 
    
24. 
    
25. Execution Plan
    
26. ----------------------------------------------------------
    
27. Plan hash value: 3617692013
    
28. 
    
29. --------------------------------------------------------------------------
    
30. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
31. --------------------------------------------------------------------------
    
32. |   0 | SELECT STATEMENT  |      | 23680 |   462K|   284   (1)| 00:00:04 |
    
33. |*  1 |  TABLE ACCESS FULL| T1   | 23680 |   462K|   284   (1)| 00:00:04 |
    
34. --------------------------------------------------------------------------
    
35. 
    
36. Predicate Information (identified by operation id):
    
37. ---------------------------------------------------
    
38. 
    
39.    1 - filter("STATUS"=:A)
    
40. 
    
41. 
    
42. Statistics
    
43. ----------------------------------------------------------
    
44.         403  recursive calls
    
45.           0  db block gets
    
46.          99  consistent gets
    
47.          19  physical reads
    
48.           0  redo size
    
49.        2543  bytes sent via SQL*Net to client
    
50.         589  bytes received via SQL*Net from client
    
51.           8  SQL*Net roundtrips to/from client
    
52.           7  sorts (memory)
    
53.           0  sorts (disk)
    
54.          99  rows processed
    
55. 
    
56. SQL> select status,count(1) from t1 group by status;
    
57. 
    
58. STATUS    COUNT(1)
    
59. ------- ----------
    
60. IND              2
    
61. VALID        70939
    
62. INVALID         99
    
63. 
    
64. SQL> select object_id ,status from t1 where status='INVALID';
    
65. 
    
66. OBJECT_ID STATUS
    
67. ---------- -------
    
68.         92 INVALID
    
69.         93 INVALID
    
70.         94 INVALID
    
71.         95 INVALID
    
72.         96 INVALID
    
73.         97 INVALID
    
74.         98 INVALID
    
75.         99 INVALID
    
76.        100 INVALID
    
77.      73871 INVALID
    
78.      73846 INVALID
    
79. Execution Plan
    
80. ----------------------------------------------------------
    
81. Plan hash value: 14748218
    
82. 
    
83. --------------------------------------------------------------------------------
    
84. -------
    
85. 
    
86. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    
87. --------------------------------------------------------------------------------
    
88. 
    
89. |   0 | SELECT STATEMENT            |         |    79 |  1580 |     3   (0)| 00:00:01 |
    
90. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    79 |  1580 |     3   (0)| 00:00:01 |
    
91. |*  2 |   INDEX RANGE SCAN          | INDE_T1 |    79 |       |     1   (0)| 00:00:01 |
    
92. 
    
93. --------------------------------------------------------------------------------
    
94. Predicate Information (identified by operation id):
    
95. ---------------------------------------------------
    
96. 
    
97.    2 - access("STATUS"='INVALID')
    
98. 
    
99. SQL>

复制代码

为什么使用绑定变量的时候执行计划是全表扫描而带入真实的值就走索引？难道是autotrace的问题吗？如果是的话这种情况应该查找真正的执行计划？麻烦刘总解答一下多谢

aaaaaaaa2000

看了一些文章说的是删除直方图信息再使用 绑定变量的时候可以走正确的执行计划，但是我测试的还是走第一次生成的执行计划....不知是因为版本问题还是我测试的问题，刘总能否解答一下啊多谢了

不了峰

把   :a  改为 :b 试一下

aaaaaaaa2000

1. SQL> select * from v$version where rownum<2;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

复制代码

表信息是刚刚收集的

几番风雨

表结构拿出来看一下呗

aaaaaaaa2000

表结果就是dba_objects

几番风雨

10.2.0.1.0正常，不用绑定变量也不会走索引

不了峰

我来解答一下 我在11.2.0.3.0 数据库上测试的。
测试有点长，我就先给说结论：
可能 Oracle 11g 在autot trace 分析SQL语句带绑定变量时假，可能会显示错误执行计划 。

1. SQL> select * from v$version;
   
2. 
   
3. BANNER
   
4. --------------------------------------------------------------------------------
   
5. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
6. PL/SQL Release 11.2.0.3.0 - Production
   
7. CORE    11.2.0.3.0      Production
   
8. TNS for Linux: Version 11.2.0.3.0 - Production
   
9. NLSRTL Version 11.2.0.3.0 - Production
   
10. 
    
11. SQL>
    
12. 
    
13. 
    
14. 
    
15. SQL> create table t2 as select * from t1;
    
16. 
    
17. Table created.
    
18. 
    
19. SQL>
    
20. SQL> create index ind_t2_status on t2(status) ;
    
21. 
    
22. Index created.
    
23. 
    
24. SQL>
    
25. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ZYF','T2',method_opt => 'for all indexed columns  size skewonly',cascade => TRUE,no_invalidate => FALSE);
    
26. 
    
27. 
    
28. PL/SQL procedure successfully completed.
    
29. 
    
30. 
    
31. SQL> select status,count(1) from t1 group by status;
    
32. 
    
33. STATUS    COUNT(1)
    
34. ------- ----------
    
35. INVALID        130
    
36. VALID      2016984
    
37. IND             62
    
38. 
    
39. 
    
40. SQL>  var a varchar2(10);
    
41. SQL>  exec :a:='INVALID';
    
42. 
    
43. PL/SQL procedure successfully completed.
    
44. 
    
45. SQL>
    
46. SQL>  set autot trace
    
47. SELECT  object_id,status   FROM  T2  WHERE  STATUS = :a;SQL>
    
48. 
    
49. 130 rows selected.
    
50. 
    
51. 
    
52. Execution Plan
    
53. ----------------------------------------------------------
    
54. Plan hash value: 1513984157
    
55. 
    
56. --------------------------------------------------------------------------
    
57. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
58. --------------------------------------------------------------------------
    
59. |   0 | SELECT STATEMENT  |      |   672K|    12M|  7898   (1)| 00:01:35 |
    
60. |*  1 |  TABLE ACCESS FULL| T2   |   672K|    12M|  7898   (1)| 00:01:35 |
    
61. --------------------------------------------------------------------------
    
62. 
    
63. Predicate Information (identified by operation id):
    
64. ---------------------------------------------------
    
65. 
    
66.    1 - filter("STATUS"=:A)
    
67. 
    
68. 
    
69. Statistics
    
70. ----------------------------------------------------------
    
71.           1  recursive calls
    
72.           0  db block gets
    
73.         112  consistent gets               --->这个很奇怪，表那么大，如果走全表的话，consistent gets 这个值太不正常了
    
74.           0  physical reads
    
75.           0  redo size
    
76.        3378  bytes sent via SQL*Net to client
    
77.         612  bytes received via SQL*Net from client
    
78.          10  SQL*Net roundtrips to/from client
    
79.           0  sorts (memory)
    
80.           0  sorts (disk)
    
81.         130  rows processed
    
82. 
    
83. --查看
    
84. 
    
85. SQL> set lines 180
    
86. SQL>
    
87. SQL>
    
88. SQL> select object_id,status   FROM t2 where status='INVALID';
    
89. 
    
90. 130 rows selected.
    
91. 
    
92. 
    
93. Execution Plan
    
94. ----------------------------------------------------------
    
95. Plan hash value: 1740296156
    
96. 
    
97. ---------------------------------------------------------------------------------------------
    
98. | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    
99. ---------------------------------------------------------------------------------------------
    
100. |   0 | SELECT STATEMENT            |               |   361 |  7220 |     9   (0)| 00:00:01 |
     
101. |   1 |  TABLE ACCESS BY INDEX ROWID| T2            |   361 |  7220 |     9   (0)| 00:00:01 |
     
102. |*  2 |   INDEX RANGE SCAN          | IND_T2_STATUS |   361 |       |     3   (0)| 00:00:01 |
     
103. ---------------------------------------------------------------------------------------------
     
104. 
     
105. Predicate Information (identified by operation id):
     
106. ---------------------------------------------------
     
107. 
     
108.    2 - access("STATUS"='INVALID')
     
109. 
     
110. 
     
111. Statistics
     
112. ----------------------------------------------------------
     
113.           1  recursive calls
     
114.           0  db block gets
     
115.         112  consistent gets              -->这个竟然和全表扫描的值一样
     
116.           0  physical reads
     
117.           0  redo size
     
118.        3378  bytes sent via SQL*Net to client
     
119.         612  bytes received via SQL*Net from client
     
120.          10  SQL*Net roundtrips to/from client
     
121.           0  sorts (memory)
     
122.           0  sorts (disk)
     
123.         130  rows processed

复制代码

[ 本帖最后由 不了峰 于 2012-4-17 18:27 编辑 ]

不了峰

2) --接下来我们看一下强制全表扫描的情况

1. SQL> SELECT  /*+ full(T2) */*    FROM  T2  WHerE  STATUS = :a;
   
2. 
   
3. 130 rows selected.
   
4. 
   
5. 
   
6. Execution Plan
   
7. ----------------------------------------------------------
   
8. Plan hash value: 1513984157
   
9. 
   
10. --------------------------------------------------------------------------
    
11. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    
12. --------------------------------------------------------------------------
    
13. |   0 | SELECT STATEMENT  |      |   672K|    62M|  7903   (1)| 00:01:35 |
    
14. |*  1 |  TABLE ACCESS FULL| T2   |   672K|    62M|  7903   (1)| 00:01:35 |
    
15. --------------------------------------------------------------------------
    
16. 
    
17. Predicate Information (identified by operation id):
    
18. ---------------------------------------------------
    
19. 
    
20.    1 - filter("STATUS"=:A)
    
21. 
    
22. 
    
23. Statistics
    
24. ----------------------------------------------------------
    
25.           1  recursive calls
    
26.           0  db block gets
    
27.       28743  consistent gets                          -->从这个看，说明第一个full t2的全表扫描显示的有问题.
    
28.           0  physical reads
    
29.           0  redo size
    
30.       10986  bytes sent via SQL*Net to client
    
31.         612  bytes received via SQL*Net from client
    
32.          10  SQL*Net roundtrips to/from client
    
33.           0  sorts (memory)
    
34.           0  sorts (disk)
    
35.         130  rows processed
    
36. 
    
37. SQL>
    
38. 
    
39. 
    
40. --这里我们做个10046的trace跟踪一下 最原始的带绑定变量的sql
    
41. 注意 我把select 从大写改为小写，为生成一个新的执行计划
    
42. 
    
43. sql> exit
    
44. 
    
45. sqlplus   zyf/zyf
    
46. 
    
47. 
    
48. SQL>
    
49. SQL> alter session set events '10046 trace name context forever, level 12';
    
50. 
    
51. Session altered.
    
52. 
    
53. SQL>  select   object_id,status   from   T2  WHERE  STATUS = :a;
    
54. 
    
55. OBJECT_ID STATUS
    
56. ---------- -------
    
57.      56921 INVALID
    
58.      56966 INVALID
    
59.      64886 INVALID
    
60.      ....
    
61.      
    
62. 
    
63. SQL>
    
64. SQL> alter session set events '10046 trace name context off';
    
65. 
    
66. Session altered.
    
67. 
    
68. 
    
69. --分析trace
    
70. 
    
71. tkprof nitmsdb1_ora_1462.trc zyf.log
    
72. 
    
73. $ more zyf.log
    
74. 
    
75.         select   object_id,status   
    
76.         from
    
77.            T2  WHERE  STATUS = :a
    
78.        
    
79.        
    
80.         call     count       cpu    elapsed       disk      query    current        rows
    
81.         ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
82.         Parse        1      0.00       0.00          0          0          0           0
    
83.         Execute      1      0.00       0.00          0          0          0           0
    
84.         Fetch       10      0.00       0.00          0        112          0         130
    
85.         ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    
86.         total       12      0.00       0.00          0        112          0         130
    
87.        
    
88.         Misses in library cache during parse: 1
    
89.         Misses in library cache during execute: 1
    
90.         Optimizer mode: ALL_ROWS
    
91.         Parsing user id: 59  
    
92.         Number of plan statistics captured: 1
    
93.        
    
94.         Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    
95.         ---------- ---------- ----------  ---------------------------------------------------
    
96.                130        130        130  TABLE ACCESS BY INDEX ROWID T2 (cr=112 pr=0 pw=0 time=3113 us cost=9 size=722
    
97.         0 card=361)
    
98.                130        130        130   INDEX RANGE SCAN IND_T2_STATUS (cr=12 pr=0 pw=0 time=244 us cost=3 size=0 ca
    
99.         rd=361)(object id 65914)     
    
100.        
     
101.         发现query=112 ，访问表不是全表扫描，而是访问索引
     
102.        
     
103.        
     
104.         所以上面的分析，可能说明11g 在autot trace SQL语句绑定变量时，显示的可能是错误 的信息

复制代码

不了峰

3) 我们再通过另一个方法证明

1. exit
   
2. 
   
3. sqlplus zyf/zyf
   
4. 
   
5. 
   
6. SQL>  var a varchar2(10);
   
7. SQL>  exec :a:='INVALID';
   
8. 
   
9. PL/SQL procedure successfully completed.
   
10. 
    
11. SQL>  select  object_id,status   from   T2  WHERE  STATUS = :a;
    
12. 
    
13. OBJECT_ID STATUS
    
14. ---------- -------
    
15.      56921 INVALID
    
16.      56966 INVALID
    
17.      64886 INVALID
    
18.      ........
    
19. 
    
20. SQL> set lines 180
    
21. SQL>  SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'allstats last'));
    
22. 
    
23. PLAN_TABLE_OUTPUT
    
24. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
25. SQL_ID  3t6wf2h7agmyh, child number 0
    
26. -------------------------------------
    
27. select  object_id,status   from   T2  WHERE  STATUS = :a
    
28. 
    
29. Plan hash value: 1740296156
    
30. 
    
31. -------------------------------------------------------------------------------------------------------
    
32. | Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    
33. -------------------------------------------------------------------------------------------------------
    
34. |   0 | SELECT STATEMENT            |               |      1 |        |    130 |00:00:00.01 |     112 |
    
35. |   1 |  TABLE ACCESS BY INDEX ROWID| T2            |      1 |    361 |    130 |00:00:00.01 |     112 |
    
36. 
    
37. PLAN_TABLE_OUTPUT
    
38. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
39. |*  2 |   INDEX RANGE SCAN          | IND_T2_STATUS |      1 |    361 |    130 |00:00:00.01 |      12 |
    
40. -------------------------------------------------------------------------------------------------------
    
41. 
    
42. Predicate Information (identified by operation id):
    
43. ---------------------------------------------------
    
44. 
    
45.    2 - access("STATUS"=:A)
    
46. 
    
47. 
    
48. 19 rows selected.
    
49. 
    
50. SQL>
    
51. 说明带绑定变量的这句SQL也是走索引扫描 .

复制代码

Maclean Liu(刘相兵

不了峰  同学的 演示 很好！

也可以参考 autotrace在绑定变量情况下不准确的问题 http://www.oracledatabase12g.com ... 97%AE%E9%A2%98.html


autotrace 给出的执行计划未必是真实的， 最可靠的是 10046 、10053 trace 中的real execution plan

不了峰

原来刘大09年已经搞清楚这事了

班门弄斧了

aaaaaaaa2000

多谢解答啊！！！！！