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