dbms_xplan.display_cursor包与ADVANCED ALLSTATS LAST PEEKED_BINDS区别是什么

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

结论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

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

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

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

|   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

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

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

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

|   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

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

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

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

|   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

 rows selected.

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

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

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:niceseo99@gmail.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

评论

有免费节点资源,我们会通知你!加入纸飞机订阅群

×
天气预报查看日历分享网页手机扫码留言评论电报频道链接