儲存過程中檢視sql執行計劃的方法

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

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-1322924/，如需轉載，請註明出處，否則將追究法律責任。