如果要分析某条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.