在追寻某个sql的性能之前我们需要先从v$session视图中找出我们需要查看的sql的SID,serial#
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
139 61 SUNXIN
146 11 SYS
而后我们需要启动dbms_system.set_sql_trace_in_session包,因为它比较耗用系统资源,使用之后一定要记得关闭
SQL> exec dbms_system.set_sql_trace_in_session(139,61,true);
PL/SQL 过程已成功完成。
而后我们在 61用户中运行我们的sql 语句,一段时候后关闭追踪
SQL> exec dbms_system.set_sql_trace_in_session(139,61,false);
PL/SQL 过程已成功完成。
找出我们的udump的位置
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'
2 ;
VALUE
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP
由于TRACE 文件我们直接读取不方便,我们使用ORACLE 自带的工具tkprof来解析我们的sql trace
C:\Documents and Settings\Administrator>tkprof D:\oracle\product\10.2.0\admin\or
cl\udump\orcl_ora_504.trc
output = D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_504.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期五 3月 23 10:25:26 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
看下我们解析后得trace文件
*******************************************************************************
select *
from
scott.dept
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.03 0.02 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 6 32 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.03 0.03 6 32 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=90 us)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.03 0.02 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 6 32 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.03 0.03 6 32 0 16
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 16 0.00 0.01 0 0 0 0
Execute 113 0.04 0.09 0 0 0 0
Fetch 121 0.01 0.20 19 344 0 768
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 250 0.06 0.30 19 344 0 768
Misses in library cache during parse: 14
Misses in library cache during execute: 14
4 user SQL statements in session.
113 internal SQL statements in session.
117 SQL statements in session.
********************************************************************************
Trace file: D:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_504.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
4 user SQL statements in trace file.
113 internal SQL statements in trace file.
117 SQL statements in trace file.
16 unique SQL statements in trace file.
1044 lines in trace file.
3 elapsed seconds in trace file.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21416913/viewspace-719343/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21416913/viewspace-719343/