前面讲了五种查看Oracle目标SQL执行计划的方法：
Oracle查看执行计划（一）
Oracle查看执行计划（二）
Oracle查看执行计划（三）
Oracle查看执行计划（四）
Oracle查看执行计划（五）
其中第一种和第二种在目标SQL使用了绑定变量的情况下，获得的执行计划有可能是不准的。

创建测试表：
SQL> create table test1 as select * from dba_objects;

Table created.

SQL> select count(*) from test1;

COUNT(*)
----------
92110

创建索引：
SQL> create index idx_test1_id on test1(object_id);

Index created.

进行更新：
SQL> update test1 set object_id=1 where object_id<90000;

86864 rows updated.

SQL> commit;

Commit complete.

收集统计信息：
SQL> exec dbms_stats.gather_table_stats('SYS','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns');

PL/SQL procedure successfully completed.

创建一个绑定变量并赋值：
SQL> var x number;
SQL> exec :x := 1;

PL/SQL procedure successfully completed.

使用方法一获取执行计划：
SQL> explain plan for select count(*) from test1 where object_id = :x;

Explained.

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3237771381

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=TO_NUMBER(:X))

14 rows selected.

使用方法二获取执行计划：
SQL> set autot traceonly
SQL> select count(*) from test1 where object_id=:x;

Execution Plan
----------------------------------------------------------
Plan hash value: 3237771381

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=TO_NUMBER(:X))

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

SQL> set autot off

使用方法三获取执行计划：
SQL> select count(*) from test1 where object_id=:x;

COUNT(*)
----------
86864

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID drj4fkb9vj73m, child number 0
-------------------------------------
select count(*) from test1 where object_id=:x

Plan hash value: 746261920

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 86 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST1_ID | 85968 | 335K| 86 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

1 - SEL$1
2 - SEL$1 / TEST1@SEL$1

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OBJECT_ID"))
END_OUTLINE_DATA
*/

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

1 - :X (NUMBER): 1

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

2 - filter("OBJECT_ID"=:X)

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

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

49 rows selected.

可以看到方法一，方法二获取的执行计划与方法三不一致，说明在使用绑定变量的情况下，最好使用方法三来获取准确的执行计划。

ITPub博客

Oracle查看执行计划（六）

最新文章