[20150810]10g下dbms_xplan.display_cursor.txt
--执行过的sql语句要查看执行计划要使用dbms_xplan包，我一般写成脚本反复使用，这样快捷方便一些。
--我的定义如下：
$ cat dpc.sql
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt
--但是在10g下遇到一点小问题，今天花一点实际解决它：
SCOTT@test> select * from dept where deptno=10;
DEPTNO DNAME LOC
------------ -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4xamnunv51w9j, child number 0
-------------------------------------
select * from dept where deptno=10
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEPT@SEL$1
2 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
--如果仔细看缺少1行，id=0, SELECT STATEMENT.
SCOTT@test> select * from table(dbms_xplan.display_cursor(NULL,NULL));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4xamnunv51w9j, child number 0
-------------------------------------
select * from dept where deptno=10
Plan hash value: 2852011669
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
--很明显是我后面的参数导致的问题，自己手工测试发现：
SCOTT@test> select * from table(dbms_xplan.display_cursor('4xamnunv51w9j',NULL,'ALLSTATS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4xamnunv51w9j, child number 0
-------------------------------------
select * from dept where deptno=10
Plan hash value: 2852011669
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=10)
--看来脚本要取消ALLSTATS参数。但是在11g，这样执行是正常的，估计是10g的一个小bug。
Table 8-1. Format levels and fine-grained control in calls to DBMS_XPLAN packages
Format levels
Fine-grained
Control Column/Section Basic Serial Typical All Advanced
==============================================================================
rows C X X X X
bytes C X X X X
cost C X X X X
partition C X X X X
predicate S X X X X
remote S X X X X
note S X X X X
parallel C X X X
projection S X X
alias S X X
peeked_binds S X
outline S X
adaptive See discussion of adaptive execution plans below
==============================================================================