如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。 如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。

explain plan for 方式存取sql的执行计划查看的只是最近的Plan table中的信息

实验：

1.以explain plan for 的方式进行操作存入sql执行计划；

SYS@ORA11GR2>conn hr/hr  

Connected.

HR@ORA11GR2>explain plan for select count(*) from employees;

Explained.

2.查看dbms_xplan包

SYS@ORA11GR2>desc dbms_xplan

3.以basic模式查看执行计划：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3580537945

-----------------------------------------

| Id  | Operation        | Name         |

-----------------------------------------

|   0 | SELECT STATEMENT |              |

|   1 |  SORT AGGREGATE  |              |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |

-----------------------------------------

9 rows selected.

4.以typical模式查看执行计划：（默认是typical）

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'typical'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3580537945

-------------------------------------------------------------------------

| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------

9 rows selected

5. 以serial模式查看执行计划：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'serial'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3580537945

-------------------------------------------------------------------------

| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------

9 rows selected.

6. 以all模式查看执行计划：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'all'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3580537945

-------------------------------------------------------------------------

| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / EMPLOYEES@SEL$1

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

20 rows selected.

7. 以advanced模式查看执行计划：

HR@ORA11GR2>select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3580537945

-------------------------------------------------------------------------

| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |              |     1 |            |          |

|   2 |   INDEX FULL SCAN| EMP_EMAIL_UK |   107 |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / EMPLOYEES@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      INDEX(@"SEL$1" "EMPLOYEES"@"SEL$1" ("EMPLOYEES"."EMAIL"))

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      DB_VERSION('11.2.0.4')

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Column Projection Information (identified by operation id):

-----------------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

34 rows selected.