共計 6431 個字符，預計需要花費 17 分鐘才能閱讀完成。
行業資訊
數據庫
關系型數據庫
dbms_xplan.display_cursor 包與 ADVANCED ALLSTATS LAST PEEKED_BINDS 區別是什么
dbms_xplan.display_cursor 包與 ADVANCED ALLSTATS LAST PEEKED_BINDS 區別是什么，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
結論 1：使用 ALL LAST 比 typical 多了 Query Block Name / Object
Alias 和 Column Projection Information（列的信息）
結論 2：ADVANCED ALLSTATS LAST PEEKED_BINDS 比 ALL LAST 多了這些內容：outline 和 NOTE，當然如果使用了綁定變量的話，還有綁定變量信息
結論 3：一般來說 ALL LAST 就已經夠用了。
使用一個不使用綁定變量的語句來做對比試驗：
select /*weiwei*/ e.ename,d.dname from scott.emp e,scott.dept d where
e.deptno=d.deptno;
SQL SELECT *
FROM table (DBMS_XPLAN.DISPLAY_CURSOR);
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 |
—————————————————————————————-
Predicate Information (identified by
operation id):
—————————————————
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 |
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 |
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，當然如果使用了綁定變量的話，還有綁定變量信息
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。
