oracle執(zhí)行計劃

2019-11-08 20:35:56

字體：大中小

供稿：網(wǎng)友

查看Oracle執(zhí)行計劃的三種方式：

1、利用PL/SQL Developer工具獲取

在SQL windows寫好sql，然后通過F5快捷鍵獲取執(zhí)行計劃，如果是已經(jīng)執(zhí)行過的sql，可以通過V$SQL_PLAN視圖查看已經(jīng)被執(zhí)行過的sql的執(zhí)行計劃

2、使用sql語句獲取執(zhí)行計劃

SQL> explain plan for select * from dual;已解釋。SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4017058736--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 || 1 | TABLE access FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------已選擇8行。SQL>

或者

SQL> explain plan for select * from dual;已解釋。SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4017058736--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------已選擇8行。SQL>

3、設(shè)置autotrace

SQL> set autotrace on;SQL> select * from dual;DU--X執(zhí)行計劃----------------------------------------------------------Plan hash value: 4017058736--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------統(tǒng)計信息---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 339 bytes sent via SQL*Net to client 358 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows PRocessedSQL>

autotrace相關(guān)設(shè)置說明

序號	命令	含義
1	SET AUTOTRACE OFF	默認值，即關(guān)閉autotrace，顯示sql的執(zhí)行結(jié)果
2	SET AUTOTRACE ON EXPLAIN	只顯示執(zhí)行計劃
3	SET AUTOTRACE ON STATISTICS	只顯示統(tǒng)計信息
4	SET AUTOTRACE ON	顯示sql執(zhí)行結(jié)果，執(zhí)行計劃，統(tǒng)計信息
5	SET AUTOTRACE TRACEONLY	顯示sql執(zhí)行計劃，統(tǒng)計信息，但不顯示執(zhí)行結(jié)果