如果sql在存储过程中，用set autotrace traceonly的方法一般不易直接查看，本文尝试了两种方法搜集存储过程中的执行计划
一 explain plan方法
测试用的存储过程
declare
  p varchar2(10) ;
begin
  p:='15%';
  execute immediate 'explain plan for select  h.id,h.phone　from test.test_his H where h.phone like :1'
  using p;
end;
查看增加 explain plan for的sql的执行计划
select * from table(dbms_xplan.display());

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   561 |  5610 |    14   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_HIS |   561 |  5610 |    14   (8)| 00:00:01 |

二 10046事件方法
打开跟踪
alter session set tracefile_identifier='PLAN_TEST';
//alter session set sql_trace=true; //打开会话跟踪
alter session set events '10046 trace name context forever, level 4';//在sql_trace基础上增加收集的绑定变量值
测试用存储过程
declare
  p varchar2(10) ;
begin
  p:='15%';
  execute immediate 'select  h.id,h.phone　from test.test_his H where h.phone like :1'
  using p;
end;
关闭跟踪
alter session set events '10046 trace name context off'
查看跟踪文件位置
show parameter user_dump_dest

跟踪文件sql部分执行计划的内容
PARSING IN CURSOR #3 len=65 dep=1 uid=55 oct=3 lid=55 tim=281862249663 hv=2556942037 ad='9d28cda8'
select  h.id,h.phone　from test.test_his H where h.phone like :1
END OF STMT
PARSE #3:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=281862249660
BINDS #3:
kkscoacd
 Bind#0
  oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
  oacflg=13 fl2=206001 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=09800f1c  bln=32  avl=03  flg=05
  value="15%"
EXEC #3:c=0,e=150,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=281862249928
EXEC #6:c=0,e=467,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=281862249997
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=52009 op='TABLE ACCESS FULL TEST_HIS (cr=0 pr=0 pw=0 time=3 us)'

还可以通过tkprof工具解析文件格式
tkprof orcl_ora_84536_plan_test.trc phone_trace.txt print=100 record=sql.txt sys=no explain=TEST/TEST

文件结果如下
********************************************************************************

declare
  p varchar2(10) ;
begin
  p:='15%';
  execute immediate 'select  h.id,h.phone from test.test_his H where h.phone like :1'
  using p;
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.01          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55 
********************************************************************************

select  h.id,h.phone from test.test_his H where h.phone like :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL TEST_HIS (cr=0 pr=0 pw=0 time=2 us)

********************************************************************************