本篇文章為大家展示了EXPLAIN PLAN FOR 和 SET AUTOTRACE之間的差別是什么,內(nèi)容簡(jiǎn)明扼要并且容易理解,絕對(duì)能使你眼前一亮,通過這篇文章的詳細(xì)介紹希望你能有所收獲。
我們提供的服務(wù)有:網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、上蔡ssl等。為上1000+企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù),是有科學(xué)管理、有技術(shù)的上蔡網(wǎng)站制作公司
通常我們使用 EXPLAIN PLAN FOR 和 SET AUTOTRACE 來(lái)查看sql語(yǔ)句的執(zhí)行計(jì)劃,這里做一個(gè)小實(shí)驗(yàn)來(lái)看看兩種方法對(duì)sql執(zhí)行情況的差別。
yang@rac1>create table yang_t as select * from t;
Table created.
yang@rac1>set autot on exp
yang@rac1>select * from yang_t;
ID NAME
---------- ------------------
130864 YANG_SEQ
132031 YANG_A
132032 SYS_C0066382
132033 YANG_B
132034 SYS_C0066383
132035 FACT
132036 MLOG$_YANG_A
132037 MLOG$_YANG_B
132038 MLOG$_FACT
132039 T
131949 YANG_ROWID
131951 YANG_PK
131952 SYS_C0066303
131955 YANG_OBJECT
131956 YANG_OID
131957 SYS_C0066304
132018 YANG_C
132017 MV_CAPABILITIES_TABLE
132030 MLOG$_YANG_PK
132027 MLOG$_YANG_ROWID
LINKORACL
LINKYANG
22 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2508602004
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1738 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| YANG_T | 22 | 1738 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
yang@rac1>set autot off
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t';
EXECUTIONS PARSE_CALLS
---------- -----------
1 1
yang@rac1>set autot on exp
yang@rac1>set autotrace traceonly
yang@rac1>select * from yang_t;
22 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2508602004
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1738 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| YANG_T | 22 | 1738 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
21 recursive calls
61 db block gets
33 consistent gets
6 physical reads
14040 redo size
1082 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
yang@rac1>set autot off
再次查詢是否執(zhí)行??梢钥闯鍪褂胹et autotrace 查看執(zhí)行計(jì)劃時(shí),oracle會(huì)執(zhí)行一下sql語(yǔ)句的。
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t';
EXECUTIONS PARSE_CALLS
---------- -----------
2 2
對(duì)測(cè)試表進(jìn)行dml操作。并查看dml 的執(zhí)行情況。
yang@rac1>insert into yang_t values (1,2);
1 row created.
yang@rac1>commit;
Commit complete.
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'insert into yang_t values (1,2);';
no rows selected
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'insert into yang_t values%';
EXECUTIONS PARSE_CALLS
---------- -----------
1 1
=======EXPLAIN PLAN FOR ========
實(shí)驗(yàn)一下EXPLAIN PLAN 查看sql語(yǔ)句執(zhí)行計(jì)劃的情況。
yang@rac1>EXPLAIN PLAN FOR SELECT * FROM YANG_T;
Explained.
yang@rac1>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2508602004
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1817 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| YANG_T | 23 | 1817 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
12 rows selected.
從下面的查詢結(jié)果中可以看出使用EXPLAIN PLAN FOR 查看執(zhí)行計(jì)劃時(shí)oracle是沒有執(zhí)行要查看執(zhí)行計(jì)劃的sql 語(yǔ)句的。
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG_T%';
no rows selected
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'SELECT * FROM YANG%';
no rows selected
yang@rac1>select executions,parse_calls from v$sqlarea where sql_text like 'select * from yang_t';
EXECUTIONS PARSE_CALLS
---------- -----------
2 2
yang@rac1>
小結(jié):
EXPLAIN PLAN FOR 方式查看執(zhí)行計(jì)劃時(shí)oracle本身并不真正的執(zhí)行該sql 語(yǔ)句,只是對(duì)sql進(jìn)行解析獲取執(zhí)行計(jì)劃。
SET AUTOTRACE 方式查看sql語(yǔ)句的執(zhí)行計(jì)劃則是oracle 則對(duì)sql進(jìn)行 解析并執(zhí)行的。
上述內(nèi)容就是EXPLAIN PLAN FOR 和 SET AUTOTRACE之間的差別是什么,你們學(xué)到知識(shí)或技能了嗎?如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備,歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
文章題目:EXPLAINPLANFOR和SETAUTOTRACE之間的差別是什么
標(biāo)題鏈接:http://muchs.cn/article46/ihcoeg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站建設(shè)、網(wǎng)站導(dǎo)航、域名注冊(cè)、網(wǎng)站維護(hù)、動(dòng)態(tài)網(wǎng)站、網(wǎng)站設(shè)計(jì)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)