TKPROF——SQL语句优化工具
将SQL_trace文件格式化 ，前提是必须启动SQL_Trace
用法：
[oracle@test1 udump]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
[oracle@test1 udump]$ tkprof orcl_ora_12438.trc yes.txt ysy=no
TKPROF: Release 10.2.0.1.0 - Production on Sun Dec 4 04:02:44 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: orcl_ora_12438.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
********************************************************************************
The following statement encountered a error during parse:
select dname from dept where deptno=:"SYS_B_0
Error encountered: ORA-00942
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20 0.05 0.05 0 0 2 0
Execute 20 0.07 0.15 2 309 3 4
Fetch 24 0.07 0.17 2 21 0 31
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 64 0.21 0.38 4 330 5 35
Misses in library cache during parse: 14
Misses in library cache during execute: 9
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 255 0.10 0.11 0 0 28 0
Execute 401 0.16 0.23 5 885 12 4
Fetch 987 0.19 0.51 60 2253 23 1025
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1643 0.47 0.86 65 3138 63 1029
Misses in library cache during parse: 67
Misses in library cache during execute: 54
21 user SQL statements in session.
364 internal SQL statements in session.
385 SQL statements in session.
********************************************************************************
Trace file: orcl_ora_12438.trc
Trace file compatibility: 10.01.00
Sort options: default
5 sessions in tracefile.
55 user SQL statements in trace file.
1430 internal SQL statements in trace file.
385 SQL statements in trace file.
71 unique SQL statements in trace file.
4316 lines in trace file.
2356 elapsed seconds in trace file.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26465805/viewspace-713006/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
存储大纲_保持CBO的稳定性
请登录后发表评论
登录
全部评论
