首页 > 数据库开发 > ORACLE数据库SQL优化—>如何得到真实的执行计划

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报告来得到真实的历史执行计划。


本文固定链接: http://www.devba.com/index.php/archives/4975.html | 开发吧

报歉!评论已关闭.