oracle获取执行计划的方法

1. 查看预估的执行计划-- explain plan for

SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
  2  SELECT  *
  3  FROM t1, t2
  4  WHERE t1.OBJECT_ID = t2.OBJECT_ID;
 
Explained.
 
SQL> select * from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Plan hash value: 1838229974
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 |   808K|    20   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 |   808K|    20   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  2000 |   404K|    10   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  2000 |   404K|    10   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
19 rows selected.

explain plan for 命令将在执行后将解析目标SQL所产生的执行计划的具体步骤写入表 "SYS"."PLAN_TABLE$" 中。
select * from table(dbms_xplan.display()) 从 SYS.PLAN_TABLE$ 中将具体执行步骤以格式化的方式显示出来。
SYS.PLAN_TABLE$ 是 ON COMMIT PRESERVE ROWS 的全局临时表，它会存储数据直到会话结束，多个并发用户互不影响。
SYS.PLAN_TABLE$ 表是由 $ORACLE_HOME/rdbms/admin/utlxplan.sql 创建的。
获取 SYS.PLAN_TABLE$ 的DDL语句
SQL> select dbms_metadata.get_ddl('TABLE','PLAN_TABLE$','SYS') from dual; 

2.  查看预估的执行计划--AUTOTRACE

SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

set autotrace on                   ----输出所有内容，包括语句本身的查询结果、执行计划，以及性能统计数据
set autotrace on explain           ----输出所有内容，包括语句本身的查询结果和执行计划，不输出性能统计数据
set autotrace on statistics        ----输出所有内容，包括语句本身的查询结果和性能统计数据，不输出执行计划
set autotrace traceonly            ----输出执行计划和性能统计数据，不输出语句本身的查询结果
set autotrace traceonly explain    ----输出执行计划，不输出语句本身的查询结果和性能统计数据
set autotrace traceonly statistics ----输出性能统计数据，不输出语句本身的查询结果和执行计划

开启autotrace我们可以看到目标SQL执行时所耗费的物理读、逻辑读、产生redo的数量及排序的数量等。

SQL> set autotrace traceonly;
SQL> SELECT  *
  2  FROM t1, t2
  3  WHERE t1.OBJECT_ID = t2.OBJECT_ID;
 
2000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 |   808K|    20   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 |   808K|    20   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  2000 |   404K|    10   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  2000 |   404K|    10   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        188  consistent gets
          0  physical reads
          0  redo size
     174897  bytes sent via SQL*Net to client
       1986  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2000  rows processed

        当使用 set autotrace traceonly explain 时，如果执行的是 SELECT 语句，则该SELECT语句并没有被Oracle实际执行，但如果执行的是DML语句，是会被Oracle实际执行的。
        所以在使用 set autotrace on、set autotrace traceonly 和 set autotrace traceonly explain 来获取DML语句的执行计划要小心，因为这些DML语句实际上已经被执行了。
        虽然使用部分 set autotrace 命令后目标SQL实际上已经被执行过了，但所有使用 set autotrace 命令所得到的执行计划都有可能不准确，因为使用 set autotrace  命令所显示的执行计划都是来源于调用 explain plan 命令。

3.  查看现在的真实执行计划-- SHARE POOL

方式1
set autotrace off
set linesize 1000
set pagesize 2000
alter session set statistics_level=all;
SELECT  * FROM t1, t2 WHERE t1.OBJECT_ID = t2.OBJECT_ID;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

方式2
select * from table(dbms_xplan.display_cursor('fu9fh7nx72xx8',0,'advanced'));

通过以下视图得到执行计划
v$sql_plan
v$sql_plan_statistics
v$sql_workarea
v$sql_plan_statistics_all

select count(*) from t10;
查询sql_id
select sql_id,child_number,sql_text from v$sql where sql_text like '%select count(*) from t10%' and sql_text not like '%v$sql%';
生成执行计划
col plan_table_output for a300
set long 900
set pagesize 800
select * from table(dbms_xplan.display_cursor('fu9fh7nx72xx8',0,'advanced'));

4.  查看过去的真实执行计划-- AWR

获取执行计划的基表  WRH$_SQL_PLAN
查询自动工作量资料库(Automatic Workload Repository)或查询Statspack表，它显示存储在资料库中的执行计划
通过视图 dba_hist_sql_plan 查询SQL_ID    
select * from table(dbms_xplan.display_awr('gwq01ynnbm5aj'));

5.  真实执行计划--10046事件与TKprof命令

10046得到的执行计划中明确显示了目标SQL实际执行计划中的每一个执行步骤所消耗的逻辑读、物理读和花费的时间。这种细粒度的明细显示在我们诊断复杂SQL的性能问题时尤为有用。

在当前session激活10046事件的两种方法：
alter session set events '10046 trace name context forever,level 12';
oradebug event 10046 trace name context forever,level 12         --推荐使用，因为可以使用命令 oradebug tracefile_name得到trace文件路径

在当前session关闭10046事件的两种方法：
alter session set events '10046 trace name context off';
oradebug event 10046 trace name context off

tkprof命令是oracle自带的，用来翻译trace文件，使其更直观，易懂。

oradebug 举例（普通用户有可能没有权限执行）：

SQL> oradebug setmypid           --表示准备对当前session使用oradebug命令
SQL> oradebug event 10046 trace name context forever,level 12
SQL> select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;          --执行目标SQL
SQL> oradebug tracefile_name     --获取trace文件路径
SQL> oradebug event 10046 trace name context off
tkprof C:\app\diag\rdbms\aa\aa\trace\aa_ora_4260.trc E:\aa_ora_4260_tkprof.trc

裸trace文件中可以看到消耗的逻辑读(cr, consistent reads)、物理读（pr, physical reads）、耗费的时间（time,单位是微秒，1秒=1,000,000微秒）和实际返回的结果集的行数（card, cardinality）。

alter session 举例

alter session set events '10046 trace name context forever,level 12';
select empno,ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno;          --执行目标SQL
alter session set events '10046 trace name context off';
--获取trace文件路径
SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
    FROM v$process a, v$session b, v$parameter c, v$instance d
   WHERE a.addr = b.paddr
        AND b.audsid = userenv('sessionid')
        AND c.name = 'user_dump_dest'; 
--格式化trace文件
tkprof C:\app\diag\rdbms\aa\aa\trace\aa_ora_4260.trc E:\aa_ora_4260_tkprof.trc