存储过程没有执行计划,查看的是存储过程中SQL语句的执行计划,这里用10046来看
----------------------------------------------------
创建存储过程
CreateOrReplaceProcedureLee_Xc(Eno Number)Is
Begin
SelectEmpno, Ename, Dname
FromEmp, Dept
WhereEmp.Deptno = Dept.Deptno
AndEmp.Deptno = Eno;
Commit;
End;
/
----------------------------------------------------
追踪10046
SQL> altersessionsetevents'10046 trace name context forever, level 12';
Session altered
SQL> execLEE_HK(8800186378);
PL/SQL proceduresuccessfully completed
SQL> altersessionsetevents'10046 trace name context off';
Session altered
SQL>
SQL> SELECTd.VALUE
2 || '/'
3 || LOWER(RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc'as"trace_file_name"
7 FROM(SELECTp.spid
8 FROMv$mystat m, v$session s, v$process p
9 WHEREm.statistic# = 1ANDs.SID = m.SIDANDp.addr = s.paddr) p,
10 (SELECTt.INSTANCE
11 FROMv$thread t, v$parameter v
12 WHEREv.NAME='thread'
13 AND(v.VALUE = 0ORt.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECTVALUE
15 FROMv$parameter
16 WHERENAME='user_dump_dest') d
17 ;
trace_file_name
--------------------------------------------------------------------------------
/oracle/admin/jsjdata0/udump/jsjdata0_ora_19658.trc
----------------------------------------------------
格式转换一下
[oracle@jsb2011data udump]$ tkprof jsjdata0_ora_19984.trc lee1 sys=noexplain=gcbb/gcbb
[oracle@jsb2011data udump]$ more lee5.prf
TKPROF: Release 10.2.0.1.0 - Production onThu Apr 26 14:50:56 2012
Copyright (c) 1982, 2005, Oracle. Allrights reserved.
Trace file: jsjdata0_ora_19984.trc
Sort options: default
********************************************************************************
count= numberoftimes OCIprocedurewas executed
cpu = cpu timeinseconds executing
elapsed = elapsed timeinseconds executing
disk = number ofphysical readsofbuffersfromdisk
query = number ofbuffers gottenforconsistentread
current= numberofbuffers gottenincurrentmode (usuallyforupdate)
rows= numberofrowsprocessedbythefetchorexecutecall
********************************************************************************
altersessionsetevents'10046 trace name context forever, level 12'
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute1 0.00 0.00 0 0 0 0
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses inlibrary cache during parse: 0
Misses inlibrary cache duringexecute: 1
Optimizer mode: ALL_ROWS
Parsing userid: 54 (SCOTT)
Elapsed times include waiting onfollowing events:
Event waited onTimesMax. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message toclient 1 0.00 0.00
SQL*Net message fromclient 1 0.03 0.03
********************************************************************************
begin:id := sys.dbms_transaction.local_transaction_id;end;
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute4 0.00 0.00 0 0 0 4
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 0 0 4
Misses inlibrary cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing userid: 54 (SCOTT)
Elapsed times include waiting onfollowing events:
Event waited onTimesMax. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message toclient 4 0.00 0.00
SQL*Net message fromclient 4 8.29 16.08
********************************************************************************
select'x'
from
dual
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute2 0.00 0.00 0 0 0 0
Fetch2 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 0 0 2
Misses inlibrary cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing userid: 54 (SCOTT)
RowsRow Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=10 us)
RowsExecution Plan
------- ---------------------------------------------------
0 SELECTSTATEMENT MODE: ALL_ROWS
1 FAST DUAL
Elapsed times include waiting onfollowing events:
Event waited onTimesMax. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message toclient 4 0.00 0.00
SQL*Net message fromclient 4 0.01 0.03
********************************************************************************
beginlee_xc(7788);end;--这里可以看到输入的参数
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute1 0.00 0.00 0 0 0 1
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses inlibrary cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing userid: 54 (SCOTT)
Elapsed times include waiting onfollowing events:
Event waited onTimesMax. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.00 0.00
SQL*Net message toclient 1 0.00 0.00
SQL*Net message fromclient 1 0.03 0.03
********************************************************************************
UPDATEEMPSETSAL = 30000--这里看到带参的SQL
WHERE
EMPNO = :B1
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute1 0.00 0.00 0 1 2 1
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 1 2 1
Misses inlibrary cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing userid: 54 (SCOTT) (recursive depth: 1)
RowsExecution Plan---这里就是执行计划了
------- ---------------------------------------------------
0 UPDATESTATEMENT MODE: ALL_ROWS
0 UPDATEOF'EMP'
0 INDEXMODE: ANALYZED (UNIQUESCAN)OF'PK_EMP'(INDEX
(UNIQUE))
********************************************************************************
COMMIT
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute1 0.00 0.00 0 0 1 0
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 1 0
Misses inlibrary cache during parse: 0
Parsing userid: 54 (SCOTT) (recursive depth: 1)
********************************************************************************
altersessionsetevents'10046 trace name context off'
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute1 0.00 0.00 0 0 0 0
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses inlibrary cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing userid: 54 (SCOTT)
********************************************************************************
OVERALL TOTALS FORALLNON-RECURSIVE STATEMENTS
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute9 0.00 0.00 0 0 0 5
Fetch2 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 0 0 7
Misses inlibrary cache during parse: 2
Misses inlibrary cache duringexecute: 1
Elapsed times include waiting onfollowing events:
Event waited onTimesMax. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message toclient 10 0.00 0.00
SQL*Net message fromclient 10 8.29 16.17
log file sync 1 0.00 0.00
OVERALL TOTALS FORALLRECURSIVE STATEMENTS
call countcpu elapsed disk querycurrentrows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute2 0.00 0.00 0 1 3 1
Fetch0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses inlibrary cache during parse: 0
11 userSQL statementsinsession.
0 internal SQL statements insession.
11 SQL statements insession.
2 statements EXPLAINed inthis session.
********************************************************************************
Trace file: jsjdata0_ora_19984.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session intracefile.
11 userSQL statementsintrace file.
0 internal SQL statements intrace file.
11 SQL statements intrace file.
7 uniqueSQL statementsintrace file.
2 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Defaulttablewas used.
Tablewas created.
Tablewas dropped.
144 lines intrace file.
16 elapsed seconds intrace file.