【dbms_xplan包】对比试验之ALL与ADVANCED+PEEKED_BINDS区别

结论：1、ADVANCED只比ALL多了一个Outline Data

成都创新互联公司成立与2013年，是专业互联网技术服务公司，拥有项目成都网站设计、成都做网站网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元泰州做网站,已为上家服务,为泰州各地企业和个人服务,联系电话:028-86922220

结论：2、ADVANCED +PEEKED_BINDS比ADVANCED多显示了一个Peeked Binds (identified by position):，如果SQL语句并没有使用绑定变量，则与ADVANCED效果一致。

结论：3、ADVANCED +PEEKED_BINDS确实是最全的显示执行计划的方法，但是比较难记，官方文档上也没有，大多数情况用ALL就已经足够了

首先，对比ALL与ADVANCED

ALL：

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('0bkjwu3swz0wy',null,2082231315,'ALL'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0bkjwu3swz0wy

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

SELECT value,type FROM v$parameter WHERE name = :1

Plan hash value: 1023639799

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | MERGE JOIN | |

| 2 | FIXED TABLE FULL | X$KSPPCV |

| 3 | FILTER | |

| 4 | SORT JOIN | |

| 5 | FIXED TABLE FULL| X$KSPPI |

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

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

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

1 - SEL$5C160134

2 - SEL$5C160134 / Y@SEL$3

5 - SEL$5C160134 / X@SEL$3

Note

-----

- rule based optimizer used (consider using cbo)

28 rows selected.

ADVANCED：

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('0bkjwu3swz0wy',null,2082231315,'ADVANCED'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0bkjwu3swz0wy

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

SELECT value,type FROM v$parameter WHERE name = :1

Plan hash value: 1023639799

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | MERGE JOIN | |

| 2 | FIXED TABLE FULL | X$KSPPCV |

| 3 | FILTER | |

| 4 | SORT JOIN | |

| 5 | FIXED TABLE FULL| X$KSPPI |

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

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

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

1 - SEL$5C160134

2 - SEL$5C160134 / Y@SEL$3

5 - SEL$5C160134 / X@SEL$3

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

RBO_OUTLINE

OUTLINE_LEAF(@"SEL$5C160134")

MERGE(@"SEL$335DD26A")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$335DD26A")

MERGE(@"SEL$3")

OUTLINE(@"SEL$2")

OUTLINE(@"SEL$3")

FULL(@"SEL$5C160134" "Y"@"SEL$3")

FULL(@"SEL$5C160134" "X"@"SEL$3")

LEADING(@"SEL$5C160134" "Y"@"SEL$3" "X"@"SEL$3")

USE_MERGE(@"SEL$5C160134" "X"@"SEL$3")

END_OUTLINE_DATA

Note

-----

- rule based optimizer used (consider using cbo)

51 rows selected.

结论：1、ADVANCED只比ALL多了一个Outline Data

然后，对比ADVANCED与ADVANCED +PEEKED_BINDS，并没有加东西，因为没有使用绑定变量

ADVANCED +PEEKED_BINDS：

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('0bkjwu3swz0wy',null,2082231315,'ADVANCED +PEEKED_BINDS'));

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('0bkjwu3swz0wy',null,2082231315,'ADVANCED +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0bkjwu3swz0wy

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

SELECT value,type FROM v$parameter WHERE name = :1

Plan hash value: 1023639799

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

| Id | Operation | Name |

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

| 0 | SELECT STATEMENT | |

| 1 | MERGE JOIN | |

| 2 | FIXED TABLE FULL | X$KSPPCV |

| 3 | FILTER | |

| 4 | SORT JOIN | |

| 5 | FIXED TABLE FULL| X$KSPPI |

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

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

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

1 - SEL$5C160134

2 - SEL$5C160134 / Y@SEL$3

5 - SEL$5C160134 / X@SEL$3

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

RBO_OUTLINE

OUTLINE_LEAF(@"SEL$5C160134")

MERGE(@"SEL$335DD26A")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$335DD26A")

MERGE(@"SEL$3")

OUTLINE(@"SEL$2")

OUTLINE(@"SEL$3")

FULL(@"SEL$5C160134" "Y"@"SEL$3")

FULL(@"SEL$5C160134" "X"@"SEL$3")

LEADING(@"SEL$5C160134" "Y"@"SEL$3" "X"@"SEL$3")

USE_MERGE(@"SEL$5C160134" "X"@"SEL$3")

END_OUTLINE_DATA

Note

-----

- rule based optimizer used (consider using cbo)

51 rows selected.

换一个试试：

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('0xqn4sx1ytghr',null,2082231315,'ADVANCED +PEEKED_BINDS'));

这次由于使用了绑定变量，所以比ADVANCED多显示了一个Peeked Binds (identified by position):

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('0xqn4sx1ytghr',null,2082231315,'ADVANCED +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0xqn4sx1ytghr

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

select /*+ first_rows(1) no_expand */ tab.msgid from

"SYSMAN"."AQ$_MGMT_TASK_QTABLE_F" tab where q_name = :1 and (state =

:2 ) and queue_id = :3 and ( tab.user_data.scheduled_time <=

CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) AND

(tab.user_data.message_code = 0 OR

tab.user_data.message_code = 1))

Plan hash value: 2797331186

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

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

| Cost (%CPU)| Time |

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

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

| 0 | SELECT STATEMENT | | |

| 5 (100)| |

| 1 | NESTED LOOPS | | |

| | |

| 2 | NESTED LOOPS | | 1 | 111

| 5 (0)| 00:00:01 |

| 3 | VIEW | ALL_INT_DEQUEUE_QUEUES | 1 | 21

| 3 (0)| 00:00:01 |

| 4 | FILTER | | |

| | |

| 5 | NESTED LOOPS | | 1 | 56

| 3 (0)| 00:00:01 |

| 6 | NESTED LOOPS | | 1 | 48

| 2 (0)| 00:00:01 |

| 7 | INDEX RANGE SCAN | I1_QUEUES | 1 | 31

| 1 (0)| 00:00:01 |

| 8 | INDEX RANGE SCAN | I1_QUEUE_TABLES | 1 | 17

| 1 (0)| 00:00:01 |

| 9 | INDEX RANGE SCAN | I_OBJ1 | 1 | 8

| 1 (0)| 00:00:01 |

| 10 | HASH JOIN | | 1 | 24

| 3 (34)| 00:00:01 |

| 11 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 11

| 2 (0)| 00:00:01 |

| 12 | FIXED TABLE FULL | X$KZSRO | 100 | 1300

| 0 (0)| |

| 13 | FIXED TABLE FULL | X$KZSPR | 1 | 26

| 0 (0)| |

| 14 | NESTED LOOPS | | 1 | 45

| 5 (0)| 00:00:01 |

| 15 | INLIST ITERATOR | | |

| | |

| 16 | INDEX RANGE SCAN | I_OBJ2 | 1 | 37

| 4 (0)| 00:00:01 |

| 17 | INDEX RANGE SCAN | I_OBJAUTH2 | 1 | 8

| 1 (0)| 00:00:01 |

| 18 | INDEX RANGE SCAN | MGMT_TASK_QTABLE_IDX01 | 1 |

| 1 (0)| 00:00:01 |

| 19 | TABLE ACCESS BY INDEX ROWID| MGMT_TASK_QTABLE | 1 | 90

| 2 (0)| 00:00:01 |

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

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

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

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

1 - SEL$F5BB74E1

3 - SEL$3 / QO@SEL$2

4 - SEL$3

7 - SEL$3 / Q@SEL$3

8 - SEL$3 / T@SEL$3

9 - SEL$3 / RO@SEL$3

10 - SEL$385088EC

11 - SEL$385088EC / OA@SEL$4

12 - SEL$385088EC / X$KZSRO@SEL$5

13 - SEL$A731BD80 / X$KZSPR@SEL$8

14 - SEL$9

16 - SEL$9 / O@SEL$9

17 - SEL$9 / OA@SEL$9

18 - SEL$F5BB74E1 / QT@SEL$2

19 - SEL$F5BB74E1 / QT@SEL$2

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')

FIRST_ROWS(1)

FORCE_XML_QUERY_REWRITE

XML_DML_RWT_STMT

XMLINDEX_REWRITE

XMLINDEX_REWRITE_IN_SELECT

NO_COST_XML_QUERY_REWRITE

OUTLINE_LEAF(@"SEL$385088EC")

UNNEST(@"SEL$5")

OUTLINE_LEAF(@"SEL$A731BD80")

MERGE(@"SEL$8A3193DA")

OUTLINE_LEAF(@"SEL$9")

OUTLINE_LEAF(@"SEL$3")

OUTLINE_LEAF(@"SEL$F5BB74E1")

MERGE(@"SEL$2")

OUTLINE(@"SEL$4")

OUTLINE(@"SEL$5")

OUTLINE(@"SEL$6")

OUTLINE(@"SEL$8A3193DA")

MERGE(@"SEL$8")

OUTLINE(@"SEL$1")

OUTLINE(@"SEL$2")

OUTLINE(@"SEL$7")

OUTLINE(@"SEL$8")

NO_ACCESS(@"SEL$F5BB74E1" "QO"@"SEL$2")

INDEX(@"SEL$F5BB74E1" "QT"@"SEL$2" "MGMT_TASK_QTABLE_IDX01")

LEADING(@"SEL$F5BB74E1" "QO"@"SEL$2" "QT"@"SEL$2")

USE_NL(@"SEL$F5BB74E1" "QT"@"SEL$2")

NLJ_BATCHING(@"SEL$F5BB74E1" "QT"@"SEL$2")

INDEX(@"SEL$3" "Q"@"SEL$3" ("AQ$_QUEUES"."NAME" "AQ$_QUEUES"."EVENTID"

"AQ$_QUEUES"."TABLE_OBJNO"))

INDEX(@"SEL$3" "T"@"SEL$3" ("AQ$_QUEUE_TABLES"."OBJNO" "AQ$_QUEUE_TABLES

"."SCHEMA"

"AQ$_QUEUE_TABLES"."FLAGS"))

INDEX(@"SEL$3" "RO"@"SEL$3" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#

"))

LEADING(@"SEL$3" "Q"@"SEL$3" "T"@"SEL$3" "RO"@"SEL$3")

USE_NL(@"SEL$3" "T"@"SEL$3")

USE_NL(@"SEL$3" "RO"@"SEL$3")

INDEX(@"SEL$9" "O"@"SEL$9" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESP

ACE"

"OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME" "OBJ$"."

TYPE#" "OBJ$"."SPARE3"

"OBJ$"."OBJ#"))

NUM_INDEX_KEYS(@"SEL$9" "O"@"SEL$9" "I_OBJ2" 2)

INDEX(@"SEL$9" "OA"@"SEL$9" ("OBJAUTH$"."GRANTEE#" "OBJAUTH$"."OBJ#" "OB

JAUTH$"."COL#"))

LEADING(@"SEL$9" "O"@"SEL$9" "OA"@"SEL$9")

USE_NL(@"SEL$9" "OA"@"SEL$9")

FULL(@"SEL$A731BD80" "X$KZSPR"@"SEL$8")

INDEX(@"SEL$385088EC" "OA"@"SEL$4" ("OBJAUTH$"."OBJ#" "OBJAUTH$"."GRANTO

R#"

"OBJAUTH$"."GRANTEE#" "OBJAUTH$"."PRIVILEGE#" "OBJAUTH$"."COL#")

)

FULL(@"SEL$385088EC" "X$KZSRO"@"SEL$5")

LEADING(@"SEL$385088EC" "OA"@"SEL$4" "X$KZSRO"@"SEL$5")

USE_HASH(@"SEL$385088EC" "X$KZSRO"@"SEL$5")

END_OUTLINE_DATA

Peeked Binds (identified by position):

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

1 - :1 (VARCHAR2(30), CSID=873): 'MGMT_TASK_Q'

2 - :2 (NUMBER): 0

3 - :3 (NUMBER): 80768

Note

-----

- dynamic sampling used for this statement (level=2)

127 rows selected.

结论：2、ADVANCED +PEEKED_BINDS比ADVANCED多显示了一个Peeked Binds (identified by position):，如果SQL语句并没有使用绑定变量，则与ADVANCED效果一致。

当前题目：【dbms_xplan包】对比试验之ALL与ADVANCED+PEEKED_BINDS区别
分享网址：http://chengdu.cdxwcx.cn/article/gccood.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

【dbms_xplan包】对比试验之ALL与ADVANCED+PEEKED_BINDS区别

其他资讯

linux命令chgrp Linux命令chgrp

linux命令大全最新 linux 命令大全

php网页载入数据库不全 php网页文件中数据库在哪

怎么通过域名查ip 怎么通过域名查手机号

怎么用阿里云过户域名阿里云域名过户怎么弄

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

【dbms_xplan包】对比试验之ALL与ADVANCED+PEEKED_BINDS区别

其他资讯

linux命令chgrp Linux命令chgrp

linux命令大全最新 linux 命令大全

php网页载入数据库不全 php网页文件中数据库在哪

怎么通过域名查ip 怎么通过域名查手机号

怎么用阿里云过户域名 阿里云域名过户怎么弄

成都网站建设设计将想法与焦点和您一起共享

怎么用阿里云过户域名阿里云域名过户怎么弄