关于autotrace和explain plan是否可以反映真实的执行计划
一、引言：
今天在测试绑定变量的时候，发现使用绑定变量时，用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: 9f42yhqpkqanq 138 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事件的示例省略。
