ORACLE数据库SQL优化--->如何得到真实的执行计划

在ORACLE数据库里通常可以使用如下的四种方法来得到目标SQL的执行计划：

1，EXPLAIN PLAN命令

2，DBMS_XPLAN包

3，SQLPLUS中的AUTOTRACE开关

4，10046事件

除了第四种方法外，其他的三种方法得到的执行计划都有可能不准确。在ORACLE数据库中判断得到的执行计划是否准确，就是看目标SQL是否被真正的执行，真正执行过的SQL所对应的执行计划就是准确的，反之，则有可能不准确。

 

对于使用第二种方法(DBMS_XPLAN)而言，针对不同的应用场景，你可以选择如下四种方式的一种。

a, select * from table(dbms_xplan.display)

例如：

SQL> explain plan for select * from hr.employees;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7276 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7276 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

 

b,select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

c,select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));

d,select * from table(dbms_xplan.display_awr('sql_id'));

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

 

 对第三种方法（即使用SQLPLUS中的AUTOTRACE）而言，可以有下面几种方法来开启：

set autotrace on;(目标SQL都已经被执行)

set autotrace traceonly;(目标SQL都已经被执行)

set autotrace traceonly explain; (对于查询目标SQL时，是没有被实际执行，但是如果目标SQL是DML语句时，这个时候DML是实际上已经被执行了)

由于SET AUTOTRACE命令后显示的执行计划实际上是来源于调用EXPLIAN PLAN命令，而用EXPLAIN PLAN命令得到的执行计划有可能不准确（特别是在使用了绑定变量的情况下），所以使用SET AUTOTRACE命令所显示的执行计划也有可能不准确。

看一个如下的例子来验证下使用explain plan和set autotrace命令后得到的执行计划并不是目标SQL真实执行计划：

SQL> show user
USER is "HR"

SQL> create table T1 as select * from dba_objects;

Table created.

 

 

SQL> insert into t1 select * from t1;

50319 rows created.

SQL> commit;

Commit complete.

SQL> insert into t1 select * from t1;

100638 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
    201276

 在表T1的OBJECT_ID列上建立一个单键值的B树索引IDX_T1

SQL> create index idx_t1 on t1(object_id);

Index created.

对T1表收集一下统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T1',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

创建2个绑定变量X和Y，X=0，Y=100000

SQL> var x number;
SQL> var y number;
SQL> exec :x :=0

PL/SQL procedure successfully completed.

SQL> exec :y :=100000

PL/SQL procedure successfully completed.

查看如下语句的执行计划：

 

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

Explained.

SQL> 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 |   503 |  2515 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
   3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y))

16 rows selected.

从上面可以看出使用EXPLAIN PLAN得到的执行计划显示目标SQL走的是对索引IDX_T1的索引范围扫描。

但是实际情况是否是这样呢？我们实际执行下上面的语句：

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

  COUNT(*)
----------
    201276

用DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED')得到目标SQL的真实执行计划如下所示：

SQL> 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
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-

|   0 | SELECT STATEMENT       |        |       |       |   106 (100)|
|

|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |
|

|*  2 |   FILTER               |        |       |       |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

|*  3 |    INDEX FAST FULL SCAN| IDX_T1|   201K|   982K|   106   (7)| 00:00:02
|

--------------------------------------------------------------------------------
-

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 0
   2 - :Y (NUMBER): 100000

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

   2 - filter(:X<=:Y)
   3 - filter(("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

51 rows selected.

从上面的执行计划可以看出，现在目标SQL实际的执行计划是走对索引IDX_T1的索引快速全扫描，这才是目标SQL的真实的执行计划，即刚才用EXLPAIN PLAN命令得到的计划是不准确的。

 

我们再来看下，使用SET AUTOTRACE 命令的情况。打开当前SESSION的AUTOTRACE:

SQL> set autotrace traceonly
SQL> 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 |   503 |  2515 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER(:X)<=TO_NUMBER(:Y))
   3 - access("OBJECT_ID">=TO_NUMBER(:X) AND "OBJECT_ID"<=TO_NUMBER(:Y))

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

从上面可以看出使用SET AUTOTRACE TRACEONLY后得到的执行计划和之前用EXPLAIN PLAN命令得到的执行计划是一样的。即此时的SET AUTOTRACE TRACEONLY所得到的执行计划是不准确的。

 

结论：通过上面的实验可以证明使用了SET AUTOTRACE命令后显示的执行计划实际上是来源于调用EXPLAIN PLAN命令，而EXPLAIN PLAN命令所得到的执行计划可能是不准确的（特别是在绑定变量的时候），索引使用SET AUTORACE命令的所显示的执行计划可能是不准确的。

 

ORACLE数据库还有如下方法得到真实的执行计划：

如果是ORACLE 10G及其以上的版本，该SQL的执行计划又已经被ORACLE捕获并存储到了REPOSITORY中，在可以使用AWR SQL报告来得到真实的历史执行计划。