[20190720]12cR2顯示執行計劃.txt

[20190720]12cR2顯示執行計劃.txt

--//12c R2 可以顯示執行計劃，不要使用table。
--//以前看別人的貼子呼叫dbms_xplan.display_cursor發現執行報錯。原來要12cR2才可以這樣。12CR1不行。

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

$ cat dpcn.sql
set verify off
--select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition'));

select * from 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

2.測試：

SCOTT@test01p> alter session set statistics_level = all;
Session altered.

SCOTT@test01p> select SYSDATE from dual;
SYSDATE
-------------------
2019-07-20 18:55:17

SCOTT@test01p> @dpcn '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5q9sgumphjt9z, child number 1
-------------------------------------
select SYSDATE from dual
Plan hash value: 1388734953
-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |      1 |        |     2 (100)|          |      1 |00:00:00.01 |
|   1 |  FAST DUAL       |      |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DUAL@SEL$1