投稿
  1. 云编程开发首页
  2. 数据库
  3. 关系数据库

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

云技术五哥 • • 关系数据库 • 阅读 116

结论：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”@”S免费云主机域名EL$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效果一致。

相关推荐: Oracle 11g RAC环境下的ORA – 19606问题

在刚刚接手Oracle时，这个问题困扰了我一段时间，现在将问题的解决过程分享一下Oracle版本：11gR2OS环境：Centos6.4问题重现：1. 接手数据库是写了一个备份脚本，脚本内容如下：——————————–…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
云技术五哥的头像云技术五哥
0 0
上一篇 12/31 17:13
下一篇 12/31 17:13