dbms_xplan.display_cursor包与高级所有统计上次查看绑定区别是什么，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
结论1:使用全部最后比典型的多了查询块名称/对象
别名和列投影信息（列的信息)
结论2:高级所有统计最后查看绑定比全部最后多了这些内容：大纲和注：当然如果使用了绑定变量的话，还有绑定变量信息
结论3:一般来说全部最后就已经够用了。
使用一个不使用绑定变量的语句来做对比试验：
从斯科特。电磁脉冲。斯科特德部门中选择/*weiwei*/e.ename，d.dname，其中
e。部门号=d部门号；
SQL SELECT *
从表(DBMS_XPLAN .DISPLAY _ CURSOR)；
计划_表格_输出
-
SQL_ID1qwpbwszr5hwb，
孩子编号0
-
从中选择/*weiwei*/e.ename，d.dname
斯科特。电磁脉冲e，斯科特。部门d在哪里
e.deptno=d.deptno
计划哈希值： 844388907
-
| Id
|
操作|名称
|行|字节|成本(%中央处理器)|
时间|
-
|
0 | SELECT
声明
| |
| | 6
(100)| |
|
1 | MERGE
JOIN | |
14 | 308 |
6(17)| 00:00:01 |
|nbs
p;
2 | TABLE ACCESS BY INDEX ROWID|
DEPT | 4 |
52 |
2 (0)| 00:00:01 |
|
3 | INDEX FULL
SCAN | PK_DEPT | 4 |
|
1 (0)| 00:00:01 |
|*
4 | SORT
JOIN | |
14 | 126 |
4 (25)| 00:00:01 |
|
5 | TABLE ACCESS
FULL | EMP
| 14 | 126 |
3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
4 -
access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
24 rows selected.
select
sql_id,CHILD_NUMBER,sql_text
from v$SQL where sql_text like '%weiwei%' and sql_text not like '%like%';
获得SQL_id为1qwpbwszr5hwb,CHILD_NUMBER为0
select * from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',null,'ALL LAST'));
SQL> select *
from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',null,'ALL LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1qwpbwszr5hwb,
child number 0
-------------------------------------
select /*weiwei*/
e.ename,d.dname from scott.emp e,scott.dept d where
e.deptno=d.deptno
Plan hash value:
844388907
----------------------------------------------------------------------------------------
| Id |
Operation | Name
| Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
| |
| | 6
(100)| |
| 1 |
MERGE
JOIN | |
14 | 308 |
6 (17)| 00:00:01 |
| 2 |
TABLE ACCESS BY INDEX ROWID| DEPT
| 4 | 52 |
2 (0)| 00:00:01 |
| 3 |
INDEX FULL SCAN | PK_DEPT | 4 |
|
1 (0)| 00:00:01 |
|* 4 |
SORT
JOIN | |
14 | 126 |
4 (25)| 00:00:01 |
| 5 |
TABLE ACCESS FULL | EMP
| 14 | 126 |
3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query
Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Column
Projection Information (identified by operation id):
-----------------------------------------------------------
1 -
"D"."DNAME"[VARCHAR2,14],
"E"."ENAME"[VARCHAR2,10]
2 -
"D"."DEPTNO"[NUMBER,22],
"D"."DNAME"[VARCHAR2,14]
3 - "D".ROWID[ROWID,10],
"D"."DEPTNO"[NUMBER,22]
4 - (#keys=1)
"E"."DEPTNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10]
5 -
"E"."ENAME"[VARCHAR2,10],
"E"."DEPTNO"[NUMBER,22]
41 rows selected.
结论1：使用ALL LAST比typical多了Query Block Name / Object
Alias和Column Projection Information（列的信息）
再对比ALL LAST与ADVANCED ALLSTATS LAST PEEKED_BINDS
最后最全的是65行
select
* from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',0,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
SQL> select *
from table(dbms_xplan.display_cursor('1qwpbwszr5hwb',0,'ADVANCED ALLSTATS LAST
PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1qwpbwszr5hwb,
child number 0
-------------------------------------
select /*weiwei*/
e.ename,d.dname from scott.emp e,scott.dept d where
e.deptno=d.deptno
Plan hash value:
844388907
--------------------------------------------------------------------------------------------------------------------
| Id |
Operation | Name
| E-Rows |E-Bytes| Cost (%CPU)|
E-Time | OMem |
1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
| | | | 6
(100)| | | | |
| 1 |
MERGE
JOIN | |
14 | 308 | 6 (17)|
00:00:01
| | | |
| 2 |
TABLE ACCESS BY INDEX ROWID| DEPT
| 4 | 52 |
2 (0)| 00:00:01
| | | |
| 3 |
INDEX FULL SCAN | PK_DEPT | 4 | | 1
(0)| 00:00:01
| | | |
|* 4 |
SORT
JOIN | |
14 | 126 | 4 (25)|
00:00:01 | 2048 | 2048 |
2048 (0)|
| 5 |
TABLE ACCESS FULL | EMP
| 14 | 126 |
3 (0)| 00:00:01
| | | |
--------------------------------------------------------------------------------------------------------------------
Query Block Name /
Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / D@SEL$1
3 - SEL$1 / D@SEL$1
5 - SEL$1 / E@SEL$1
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')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1"
"D"@"SEL$1" ("DEPT"."DEPTNO"))
FULL(@"SEL$1"
"E"@"SEL$1")
LEADING(@"SEL$1"
"D"@"SEL$1" "E"@"SEL$1")
USE_MERGE(@"SEL$1"
"E"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate
Information (identified by operation id):
---------------------------------------------------
4 -
access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Column Projection
Information (identified by operation id):
-----------------------------------------------------------
1 -
"D"."DNAME"[VARCHAR2,14],
"E"."ENAME"[VARCHAR2,10]
2 -
"D"."DEPTNO"[NUMBER,22],
"D"."DNAME"[VARCHAR2,14]
3 - "D".ROWID[ROWID,10],
"D"."DEPTNO"[NUMBER,22]
4 - (#keys=1)
"E"."DEPTNO"[NUMBER,22],
"E"."ENAME"[VARCHAR2,10]
5 -
"E"."ENAME"[VARCHAR2,10],
"E"."DEPTNO"[NUMBER,22]
Note
-----
- Warning: basic plan statistics not
available. These are only collected when:
* hint 'gather_plan_statistics' is used
for the statement or
* parameter 'statistics_level' is set to
'ALL', at session or system level
rows selected.
结论2：ADVANCED ALLSTATS LAST PEEKED_BINDS比ALL LAST多了这些内容：outline和NOTE，当然如果使用了绑定变量的话，还有绑定变量信息
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/157757.html