获取SQL执行计划

在做优化的过程中，在执行优化之前，我们必须先知道原来执行的SQL语句的执行计划是怎样。
这里，就讲述两种常用的方法或工具来获取执行计划。
dbms_xplan.display_cursor用来格式化v$sql_plan和v$sql_plan_statistics_all（是v$sql_plan,v$sql_plan_statistics和v$sql_workarea的信息合集）
显示格式有5种：Basic仅显示最少的信息)、typical（显示大部分相关信息）、serial、all(显示除了提纲外的所有信息)，advanced(显示所有信息)。

一、使用explain plan for获取执行计划，通过 select * from table (dbms_xplan.display(null,null,'basic')) 以不同格式化显示执行计划：

1、在hr用户下执行一条查询：select * from employees;

2、 basic格式显示：
SQL> select * from table (dbms_xplan.display(null,null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
---------------------------------------
| Id  | Operation         | Name      |
---------------------------------------
|   0 | SELECT STATEMENT  |           |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |
---------------------------------------
8 rows selected.

3、typical格式显示：
SQL> select * from table (dbms_xplan.display(null,null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.


4、 serial格式显示：
SQL> select * from table (dbms_xplan.display(null,null,'serial'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
8 rows selected.

5、 all格式显示：
SQL> select * from table (dbms_xplan.display(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
1 - SEL$1 / EMPLOYEES@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
       "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
       "EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
       "EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       "EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
       , "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
       "EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
25 rows selected.

6、 advanced格式显示：
SQL> select * from table (dbms_xplan.display(null,null,'advanced');
select * from table (dbms_xplan.display(null,null,'advanced')
                                                            *
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> select * from table (dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

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

   1 - SEL$1 / EMPLOYEES@SEL$1
Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMPLOYEES"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      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):
-----------------------------------------------------------

   1 - "EMPLOYEES"."EMPLOYEE_ID"[NUMBER,22],
       "EMPLOYEES"."FIRST_NAME"[VARCHAR2,20],

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       "EMPLOYEES"."LAST_NAME"[VARCHAR2,25], "EMPLOYEES"."EMAIL"[VARCHAR2,25],
       "EMPLOYEES"."PHONE_NUMBER"[VARCHAR2,20],
       "EMPLOYEES"."HIRE_DATE"[DATE,7], "EMPLOYEES"."JOB_ID"[VARCHAR2,10],
       "EMPLOYEES"."SALARY"[NUMBER,22], "EMPLOYEES"."COMMISSION_PCT"[NUMBER,22]
       , "EMPLOYEES"."MANAGER_ID"[NUMBER,22],
       "EMPLOYEES"."DEPARTMENT_ID"[NUMBER,22]
39 rows selected.
dbms_xplan.display_awr,专门用来格式化dba_hist_sql_plan中的执行计划，
综上所述，dbms_xplan可以格式化的显示plan table、库缓存区、awr中的信息。


二、使用autotrace工具显示执行计划：
同样登录hr用户，打开set  autotrace  tracenoly方式，则显示执行计划与解析：
直接执行随意的查询语句：

1、SQL> select * from employees
  2  where employee_id = 171;

Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=171)


2、SQL> select * from employees;
  2  where last_name = 'Sewall';

Execution Plan
----------------------------------------------------------
Plan hash value: 2077747057
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("LAST_NAME"='Sewall')


3、SQL> select * from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

以上就是常用的以下获取执行计划的方法。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2126484/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2126484/