Oracle中如何得到真实的执行计划

397次阅读

共计 5425 个字符，预计需要花费 14 分钟才能阅读完成。

之前介绍过 4 种在 Oracle 数据库里查看执行计划的方法：

explain plan 命令
DBMS_XPLAN包
SQLPLUS中的 AUTOTRACE 开关
10046事件

其中除了第四种方法之外，其他三种方法得到的执行计划都有可能是不准确的。在 Oracle 中判断得到的执行计划是否是准确，就是看目标 SQL 是否被真正执行，真正执行过的 SQL 所对应的执行计划就是准确的，反之则有可能不准。但是这里的判断原则从严格意义上来说并不适用于 AUTOTRACE 开关，因为所有的 AUTOTRACE 开关所显示的执行计划都可能是不准的，即使对应的目标 SQL 实际上已经执行过。

下面我们就用上述原则来判断除第 4 种以外的其他三种方法中哪些得到的执行计划是准的，哪些方法得到的执行计划有可能不准。

1、explain plan命令

对这种方法得到的执行计划而言，因为此时的目标 SQL 并没有被实际执行，所以该方法得到的执行计划有可能是不准的，尤其是目标 SQL 包含绑定变量时。在默认开启绑定变量窥探 (Bind Peeking) 的情况，对含绑定变量的目标 SQL 使用 explain plan 得到的执行计划只是一个半成品，Oracle在随后对该 SQL 的绑定变量进行窥探后就得到了这些绑定变量具体的值，此时 Oralce 可能会对上述半成品的执行计划做调整，一量做了调整，使用 explain plan 命令得到的执行计划就不准了。

2、DBMS_XPLAN包

对于这种方法而言，针对不同的应用场景，可以选择如下四种方式中的一种：

select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display_cursor(null,null,’advanced’));
select * from table(dbms_xplan.display_cursor(‘sql_id/hash_value’,child_cursor_number,’advanced’));
select * from table(dbms_xplan.display_awr(‘sql_id’));

显然，执行 select * from table(dbms_xplan.display) 得到的执行计划可能是不准的，因为它只是用于查看使用 explain plan 命令得到的目标 SQL 的执行计划，目标 SQL 此时还没有被真正执行，所以用它得到的执行计划可能是不准的。使用剩下的三种方式得到的执行计划都是准的，因为此时目标 SQL 都已经被实际执行过了。

3、AUTOTRACE开关

使用这种方法，可以选择如下三种方式来开启 TRACE 开关

SET AUTOTRACE ON
SET AUTOTRACE TRACEONLY
SET AUTOTRACE TRACEONLY EXPLAIN

上述三种方法中，当使用 SET AUTOTRACE ON 和SET AUTOTRACE TRACEONLY时，目标 SQL 都已经被实际执行过了，正是因为被实际执行过，所以 SET AUTOTRACE ON 和SET AUTOTRACE TRACEONLY的情况下我们能看到目标 SQL 的实际资源消耗情况。当使用 SET AUTOTRACE TRACEONLY EXPLAIN 时，如果执行的是 SELECT 语句，则该 SELECT 语句并没有被 Oracle 实际执行，但如果执行的是 DML 语句，��况就不一样了，此时的 DML 语句会被 Oracle 实际执行的。虽然使用部分 SET AUTOTRACE 命令后目标 SQL 实际上已经执行过了，但所得到的执行计划有可能是不准的，因为使用 SET AUTOTRACE 命令所显示的执行计划都是来源于调用 explain plan 命令。

下面使用一个例子证明：

scott@ORCL>create table t1 as select * from dba_objects;

Table created.

scott@ORCL>insert into t1 select * from t1;

86885 rows created.

scott@ORCL>commit;

Commit complete.

scott@ORCL>select count(*) from t1;

COUNT(*)
———-
173770

scott@ORCL>create index idx_t1 on t1(object_id);

Index created.

scott@ORCL>exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

scott@ORCL>var x number;
scott@ORCL>var y number;
scott@ORCL>exec :x := 0;

PL/SQL procedure successfully completed.

scott@ORCL>exec :y := 100000;

PL/SQL procedure successfully completed.

scott@ORCL>explain plan for select count(*) from t1 where object_id between :x and :y;

Explained.

scott@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
Plan hash value: 2351893609

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 434 | 2170 | 3 (0)| 00:00:01 |
—————————————————————————–

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

2 – filter(TO_NUMBER(:Y)>=TO_NUMBER(:X))
3 – access(“OBJECT_ID”>=TO_NUMBER(:X) AND “OBJECT_ID”<=TO_NUMBER(:Y))

16 rows selected.

scott@ORCL>select count(*) from t1 where object_id between :x and :y;

COUNT(*)
———-
173380

scott@ORCL>select * from table(dbms_xplan.display_cursor(null,null,’ADVANCED’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
SQL_ID 9dhu3xk2zu531, child number 0
————————————-
select count(*) from t1 where object_id between :x and :y

Plan hash value: 1410530761

———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | | | 107 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX FAST FULL SCAN| IDX_T1 | 172K| 843K| 107 (1)| 00:00:02 |
———————————————————————————

…… 省略部分输出

scott@ORCL>set autotrace traceonly
scott@ORCL>select count(*) from t1 where object_id between :x and :y;

Execution Plan
———————————————————-
Plan hash value: 2351893609

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 434 | 2170 | 3 (0)| 00:00:01 |
—————————————————————————–

从上面显示内容可以看到，使用 SET AUTOTRACE ON 得到的执行计划和之前 explain plan 得到的执行计划也是一模一样的，即此时使用 SET AUTOTRACE ON 所得到的执行计划也是不准的。

另外，如果目标 SQL 的执行计划已经被 age out 出Shared Pool了，此时如何得到 SQL 的真实执行计划呢？

如果是 Oracle 10g 及其以上版本，该 SQL 的执行计划已经被 Oracle 捕获并存储到了 AWR Repository 中，则可以使用 AWR SQL 报告来得到真实的历史执行计划。
如果是 Oracle 9i，通常情况下已经没有办法再得到该SQL 的执行计划，除非额外部署了 Statspack 报告，并且采集 Statspack 报告的 level 值大于或等于6。