Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.