如何oracle的获取执行计划
1.获取普通执行计划，效果类似于先执行set autot on exp;然后执行sql。
  explan plan for your_sql;
  select * from table(dbms_xplan.display);
2.获取具有outline信息的执行计划，用sqlprofile调优时非常有用，或者用这个执行计划了解更多oracle内部的hint
  explan plan for your_sql;
  select * from table(dbms_xplan.display(null, null,'advanced -projection'))
3.真实的执行计划，可以看到实际的 Starts（执行次数） | E-Rows（估算的返回行数） | A-Rows（实际的返回行数）
  ALTER SESSION SET STATISTICS_LEVEL=ALL; 
  execute your_sql;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'))
  
那么这3中获取执行计划的方式可以写到一个脚本getplan.sql，用的时候非常方便。
--getplan.sql
set feedback off timing off ver off
pro 'general,outline,starts'
pro
acc type prompt 'Enter value for plan type:' default 'general'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) where '&&type'='starts';
select * from table(dbms_xplan.display) where '&&type'='general';
select * from table(dbms_xplan.display(null, null,'advanced -projection')) where '&&type'='outline';
set feedback on timing on ver on
undef type


测试如下：
SQL> select * from a;

        ID NAME
---------- ----------
         1 a1
         2 a2
         3 a3
         4 a4
         5 a5
SQL> select * from b;

        ID NAME
---------- ----------
         1 b1
         2 b2
         
         
         
--执行计划1：普通执行计划
SQL> explain plan for select a.*,(select name from b where b.id=a.id) from a;

Explained.

Elapsed: 00:00:00.04
SQL> @getplan
'general,outline,starts'

Enter value for plan type:                ----这里输入general或回车

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3653839899

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| B    |     1 |    20 |     3   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL| A    |     5 |   100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("B"."ID"=:B1)

Note
-----
   - dynamic sampling used for this statement
   
   
   
--执行计划2：outline执行计划
SQL> explain plan for select a.*,(select name from b where b.id=a.id) from a;

Explained.

Elapsed: 00:00:00.01
SQL> @getplan
'general,outline,starts'

Enter value for plan type:outline           --这里输入outline

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3653839899

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| B    |     1 |    20 |     3   (0)| 00:00:01 |
|   2 |  TABLE ACCESS FULL| A    |     5 |   100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - SEL$2 / B@SEL$2
   2 - SEL$1 / A@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$2" "B"@"SEL$2")
      FULL(@"SEL$1" "A"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      O