dbms_xplan.display_cursor包与ADVANCED ALLSTATS LAST PEEKED_BINDS区别是什么，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

结论1：使用ALL LAST比typical多了Query Block Name / ObjectAlias和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 wheree.deptno=d.deptno;

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

PLAN_TABLE_OUTPUT

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

SQL_ID1qwpbwszr5hwb,child number 0

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

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

e.deptno=d.deptno

Plan hash value: 844388907

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

| Id|Operation | Name| Rows | Bytes | Cost (%CPU)|Time |

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

|0 | SELECTSTATEMENT| || | 6(100)| |

|1 | MERGEJOIN | |14 | 308 |6(17)| 00:00:01 |

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

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

|*4 | SORTJOIN | |14 | 126 |4(25)| 00:00:01 |

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

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

Predicate Information (identified byoperation id):

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

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

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

24 rows selected.

selectsql_id,CHILD_NUMBER,sql_textfrom 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_ID1qwpbwszr5hwb,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

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

| Id |Operation | Name| Rows | Bytes | Cost (%CPU)|Time |

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

| 0 | SELECTSTATEMENT| || | 6(100)| |

| 1 |MERGEJOIN | |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 |SORTJOIN | |14 | 126 |4(25)| 00:00:01 |

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

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

QueryBlock 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

PredicateInformation (identified by operation id):

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

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

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

ColumnProjection 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 / ObjectAlias和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 LASTPEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

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

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

| Id |Operation | Name| E-Rows |E-Bytes| Cost (%CPU)|E-Time| OMem |1Mem | Used-Mem |

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

| 0 | SELECTSTATEMENT| ||| 6(100)|||| |

| 1 |MERGEJOIN | |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 |SORTJOIN | |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

OutlineData

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

/*+

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

*/

PredicateInformation (identified by operation id):

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

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

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

Column ProjectionInformation (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 notavailable. These are only collected when:

* hint 'gather_plan_statistics' is usedfor the statement or

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

rows selected.

结论2：ADVANCED ALLSTATS LAST PEEKED_BINDS比ALL LAST多了这些内容：outline和NOTE，当然如果使用了绑定变量的话，还有绑定变量信息

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。