【dbms_xplan包】对比试验之ALL与ADVANCED +PEEKED_BINDS区别

2025-04-13 技术教程

结论：1、ADVANCED只比ALL多了一个OutlineData

结论：2、ADVANCED +PEEKED_BINDS比ADVANCED多显示了一个Peeked Binds (identified by position):，如果SQL语句并没有使用绑定变量，则与ADVANCED效果一致。

结论：3、ADVANCED +PEEKED_BINDS确实是最全的显示执行计划的方法，但是比较难记，官方文档上也没有，大多数情况用ALL就已经足够了

首先，对比ALL与ADVANCED

ALL：

SQL> SELECT * FROMtable(DBMS_XPLAN.DISPLAY_AWR('0bkjwu3swz0wy',null,2082231315,'ALL'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------

SQL_ID 0bkjwu3swz0wy

--------------------

SELECT value,type FROM v$parameter WHEREname = :1

Plan hash value: 1023639799

----------------------------------------

| Id| Operation | Name|

----------------------------------------

|0 | SELECT STATEMENT| |

|1 | MERGEJOIN| |

|2 | FIXED TABLE FULL | X$KSPPCV |

|3 |FILTER| |

|4 | SORTJOIN| |

|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,typeFROM 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

OutlineData

-------------

/*+

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 usingcbo)

51 rows selected.

结论：1、ADVANCED只比ALL多了一个OutlineData

然后，对比ADVANCED与ADVANCED +PEEKED_BINDS，并没有加东西，因为没有使用绑定变量

ADVANCED +PEEKED_BINDS：

SELECT * FROMtable(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,typeFROM 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 usingcbo)

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.msgidfrom

"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

------------------------------------------------------------------------------

-------------------------

| Cost (%CPU)|Time|

------------------------------------------------------------------------------

-------------------------

| 0 | SELECTSTATEMENT| ||

| 5(100)||

| 1 |NESTEDLOOPS | ||

|||

| 2 |NESTEDLOOPS | |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 |HASHJOIN | |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

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

声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。

【dbms_xplan包】对比试验之ALL与ADVANCED +PEEKED_BINDS区别

热门

最新文章

随机文章

TAGS标签

【dbms_xplan包】对比试验之ALL与ADVANCED +PEEKED_BINDS区别

相关推荐

相关推荐

热门

最新文章

随机文章

TAGS标签