ORACLE数据库SQL优化---)如何执行计划的执行顺序

建议安装（10g即以上版本上）xplan package，XPALN包其实是对DBMS_XPLAN包的封装，使用XPLAN包就可以很清晰的看到执行计划的执行顺序。

安装很简单：其中xplan.sql_.txt可以到如下的网站下载：

http://www.dbsnake.net/wp-content/uploads/2012/08/xplan.sql_.txt

[oracle@vmoel5u4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 12 10:59:14 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @xplan.sql_.txt

Type created.

Type created.

old 13: &&_awr_start
new 13: /*
old 19: &&_awr_end
new 19: */

Package created.

old 270: &_awr_start
new 270: /*
old 342: &_awr_end
new 342: */

Package body created.

下面我用刚才已经安装好的XPLAN包的同名方法来查看目标SQL执行计划：

SQL> select count(*) from hr.t1;

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

SQL> select sql_text,sql_id,child_number from v$sql where sql_text like 'select count(*) from hr.t1%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID CHILD_NUMBER
------------- ------------
select count(*) from hr.t1
bdw8bnh2qhwcm 0

SQL> select * from table(xplan.display_cursor('bdw8bnh2qhwcm',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bdw8bnh2qhwcm, child number 0
-------------------------------------
select count(*) from hr.t1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | 3 | SELECT STATEMENT | | | 617 (100)| |
| 1 | 2 | SORT AGGREGATE | | 1 | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | 1 | TABLE ACCESS FULL| T1 | 201K| 617 (2)| 00:00:08 |
---------------------------------------------------------------------------

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

1 - SEL$1
2 - SEL$1 / T1@SEL$1

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

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/

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

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

38 rows selected.

从上面可以看出有一个ORDER列，它列出了目标SQL的实际执行步骤。