Oracle View SQL execution Plan 1--explain plan

Syntax:

Explain plan for + Target 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.

In Oracle10g\ 11g, if we execute the explain plan command on the target SQL, oracle writes the specific execution steps of the execution plan generated by parsing the target SQL into the PLAN_TABLE$, and then executes select * from table (dbms_xplan.display), just showing these specific execution steps in a formatted manner from the PLAN_TABLE$.

PLAN_TABLE$ is a global temporary table of on commit preserve rows, so here Oracle can make sure that each session can only see the execution plan generated by the SQL that it executes, and the process of each session writing the execution plan to the PLAN_TABLE$ does not interfere with each other.