【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
------------------------------------------------------------------------------
-------------------------
| 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区别
网站网址：http://azwzsj.com/article/gccood.html
