[dbms_xplan package] the difference between ALL and ADVANCED + PEEKED_BINDS in comparative tests
Conclusion: 1. ADVANCED has only one more Outline Data than ALL.
Conclusion: 2. ADVANCED + PEEKED_BINDS shows one more Peeked Binds (identified by position) than ADVANCED: if the SQL statement does not use binding variables, it is consistent with the ADVANCED effect.
Conclusion: 3. ADVANCED + PEEKED_BINDS is indeed the most complete method to show the execution plan, but it is difficult to remember, and it is not found in the official documents. In most cases, using ALL is enough.
First of all, compare ALL with ADVANCED
ALL:
SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('0bkjwu3swz0wywriting recorder nullmeme2082231315heroine 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 ('0bkjwu3swz0wywriting recordnullReclamation 2082231315))
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.
Conclusion: 1. ADVANCED has only one more Outline Data than ALL.
Then, comparing ADVANCED with ADVANCED + PEEKED_BINDS, there is nothing added because binding variables are not used
ADVANCED + PEEKED_BINDS:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('0bkjwu3swz0wyjewelry)
SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('0bkjwu3swz0wywriting recordnullReclamation 2082231315danded + 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.
Try another one:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('0xqn4sx1ytghr)
This time, due to the use of binding variables, there is one more Peeked Binds (identified by position) than ADVANCED:
SQL > SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR ('0xqn4sx1ytghr)
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
