导航：首页 > 数据库 >

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

发表于：2025-02-06 作者：千家信息网编辑

千家信息网最后更新 2025年02月06日，结论：1、ADVANCED只比ALL多了一个Outline Data结论：2、ADVANCED +PEEKED_BINDS比ADVANCED多显示了一个Peeked Binds (identified

千家信息网最后更新 2025年02月06日【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

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

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

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

很赞哦！

结论变量一致效果语句最全东西官方情况文档方法试验数据库的安全要保护哪些东西数据库安全各自的含义是什么生产安全数据库录入数据库的安全性及管理数据库安全策略包含哪些海淀数据库安全审计系统建立农村房屋安全信息数据库易用的数据库客户端支持安全管理连接数据库失败ssl安全错误数据库的锁怎样保障安全清除此单元格以下的数据库寒假网络安全教育版本 wind高频数据库怎么登录局域网服务器软件开发与前端开发绝境求生美国服务器 ftp服务器管理软件网络技术证书那些比较有用服务器监控系统服务器域名怎么查网络安全渗透培训哪家好 vs中有数据库文件甲骨文范例数据库 ovid数据库的检索技术服务器配置推荐文件互联网公司服务器维修网络安全法哪年上海闪态网络技术有限公司南京软件开发公司 cf怎么下载最新版服务器怎么登录局域网服务器网络技术初学者教程南通软件开发工资标准疫情所用的数据库技术登录qq邮箱服务器共筑防火墙网络安全成都软件开发怎么样在数据库阿伯中不能为控制读者与作者的关系数据库我的世界基岩版服务器开启作弊

千家信息网

千家信息网

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

oracle中delete drop truncate的用法和区别

mysql insert into ... select的死锁问题是什么

相关文章