一、引言：
今天在测试绑定变量的时候，发现使用绑定变量时，用autotrace看的执行计划有误，由此想到autotrace和explain plan是否可以反映真实的执行计划？
实验环境：
操作系统：rhel 5.4 x32
数据库：oracle 11g r2
二、实验内容：
在这里以autotrace为例子：
----创建一张jack_tab表，其中表里面的数值只有2个值，id=99只有1条记录，剩下的全部等于1----原来第二条语句实际上走的是索引访问，所以才只有3个一致性读。
1 SQL> create table jack_tab as select 1 id,a.* from dba_objects a; 2
3 Table created.
4
5 SQL> update jack_tab set id=99 where rownum=1;
6
7 1 row updated.
8
9 SQL> commit;
10
11 Commit complete.
12
13 SQL> create index jack_tab_ind on jack_tab(id);
14
15 Index created.
16
17 SQL> analyze table jack_tab compute statistics
18 2 for table
19 3 for all indexes
20 4 for all indexed columns size 2;
21
22 Table analyzed.
23
24 SQL> @/u01/scripts/showtrace
25
26 trace_file_name
27 --------------------------------------------------------------------------------
28 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_5626.trc
29
30
31 [oracle@yft ~]$ cat /u01/scripts/showtrace.sql
32 SELECT d.VALUE
33 || '/'
34 || LOWER (RTRIM(i.INSTANCE,CHR(0)))
35 || '_ora_'
36 || p.spid
37 || '.trc' as "trace_file_name"
38 FROM (SELECT p.spid
39 FROM v$mystat m,v$session s,v$process p
40 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
41 (SELECT t.INSTANCE
42 FROM v$thread t,v$parameter v
43 WHERE v.NAME = 'thread'
44 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
45 (SELECT VALUE
46 FROM v$parameter
47 WHERE NAME = 'user_dump_dest') d;
48
49 SQL> variable w number;
50 SQL> alter session set sql_trace=true;
51
52 Session altered.
53
54 SQL> set autotrace trace exp stat
55 SQL> exec :w:=1;
56
57 PL/SQL procedure successfully completed.
58
59 SQL> select * from jack_tab w_was_1 where id=:w;
60
61 72523 rows selected.
62
63
64 Execution Plan
65 ----------------------------------------------------------
66 Plan hash value: 1314397703
67
68 ------------------------------------------------------------------------------
69 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
70 ------------------------------------------------------------------------------
71 | 0 | SELECT STATEMENT | | 36262 | 3647K| 191 (1)| 00:00:03 |
72 |* 1 | TABLE ACCESS FULL| JACK_TAB | 36262 | 3647K| 191 (1)| 00:00:03 |
73 ------------------------------------------------------------------------------
74
75 Predicate Information (identified by operation id):
76 ---------------------------------------------------
77
78 1 - filter("ID"=TO_NUMBER(:W))
79
80
81 Statistics
82 ----------------------------------------------------------
83 304 recursive calls
84 0 db block gets
85 5864 consistent gets
86 0 physical reads
87 0 redo size
88 3473648 bytes sent via SQL*Net to client
89 53593 bytes received via SQL*Net from client
90 4836 SQL*Net roundtrips to/from client
91 0 sorts (memory)
92 0 sorts (disk)
93 72523 rows processed
94
95 SQL> exec :w:=99;
96
97 PL/SQL procedure successfully completed.
98
99 SQL> select * from jack_tab w_was_99 where id=:w;
100
101
102 Execution Plan
103 ----------------------------------------------------------
104 Plan hash value: 1314397703
105
106 ------------------------------------------------------------------------------
107 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
108 ------------------------------------------------------------------------------
109 | 0 | SELECT STATEMENT | | 36262 | 3647K| 191 (1)| 00:00:03 |
110 |* 1 | TABLE ACCESS FULL| JACK_TAB | 36262 | 3647K| 191 (1)| 00:00:03 |
111 ------------------------------------------------------------------------------
112
113 Predicate Information (identified by operation id):
114 ---------------------------------------------------
115
116 1 - filter("ID"=TO_NUMBER(:W))
117
118
119 Statistics
120 ----------------------------------------------------------
121 1 recursive calls
122 0 db block gets
123 3 consistent gets
124 0 physical reads
125 0 redo size
126 1448 bytes sent via SQL*Net to client
127 419 bytes received via SQL*Net from client
128 2 SQL*Net roundtrips to/from client
129 0 sorts (memory)
130 0 sorts (disk)
131 1 rows processed ----在这里可以看到怎么走全表扫描一致性读只有3个。
132
133 SQL> alter session set sql_trace=false;
134
135 Session altered.
136
----trace文件内容：----
137 SQL ID: 9f42yhqpkqanq138 Plan Hash: 1314397703
139 select *
140 from
141 jack_tab w_was_1 where id=:w
142
143
144 call count cpu elapsed disk query current rows
145 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
146 Parse 1 0.00 0.00 0 0 0 0
147 Execute 1 0.00 0.00 0 0 0 0
148 Fetch 4836 0.03 0.20 0 5834 0 72523
149 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
150 total 4838 0.04 0.21 0 5834 0 72523
151
152 Misses in library cache during parse: 1
153 Optimizer mode: ALL_ROWS
154 Parsing user id: 105
155
156 Rows Row Source Operation
157 ------- ---------------------------------------------------
158 72523 TABLE ACCESS FULL JACK_TAB (cr=5834 pr=0 pw=0 time=159266 us cost=191 size=7469869 card=72523)
159
160 ********************************************************************************
161
162 SQL ID: 1razvka48c332
163 Plan Hash: 2073030600
164 select *
165 from
166 jack_tab w_was_99 where id=:w
167
168
169 call count cpu elapsed disk query current rows
170 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
171 Parse 1 0.00 0.00 0 0 0 0
172 Execute 1 0.00 0.02 0 0 0 0
173 Fetch 2 0.00 0.00 0 3 0 1
174 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
175 total 4 0.00 0.02 0 3 0 1
176
177 Misses in library cache during parse: 1
178 Optimizer mode: ALL_ROWS
179 Parsing user id: 105
180
181 Rows Row Source Operation
182 ------- ---------------------------------------------------
183 1 TABLE ACCESS BY INDEX ROWID JACK_TAB (cr=3 pr=0 pw=0 time=0 us cost=2 size=103 card=1)
184 1 INDEX RANGE SCAN JACK_TAB_IND (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 75319)
185
186 ********************************************************************************
简单来说，explain plan时不使用绑定变量peeking机制，所以它也许显示不了实际的执行计划。
autotrace traceonly explain会硬分析一次，但不执行，硬分析时也不使用绑定变量peeking机制，也就是说它也显示不了实际的执行计划。
其它的autotrace形式都会执行一次，使用绑定变量peeking机制，但随后显示出现的执行计划都不是实际的执行计划。
在上面的例子中，可以看到虽然第一次的执行计划是正确的，但是第二次的执行计划就不是实际的执行计划。在看一下单独执行第二条语句的情况：
----当单独使用第二条语句查询时，还是无法显示实际的执行计划----
1 SQL> variable z number; 2 SQL> alter session set sql_trace=true;
3
4 Session altered.
5
6 SQL> exec :z:=99;
7
8 PL/SQL procedure successfully completed.
9
10 SQL> alter system flush shared_pool;
11
12 System altered.
13
14 SQL> alter system flush buffer_cache;
15
16 System altered.
17
18 SQL> select * from jack_tab z_was_99 where id=:z;
19
20 Execution Plan
21 ----------------------------------------------------------
22 Plan hash value: 1314397703
23
24 ------------------------------------------------------------------------------
25 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
26 ------------------------------------------------------------------------------
27 | 0 | SELECT STATEMENT | | 36262 | 3647K| 191 (1)| 00:00:03 |
28 |* 1 | TABLE ACCESS FULL| JACK_TAB | 36262 | 3647K| 191 (1)| 00:00:03 |
29 ------------------------------------------------------------------------------
30
31 Predicate Information (identified by operation id):
32 ---------------------------------------------------
33
34 1 - filter("ID"=TO_NUMBER(:Z))
35
36
37 Statistics
38 ----------------------------------------------------------
39 627 recursive calls
40 0 db block gets
41 80 consistent gets
42 19 physical reads
43 0 redo size
44 1448 bytes sent via SQL*Net to client
45 419 bytes received via SQL*Net from client
46 2 SQL*Net roundtrips to/from client
47 7 sorts (memory)
48 0 sorts (disk)
49 1 rows processed
50
51 SQL> alter session set sql_trace=false;
52
53 Session altered.
54
55 SQL ID: 2p8s73ypacqgs
56 Plan Hash: 2073030600
57 select *
58 from
59 jack_tab z_was_99 where id=:z
60
61
62 call count cpu elapsed disk query current rows
63 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
64 Parse 1 0.01 0.03 13 41 0 0
65 Execute 1 0.00 0.00 3 36 0 0
66 Fetch 2 0.00 0.00 3 3 0 1
67 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
68 total 4 0.02 0.04 19 80 0 1
69
70 Misses in library cache during parse: 1
71 Optimizer mode: ALL_ROWS
72 Parsing user id: 105
73
74 Rows Row Source Operation
75 ------- ---------------------------------------------------
76 1 TABLE ACCESS BY INDEX ROWID JACK_TAB (cr=3 pr=3 pw=0 time=0 us cost=2 size=103 card=1)
77 1 INDEX RANGE SCAN JACK_TAB_IND (cr=2 pr=2 pw=0 time=0 us cost=1 size=0 card=1)(object id 75319)
所以，当我们在用绑定变量的时候，如果想要得到真实的执行计划可以使用sql_trace和10046事件来查看。在这里10046事件的示例省略。