如果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)
********************************************************************************