oracle执行计划获取的几种方式

SQL> set autotrace  
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
举例：

SQL> set autot on 
SQL> select count(*) from plan_table;

  COUNT(*)
----------
  68
Execution Plan
----------------------------------------------------------
Plan hash value: 1751138260

--------------------------------------------------------------------------
| Id  | Operation  | Name  | Rows  | Cost (%CPU)| Time   |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   |   1 |   3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |   |   1 |  |  |
|   2 |   TABLE ACCESS FULL| PLAN_TABLE$ |  68 |   3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
 - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
 27  recursive calls
  0  db block gets
 15  consistent gets
  0  physical reads
  0  redo size
  515  bytes sent via SQL*Net to client
  487  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed
主要看consistent gets和physical reads，consistent gets是内存消耗，physical reads是磁盘的消耗，单位就是数据块。其他指标为辅。如果不需要列出语句的结果，那么 set autotrace traceonly即可。
2.explain plan for 

举例：
SQL> explain plan for select * from book_info;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3200443156

-------------------------------------------------------------------------------
| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |   |  1363 |   177K|   9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| BOOK_INFO |  1363 |   177K|   9   (0)| 00:00:01 |
-------------------------------------------------------------------------------
当然以上这个是最简单的使用，explain是DML语句，并不是隐式提交。
statment_id指定了需要为那些SQL语句提供执行计划的标识
id指定一个名字，区分执行计划表中的多个执行计划
table指定了计划表的表名，默认是plan_table
这里注意一点，在9i中plan_table是普通表，10g中是同义词，可以通过数据字典查看。
@?/rdbms/admin/utlxplan可以创建计划表
@?/rdbms/admin/utlxpls也可以阅读执行计划表，和上面调用dbms_xplan.display是一样的。
@?/rdbms/admin/utlxplp查看并行处理的信息。
不过这里看不到一致性读和物理读等，看不到IO的统计信息，不过这个在10g以后可以通过display_cursor可以看了。
这里还要注意变量的替换问题：
比如在一个PL/SQL中：select * from tab1 where name=p_value;
那么想要查看这个执行计划的话，千万不要用常量去替换，用select * from tab1 where name=:p_value即可。
通过statsment_id查看执行计划：
SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1409354130

----------------------------------------------------------------------------------------------------
| Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |   1 |   133 |   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOOK_INFO  |   1 |   133 |   2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN   | PK_BOOK_BOOK_INFO_ID |   1 |   |   1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
 2 - access("ID"=10270)

SQL>  explain plan set statement_id='t1' for select * from book_info where id=20360;
SQL> select * from table(dbms_xplan.display(null,'t1'));

PLAN_TABLE_OUTPUT
Plan hash value: 1409354130

----------------------------------------------------------------------------------------------------
| Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |  |   1 |   133 |   2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BOOK_INFO  |   1 |   133 |   2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN   | PK_BOOK_BOOK_INFO_ID |   1 |   |   1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - access("ID"=20360)
关于format参数：
select * from table(dbms_xplan.display(null,null,'basic'));


3.AWR
由于10g以后自动安装AWR收集信息，所以可以通过dbms_xplan包提供的display_awr函数来查看AWR抽取SQL的计划。
这里是通过sql_id参数传入的进行查看。
举例：
select * from table(dbms_xplan.display_awr('xxxxxxxx'));
这里初步介绍下，就不再深入各个参数。

4.事件跟踪，比如10046,10053等
sql_trace
会话跟踪alter session set sql_trace=true;--跟踪当前会话，false为结束跟踪
当然也可以跟踪其他会话的
select sid,serial#,username from v$session.......根据自己需求查出sid,serial#,进行下一步跟踪
exec dbms_system.set_sql_trace_in_session(sid,serial#,true); --开始
exec dbms_system.set_sql_trace_in_session(sid,serial#,false) ; --结束
这种方法是不显示绑定信息和等待事件的，如果要全面信息，需要设置events等级，也就是10046事件
alter session set events '10046 trace name context  forever,level 12';

level 1：跟踪sql语句，包括解析、执行、提取、提交和回滚等。
level 4：包括变量的详细信息
level 8：包括等待事件
level 12：包括绑定变量与等待事件

alter session set evevts '10046 trace name context off'; 结束跟踪
还有就是dbms_system的set_ev过程也是可以的。另外再10g中有了dbms_monitor可以用来跟踪。
查出跟踪文件：
alter session set tracefile_identifier='xxxxxx';
设定标识，以便查找，默认路径在是udump下面，11G路径当然有些变化是在，$ORACLE_BASE\diag\rdbms\orcl\orc