結論1：使用ALL LAST比typical多了Query Block Name / Object Alias和Column Projection Information（列的資訊）

結論2：ADVANCED ALLSTATS LAST PEEKED_BINDS比ALL LAST多了這些內容：outline和NOTE，當然如果使用了繫結變數的話，還有繫結變數資訊

結論3：一般來說ALL LAST就已經夠用了。

使用一個不使用繫結變數的語句來做對比試驗：

select /*weiwei*/ e.ename,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT

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

SQL_ID 1qwpbwszr5hwb, child number 0

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

select /*weiwei*/ e.ename,d.dname from scott.emp e,scott.dept d where

e.deptno=d.deptno

Plan hash value: 844388907

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)| |

| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

filter("E"."DEPTNO"="D"."DEPTNO")

24 rows selected.

select sql_id,CHILD_NUMBER,sql_text from v$SQL where sql_text like '%weiwei%' and sql_text not like '%like%';

獲得SQL_id為1qwpbwszr5hwb,CHILD_NUMBER為0

select * from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',null,'ALL LAST'));

SQL> select * from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',null,'ALL LAST'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1qwpbwszr5hwb, child number 0

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

select /*weiwei*/ e.ename,d.dname from scott.emp e,scott.dept d where

e.deptno=d.deptno

Plan hash value: 844388907

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)| |

| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |

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

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

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

1 - SEL$1

2 - SEL$1 / D@SEL$1

3 - SEL$1 / D@SEL$1

5 - SEL$1 / E@SEL$1

Predicate Information (identified by operation id):

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

filter("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):

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

1 - "D"."DNAME"[VARCHAR2,14], "E"."ENAME"[VARCHAR2,10]

2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]

3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]

4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10]

5 - "E"."ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22]

41 rows selected.

結論1：使用ALL LAST比typical多了Query Block Name / Object Alias和Column Projection Information（列的資訊）

再對比ALL LAST與ADVANCED ALLSTATS LAST PEEKED_BINDS

最後最全的是65行

select * from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

SQL> select * from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID 1qwpbwszr5hwb, child number 0

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

select /*weiwei*/ e.ename,d.dname from scott.emp e,scott.dept d where

e.deptno=d.deptno

Plan hash value: 844388907

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

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

| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |

| 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 | | | |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |

|* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|

| 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 | | | |

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

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

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

1 - SEL$1

2 - SEL$1 / D@SEL$1

3 - SEL$1 / D@SEL$1

5 - SEL$1 / E@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

OPT_PARAM('query_rewrite_enabled' 'false')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))

FULL(@"SEL$1" "E"@"SEL$1")

LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")

USE_MERGE(@"SEL$1" "E"@"SEL$1")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

filter("E"."DEPTNO"="D"."DEPTNO")

Column Projection Information (identified by operation id):

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

1 - "D"."DNAME"[VARCHAR2,14], "E"."ENAME"[VARCHAR2,10]

2 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14]

3 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]

4 - (#keys=1) "E"."DEPTNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10]

5 - "E"."ENAME"[VARCHAR2,10], "E"."DEPTNO"[NUMBER,22]

Note

-----

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

65 rows selected.

結論2：ADVANCED ALLSTATS LAST PEEKED_BINDS比ALL LAST多了這些內容：outline和NOTE，當然如果使用了繫結變數的話，還有繫結變數資訊

【dbms_xplan.display_cursor包】預設與ADVANCED ALLSTATS LAST PEEKED_BINDS區別

相關文章