获取执行计划的几种方法

１、执行SQL语句EXPLAIN PLAN,然后查询结果输出表
  utlxplan.sql
  plan_table(全局临时表，它会存储数据直到会话结束，多个并发用户可以互不影响彼此的工作)
  explain plan for select count(*) from t10;
col Id for 99
col Operation for a30;
col Name for a10
select id "Id",
       rtrim(lpad(' ', 2 * (level-1)) || rtrim(operation)|| ' ' ||options)  "Operation", object_name  "Name",cardinality "Rows",bytes "Bytes",
       cost "Cost "
       from plan_table
connect by prior id = parent_id
 start with id = 0;
select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display(null,null,'BASIC'));
select * from table(dbms_xplan.display(null,null,'BASIC ROWS BYTES'));
select * from table(dbms_xplan.display(null,null,'TYPICAL'));
select * from table(dbms_xplan.display(null,null,'TYPICAL -PREDICATE'));
select * from table(dbms_xplan.display(null,null,'TYPICAL PROJECTION'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ADVANCED -PROJECTION'));--高级执行计划可以看到QUERY BLOCK + OUTLINE DATA 


 2、查询一张动态性能视图，它显于缓存在库缓存中的执行计划
 v$sql_plan
 v$sql_plan_statistics
 v$sql_workarea
 v$sql_plan_statistics_all
 select count(*) from t10;
 ---查看最近执行的sql
select /* recentsql */ sql_id, child_number, hash_value, address, executions, sql_text
from v$sql
where parsing_user_id = (select user_id
from all_users
where username = 'SCOTT')
and command_type in (2,3,6,7,189)
and UPPER(sql_text) not like UPPER('%recentsql%');
select * from table(dbms_xplan.display_cursor('fu9fh7nx72xx8',0,'advanced'));
set serveroutput off;
var x number;
exec :x := 1;
SELECT /*+ GATHER_PLAN_STATISTICS */ * from t1 where id =:x;
 OR
ALTER SESSION SET STATISTICS_LEVEL=ALL; 
SELECT  * from t1 where id =:x;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));--特殊执行计划
--ALLSTATS:包含了IOSTATS和MEMSTATS的全部内容
--LAST:限定仅显示最后一次运行的统计数据
--ADVANCED:除了会输出完全格式中的所有内容外，还会看情况输出绑定变量窥视信息和计划概要(Outline)信息。
--注：如报以下错误：(解决set serveroutput off;)
NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
SELECT /*+ GATHER_PLAN_STATISTICS */ * from t2 order by 1
Plan hash value: 2552596561
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   2000 |00:00:00.01 |      11 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   2000 |   2000 |00:00:00.01 |      11 | 95232 | 95232 |83968  (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |   2000 |   2000 |00:00:00.01 |      11 |       |       |          |
----------------------------------------------------------------------------------------------------------------
--Cost(%CPU):优化器估算出完成当前操作的代价(包含子操作的代价),它是IO代价和CPU代价总和，其中IO代价是最基本的代价，而对于CPU代价，在默认情况下，优化器会将CPU代价计算在内，并且将CPU代价根据系统配置由特定的转换公式转换为IO代价。也可以通过优化器参数_optimizer_cost_model指定是否在代价模型中包括CPU代价。括号中数据即为CPU代价在总代价中的比例
--Start:当前操作的启动次数
--Buffers:当前操作中发生读内存的次数，包括一致性读(Consistent Read,CR)和当前模式读(Current Get,CU)
--Reads:当前操作中发生读磁盘的次数
--Write:当前操作中发生写磁盘的次数
--OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
--1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
--User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)


 3、查询自动工作量资料库(Automatic Workload Repository)或查询Statspack表，它显示存储在资料库中的执行计划
  dba_hist_sql_plan.sql_id
  dba_hist_sqltext.sql_id
 select sql_id,to_char(substr(sql_text,0,4000)) from dba_hist_sqltext where upper(sql_text) like 'SELECT * FROM T1%'; 
 select * from table(dbms_xplan.display_awr('bnj6pak6x2tn4'));
--PLAN_HASH_VALUE:执行计划的哈希值，每个执行计划都有一个哈希值，通过该值，可以显示SQL语句的特定的执行计划。如果该参数未指定或为NULL，则会显示语句的所有执行计划。


 4.查询存储在一个SQL调优集中的语句的执行计划
  DBA_SQLSET_PLANS.SQL_ID
  declare
  ss_name varchar2(30);
  begin
   ss_name := dbms_sqltune.create_sqlset();
   dbms_sqltune.capture_cursor_cache_sqlset(ss_name,300,100);
   dbms_output.put_line(ss_name);
  end;
  /
  select sqlset_name,sql_id,sql_text from DBA_SQLSET_STATEMENTS where upper(sql_text) like 'SELECT * FROM T1%';
  select * from table(dbms_xplan.display_sqlset('STS_2','efaf35dfrd',null,'BASIC ROWS COST'));
 
 5、显示存储在数据字典中SQL执行计划基线的计划
  dba_sql_plan_baselines.sql_handle,默认为null
  select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_99ccreredfdfdl'));
  
 6、利用AUTOTRACE查看执行计划
　set autot on ----执行SQL 并且显示执行计划和统计信息
　set autot trace ----执行SQL 但不显示运行结果，显示执行计划和统计信息
　set autot trace exp ----如果SELECT 就不执行SQL(dml 执行)，只显示执行计划
　set autot trace stat ----执行SQL，只显示统计信息
　grant plustrace to gyj;


 7、启动提供执行计划的跟踪功能
  (1)sql跟踪
    alter session set sql_trace=true;
    dbms_monitor.session_trace_enable(session_id=>300,serial_num=>30,waits=>TRUE,binds=>FALSE);
    dbms_monitor.session_trace_disable(session_id=>300,serial_num=>30);
    tkprof(trace kernel profiler )
　(2)10046事件
     alter session set events '10046 trace name context forever,level 8';
     alter session set events '10046 trace name context off';