【dbms_xplan包】對比試驗之ALL與ADVANCED +PEEKED_BINDS區別
結論：1、ADVANCED只比ALL多了一個Outline Data
結論：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效果一致。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28853590/viewspace-2151171/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【dbms_xplan.display_cursor包】預設與ADVANCED ALLSTATS LAST PEEKED_BINDS區別AST
- Spark與MapReduce的對比(區別)Spark
- Union與Union All的區別
- 【SQL】UNION ALL 與UNION 的區別SQL
- jQuery中empty與html("")的區別對比jQueryHTML
- dbms_xplan 函式包函式
- 子查詢中all與any的區別
- Vue與React兩個框架的粗略區別對比VueReact框架
- backup archivelog delete input 與delete all input 區別Hivedelete
- 工業交換機與商業交換機區別對比
- OPPO A59與樂視樂2區別對比評測
- 紅米note4與小米4區別對比評測
- 紅米3S與紅米3區別對比評測
- alter system switch log file 與 archive log current/all 區別Hive
- OC與Swift閉包對比總結Swift
- 魅藍metal2與小米4區別對比評測
- 紅米Pro與小米5引數配置區別對比評測
- 華為mate8與魅藍E區別對比評測
- 小米note與OPPO R9 Plus區別對比評測
- vivo X7與OPPO A59區別對比評測
- 樂視手機樂視2與Max 2區別對比評測
- union all和union的區別
- union和union all的區別
- union 和union all 使用區別
- jar包和war包的介紹與區別JAR
- jar包、war包和ear包的介紹與區別JAR
- CTS測試與GTS測試區別
- iPhone 7和7 Plus有什麼區別？ 蘋果7與7 Plus區別對比評測iPhone蘋果
- RMAN - backup archivelog all 與 backup database plus archivelog 區別HiveDatabase
- 耦合與聚合的區別比單體與微服務區別更重要微服務
- UDP分片和丟包與TCP效果對比UDPTCP
- Linux與windows對比有什麼優勢？詳細區別LinuxWindows
- MyISAM與InnoDB兩者的區別、詳細總結、效能對比
- nubia Z11 mini與Z11區別對比評測
- 小米5s與紅米note3區別對比評測
- 小米5s與紅米note4區別對比評測
- 小米5s與小米4c區別對比評測
- oppo r11與f1s區別對比評測