如何查看执行计划

1、设置信息收集等级(设置all有额外负载，默认值： ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;)

SQL> alter session set statistics_level=ALL;

Session altered.

2、执行一个sql

SQL> select e.employee_id,e.job_id,j.min_salary,j.max_salary
       from hr.employees e, hr.jobs j
      where e.job_id = j.job_id;

3.1、查看刚才执行sql的执行计划(dbms_xplan.DISPLAY_CURSOR的三个参数：SQL_ID    Child Number     Format)

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1hrgu1ksy8xhs, child number 0
-------------------------------------
select e.employee_id,e.job_id,j.min_salary,j.max_salary  from
hr.employees e, hr.jobs j where e.job_id = j.job_id

Plan hash value: 2650640655

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      3 |        |    321 |00:00:00.01 |      60 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                  |                  |      3 |    107 |    321 |00:00:00.01 |      60 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS             |      3 |     19 |     57 |00:00:00.01 |      42 |       |       |          |
|   3 |    INDEX FULL SCAN           | JOB_ID_PK        |      3 |     19 |     57 |00:00:00.01 |      21 |       |       |          |
|*  4 |   SORT JOIN                  |                  |     57 |    107 |    321 |00:00:00.01 |      18 |  9216 |  9216 |     3/0/0|
|   5 |    VIEW                      | index$_join$_001 |      3 |    107 |    321 |00:00:00.01 |      18 |       |       |          |
|*  6 |     HASH JOIN                |                  |      3 |        |    321 |00:00:00.01 |      18 |  1519K|  1519K|     3/0/0|
|   7 |      INDEX FAST FULL SCAN    | EMP_EMP_ID_PK    |      3 |    107 |    321 |00:00:00.01 |       9 |       |       |          |
|   8 |      INDEX FAST FULL SCAN    | EMP_JOB_IX       |      3 |    107 |    321 |00:00:00.01 |       9 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   4 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
   6 - access(ROWID=ROWID)


28 rows selected.

3.2 dbms_xplan.DISPLAY_CURSOR的三个参数

(1)SQL_ID :查询如下

SQL> select sql_id,sql_text
  2  from v$sql
  3  where sql_text not like '%like%'
  4  and sql_text like '%hr.employee%';

SQL_ID                  SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------
1hrgu1ksy8xhs         select e.employee_id,e.job_id,j.min_salary,j.max_salary  from hr.employees e, hr.jobs j where e.job_id = j.job_id

----(2)Child Number：查询如下

SQL> select child_number from v$sql where sql_id = '1hrgu1ksy8xhs';

CHILD_NUMBER
------------
           0

----(3)Format 如下

ALLSTATS = IOSTATS + MEMSTATS
IOSTATS          显示该游标累计执行的IO统计信息(Buffers,  Reads)
MEMSTATS      累计执行的PGA使用信息(Omem  1Mem Used-Mem)
LAST                仅显示最后一次执行的统计信息
Advanced        显示outline、Query Block Name、 Column Projection等信息
PEEKED_BINDS  打印解析时使用的绑定变量
Typical       不打印PROJECTION, ALIAS

3.3使用dbms_xplan.DISPLAY_CURSOR的三个参数，再次精确的查询执行计划

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('1hrgu1ksy8xhs', 0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1hrgu1ksy8xhs, child number 0
-------------------------------------
select e.employee_id,e.job_id,j.min_salary,j.max_salary  from
hr.employees e, hr.jobs j where e.job_id = j.job_id

Plan hash value: 2650640655

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |    107 |00:00:00.01 |      20 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                  |                  |      1 |    107 |    107 |00:00:00.01 |      20 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| JOBS             |      1 |     19 |     19 |00:00:00.01 |      14 |       |       |          |
|   3 |    INDEX FULL SCAN           | JOB_ID_PK        |      1 |     19 |     19 |00:00:00.01 |       7 |       |       |          |
|*  4 |   SORT JOIN                  |                  |     19 |    107 |    107 |00:00:00.01 |       6 |  9216 |  9216 | 8192  (0)|
|   5 |    VIEW                      | index$_join$_001 |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
|*  6 |     HASH JOIN                |                  |      1 |        |    107 |00:00:00.01 |       6 |  1519K|  1519K| 1528K (0)|
|   7 |      INDEX FAST FULL SCAN    | EMP_EMP_ID_PK    |      1 |    107 |    107 |00:00:00.01 |       3 |       |       |          |
|   8 |      INDEX FAST FULL SCAN    | EMP_JOB_IX       |      1 |    107 |    107 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   4 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
   6 - access(ROWID=ROWID)


28 rows selected.