dbms_xplan.display_cursor包與ADVANCED ALLSTATS LAST PEEKED_BINDS區(qū)別是什么，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。
成都創(chuàng)新互聯(lián)專注于企業(yè)營(yíng)銷型網(wǎng)站、網(wǎng)站重做改版、利通網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、HTML5、商城網(wǎng)站制作、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站制作、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為利通等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
結(jié)論1：使用ALL LAST比typical多了Query Block Name / Object Alias和Column Projection Information（列的信息）
結(jié)論2：ADVANCED ALLSTATS LAST PEEKED_BINDS比ALL LAST多了這些內(nèi)容：outline和NOTE，當(dāng)然如果使用了綁定變量的話，還有綁定變量信息
結(jié)論3：一般來(lái)說(shuō)ALL LAST就已經(jīng)夠用了。
使用一個(gè)不使用綁定變量的語(yǔ)句來(lái)做對(duì)比試驗(yàn)：
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):
---------------------------------------------------
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.
結(jié)論1：使用ALL LAST比typical多了Query Block Name / Object Alias和Column Projection Information（列的信息）
再對(duì)比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.
結(jié)論2：ADVANCED ALLSTATS LAST PEEKED_BINDS比ALL LAST多了這些內(nèi)容：outline和NOTE，當(dāng)然如果使用了綁定變量的話，還有綁定變量信息
看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝您對(duì)創(chuàng)新互聯(lián)的支持。
重慶分公司
重慶分公司
