tkprof xxxx.trc report,log
这个是经常使用用来个格式化trace的命令。所谓的格式化trace其实就是用tkprof来解释trace文件的内容,把原始的trace文件转化为容易理解的文件。
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:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor
以下是格式化定义后的一个例子:
********************************************************************************
select count(1)
from
t1 where owner='SYSTEM'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 118 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 119 0 2
Misses in library cache during parse: 1 --1为硬解析、0为软解析
Optimizer mode: ALL_ROWS --当前优化器的类型,CBO的话有all_rows和first_rows
Parsing user id: 55 --当前执行用户的ID,具体查看“user id查询”
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=59 pr=0 pw=0 time=562 us)
355 TABLE ACCESS FULL T1 (cr=59 pr=0 pw=0 time=822 us)
********************************************************************************
user id查询:
********************************************************************************
SQL> select username,user_id from all_users where user_id=55;
USERNAME USER_ID
------------------------------ ----------
XIAOYAO 55
在上面例子,还有几个项目还得解释解释:
********************************************************************************
(以下的英文解释在格式化定义后的文件头会存在)
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
既是CPU的时间,单位为秒。
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)
既是在current方式下读取的数据块数量。一般来说执行insert、update、delete操作都会获取buffer。
rows = number of rows processed by the fetch or execute call
既是sql语句返回的记录数。对于select语句,返回记录在fetch这步,对于insert、update、delete操作,返回记录则在execute。
********************************************************************************
Call 项目名字。
Parse 解释的过程。
Execute 真正执行的过程。
Fetch 执行完后返回记录的过程。
Rows,查询返回结果的行数。
cr --(consistent read),一致性方式读取的数据块,相当于query 的Fetch步骤。
pr --(physcical read), 物理读取的数据块,相当于disk列上的Fetch步骤的值。
pw --(physcical wirte),物理写。
time --当前在操作执行的时间。