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

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

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

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

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

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