set autotrace打印出来的执行计划不一定准确

最新推荐文章于 2021-04-03 09:41:45 发布

原创最新推荐文章于 2021-04-03 09:41:45 发布 · 828 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#AUTOTRAC #EXPLAIN PLAN #10046

性能调优专栏收录该内容

10 篇文章

订阅专栏

本文通过实验对比了SET AUTOTRACE与10046事件在获取Oracle SQL执行计划方面的准确性。发现SET AUTOTRACE虽然执行了SQL语句，但其显示的执行计划可能与实际执行情况不符，而10046事件则能提供更为准确的执行计划。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://blog.youkuaiyun.com/robo23/article/details/42616067

文章里简单地比较了EXPLAIN PLAN FOR和SET AUTOTRACE的方法来打印执行计划。

但偶然的机会我发现SET AUTOTRCE尽管有真实去执行SQL，但打印出来的执行计划并不一定准确。看如下实验：

T表有10万条记录，A字段有普通索引，A字段的选择性很好：

<span style="font-size:14px;">SQL> set autotrace on;
SQL> alter session set events '10046 trace name context forever,level 12';  <<<<

Session altered.

SQL> variable a number;
SQL> execute :a := 99999; <<<<<<<

PL/SQL procedure successfully completed.

SQL> select /*bind_peek*/count(b) from t where a < :a; <<<<<<<<<<
  COUNT(B)
----------
     99998


Execution Plan
----------------------------------------------------------
Plan hash value: 3565334791

----------------------------------------------------------------------------------------
| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     8 |     5  (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |     8 |     |        |
|   2 |   TABLE ACCESS BY INDEX ROWID| T       |  5000 | 40000 |     5  (0)| 00:00:01 |
|*  3 |    <strong>INDEX RANGE SCAN</strong>      | IND_T_A |   900 |       |     3  (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"<TO_NUMBER(:A))


Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
185  consistent gets
  0  physical reads
  0  redo size
528  bytes sent via SQL*Net to client
524  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_24324.trc   <<<<<<<<</span>

接下来,我们去检查一下trace文件内容: