Oracle查看SQL執(zhí)行計(jì)劃1--explain plan

語法：
explain plan for + 目標(biāo)SQL
select * from table(dbms_xplan.display);

eg：
SQL> explain plan for select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;
Explained.

SQL> set linesize 800
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 844388907

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 364 | 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 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

18 rows selected.

Oracle10g\11g中，如果我們對目標(biāo)SQL執(zhí)行explain plan命令，則oracle就將解析目標(biāo)SQL所產(chǎn)生的執(zhí)行計(jì)劃的具體執(zhí)行步驟寫入PLAN_TABLE$，隨后執(zhí)行select * from table(dbms_xplan.display)，只是從PLAN_TABLE$中將這些具體執(zhí)行步驟以格式化的方式顯示出來。

PLAN_TABLE$是一個(gè)on commit preserve rows的global temporary table,所以這里Oracle可以做到各個(gè)session只能看到自己執(zhí)行的SQL所產(chǎn)生的執(zhí)行計(jì)劃，并且各個(gè)session往PLAN_TABLE$寫入執(zhí)行計(jì)劃的過程互不干擾。