【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效果一致。