地图网站开发/网站维护需要多长时间
存储过程没有计划的，只有sql有计划，看存储过程整体效率用DBMS_PROFILE包或查表 v$sql_plan；
转摘：http://blog.csdn.net/wadekobe9/article/details/7721527
存储过程没有执行计划，查看的是存储过程中SQL语句的执行计划，这里用10046来看
----------------------------------------------------
创建存储过程
Create Or Replace Procedure Lee_Xc(Eno Number) Is
Begin
Select Empno, Ename, Dname
From Emp, Dept
Where Emp.Deptno = Dept.Deptno
And Emp.Deptno = Eno;
Commit;
End;
/
----------------------------------------------------
追踪10046
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered
SQL> exec LEE_HK(8800186378);
PL/SQL procedure successfully completed
SQL> alter session set events '10046 trace name context off';
Session altered
SQL>
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' as "trace_file_name"
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = '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=no explain=gcbb/gcbb
[oracle@jsb2011data udump]$ more lee5.prf
TKPROF: Release 10.2.0.1.0 - Production on Thu Apr 26 14:50:56 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: jsjdata0_ora_19984.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
alter session set events '10046 trace name context forever, level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 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 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.03 0.03
********************************************************************************
begin :id := sys.dbms_transaction.local_transaction_id; end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 0 0 4
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 8.29 16.08
********************************************************************************
select 'x'
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=10 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 FAST DUAL
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 0.01 0.03
********************************************************************************
begin lee_xc(7788); end; --这里可以看到输入的参数
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 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.03 0.03
********************************************************************************
UPDATE EMP SET SAL = 30000 --这里看到带参的SQL
WHERE
EMPNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT) (recursive depth: 1)
Rows Execution Plan ---这里就是执行计划了
------- ---------------------------------------------------
0 UPDATE STATEMENT MODE: ALL_ROWS
0 UPDATE OF 'EMP'
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_EMP' (INDEX
(UNIQUE))
********************************************************************************
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 1 0
Misses in library cache during parse: 0
Parsing user id: 54 (SCOTT) (recursive depth: 1)
********************************************************************************
alter session set events '10046 trace name context off'
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
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 9 0.00 0.00 0 0 0 5
Fetch 2 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19 0.00 0.00 0 0 0 7
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 10 0.00 0.00
SQL*Net message from client 10 8.29 16.17
log file sync 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses in library cache during parse: 0
11 user SQL statements in session.
0 internal SQL statements in session.
11 SQL statements in session.
2 statements EXPLAINed in this session.
********************************************************************************
Trace file: jsjdata0_ora_19984.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
11 user SQL statements in trace file.
0 internal SQL statements in trace file.
11 SQL statements in trace file.
7 unique SQL statements in trace file.
2 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
144 lines in trace file.
16 elapsed seconds in trace file.
转载于:https://blog.51cto.com/summervast/1530254