autotrace在绑定变量情况下不准确的问题
通常我们在检验SQL执行计划时采用autotrace的方法，但autotrace本身存在许多不准确的情况。
以下为一个例子：
SQL> create table test(t1 int, t2 char(200));
表已创建。
SQL> create index ind_t2 on test(t2);
索引已创建。
SQL> insert into test values (0,'A');
已创建 1 行。
SQL> commit;
提交完成。
SQL> begin
2 for i in 1..100000 loop
3 insert into test values(i,'ZZZZ');
4 end loop;
5 commit;
6 end;
7 /
SQL> analyze table test compute statistics ;
表已分析。
SQL> analyze index ind_t2 compute statistics;
索引已分析
SQL> analyze table test compute statistics for all indexed columns;
表已分析。
以上代码 在test表中 产生一条t2为A的记录以及10万条t2为ZZZZ的语句，即列上值出现严重的倾斜。
SQL> set autotrace on;
SQL> variable a char;
SQL> exec :a:='A';
SQL> alter system flush shared_pool;
系统已更改。
PL/SQL 过程已成功完成。
SQL> oradebug setmypid;
已处理的语句
SQL> oradebug event 10046 trace name context forever,level 10;
已处理的语句
SQL> select * from test where t2=:a;
T1
----------
T2
--------------------------------------------------------------------------
0
A
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50001 | 9961K| 652 (2)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| TEST | 50001 | 9961K| 652 (2)| 00:00:08 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"=:A)
统计信息
----------------------------------------------------------
231 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
654 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> oradebug tracefile_name;
e:\oracle\product\10.2.0\admin\orclv\udump\orclv_ora_4956.trc
使用tkprof 工具对 trace文件整理
tkprof e:\oracle\product\10.2.0\admin\orclv\udump\orclv_ora_4956.trc C:\ora_4956.trc
可以找到以上查询的实际执行计划。
select *
from
test where t2=:a
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 6 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST (cr=6 pr=0 pw=0 time=43 us)
1 INDEX RANGE SCAN IND_T2 (cr=5 pr=0 pw=0 time=32 us)(object id 51539)
可以看到这里实际的执行计划时 INDEX RAGNE SCAN 而非TABLE ACCESS FULL，这是由于优化器(optimizer)实际使用了绑定变量窥视的手段，而autotrace工具似乎不具备这种特性，故其展现的执行计划出现严重偏差。
一般情况下autotrace的结果仍是准确的，但也仅是一般情况，这需要我们凭借直觉去分辨。