关于autotrace和explain plan是否可以反映真实的执行计划

一、引言：

今天在测试绑定变量的时候，发现使用绑定变量时，用autotrace看的执行计划有误，由此想到autotrace和explain plan是否可以反映真实的执行计划？

实验环境：

操作系统：rhel 5.4 x32

数据库：oracle 11g r2

二、实验内容：

在这里以autotrace为例子：

----创建一张jack_tab表，其中表里面的数值只有2个值，id=99只有1条记录，剩下的全部等于1----

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 3for 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 40WHERE 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 43WHERE v.NAME = 'thread' 44 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, 45 (SELECT VALUE 46 FROM v$parameter 47WHERE 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 ---------------------------------------------------------- 83304 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 Plan103 ----------------------------------------------------------104 Plan hash value: 1314397703105 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 Statistics120 ----------------------------------------------------------121 1 recursive calls122 0 db block gets123 3 consistent gets124 0 physical reads125 0 redo size126 1448 bytes sent via SQL*Net to client127419 bytes received via SQL*Net from client128 2 SQL*Net roundtrips to/from client129 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: 1314397703139 select * 140 from141 jack_tab w_was_1 where id=:w142 143 144 callcount cpu elapsed diskquery current rows145 ------- ------ -------- ---------- ---------- ---------- ---------- ----------146 Parse 10.00 0.00000 0147 Execute10.00 0.00000 0148 Fetch48360.03 0.200 58340 72523149 ------- ------ -------- ---------- ---------- ---------- ---------- ----------150 total48380.04 0.210 58340 72523151 152 Misses in library cache during parse: 1153 Optimizer mode: ALL_ROWS154 Parsing user id: 105 155 156 RowsRow Source Operation157 ------- ---------------------------------------------------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: 1razvka48c332163 Plan Hash: 2073030600164 select * 165 from166 jack_tab w_was_99 where id=:w167 168 169 callcount cpu elapsed diskquery current rows170 ------- ------ -------- ---------- ---------- ---------- ---------- ----------171 Parse 10.00 0.00000 0172 Execute10.00 0.02000 0173 Fetch 20.00 0.00030 1174 ------- ------ -------- ---------- ---------- ---------- ---------- ----------175 total 40.00 0.02030 1176 177 Misses in library cache during parse: 1178 Optimizer mode: ALL_ROWS179 Parsing user id: 105 180 181 RowsRow Source Operation182 ------- ---------------------------------------------------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 ********************************************************************************

原来第二条语句实际上走的是索引访问，所以才只有3个一致性读。

简单来说，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 Plan21 ----------------------------------------------------------22 Plan hash value: 131439770323 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 Statistics38 ----------------------------------------------------------39627 recursive calls40 0 db block gets4180 consistent gets4219 physical reads43 0 redo size44 1448 bytes sent via SQL*Net to client45419 bytes received via SQL*Net from client46 2 SQL*Net roundtrips to/from client47 7 sorts (memory)48 0 sorts (disk)49 1 rows processed50 51 SQL> alter session set sql_trace=false;52 53 Session altered.54 55 SQL ID: 2p8s73ypacqgs56 Plan Hash: 207303060057 select * 58 from59 jack_tab z_was_99 where id=:z60 61 62 callcount cpu elapsed diskquery current rows63 ------- ------ -------- ---------- ---------- ---------- ---------- ----------64 Parse 10.01 0.03 13 410 065 Execute10.00 0.003 360 066 Fetch 20.00 0.00330 167 ------- ------ -------- ---------- ---------- ---------- ---------- ----------68 total 40.02 0.04 19 800 169 70 Misses in library cache during parse: 171 Optimizer mode: ALL_ROWS72 Parsing user id: 105 73 74 RowsRow Source Operation75 ------- ---------------------------------------------------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事件的示例省略。