首页 > 数据库 > Oracle >

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

2014-02-19

在ORACLE数据库里通常可以使用如下的四种方法来得到目标SQL的执行计划: 1,EXPLAIN PLAN命令 2,DBMS_XPLAN包 3,SQLPLUS中的AUTOTRACE开关 4,10046事件 除了第四种方法外,其他的三种方法得到的执行计划都有

在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,&#39;ADVANCED&#39;)得到目标SQL的真实执行计划如下所示:

SQL> select * from table(dbms_xplan.display_cursor(null,null,&#39;advanced&#39;));

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(&#39;10.2.0.1&#39;)

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

相关文章
最新文章
热点推荐