结论：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
------------------------------------------------------------------------------
-------------------------
| Id |
Operation |
Name | Rows
| Bytes
| 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区别
URL链接：
http://cdkjz.cn/article/gccood.html
