解释tkprof程序产生的格式化文件:
tkprof的格式化输出文件主要包含两大部分:
header
body
summary
header:
主要包括一些描述信息,如TKPROF的版本、运行时间,各个统计项的描述。如:
TKPROF: Release 8.1.7.0.0 - Production on 星期四 6月 30 13:10:59 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: D:\oracle\admin\xyj\udump\ORA01720.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
********************************************************************************
body:
是我们主要关心的地方,有我们感兴趣的信息。如sql语句、sql语句的统计信息、sql语句的执行计划等。如
select *
from
emp
call count cpu elapsed disk query current rows
------- ----- ----- ------- ----- ------ -------- -----
Parse 3 0.00 0.00 1 0 1 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 1 6 12 36 61
------- ----- ----- ------- ----- ------ -------- -----
total 12 0.00 0.00 2 6 13 36
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 19 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
12 TABLE ACCESS FULL EMP
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
12 TABLE ACCESS (FULL) OF 'EMP'
DELETE FROM RM$HASH_ELMS
call count cpu elapsed disk query current rows
------- ------ ------ -------- ----- ------ -------- ----
Parse 2 0.00 0.00 0 0 0 0
Execute 29 12.04 12.61 6786 6853 108 19
Fetch 0 0.00 0.00 0 0 0 0
------- ------ ------ -------- ----- ------ -------- ----
total 31 12.04 12.61 6786 6853 108 19
Misses in library cache during parse: 0
Optimizer hint: CHOOSE
Parsing user id: 9 (DES12A) (recursive depth: 3)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT HINT: CHOOSE
16 TABLE ACCESS (FULL) OF 'RM$HASH_ELMS'
summary:
对所有的sql语句各个执行阶段的统计数据的汇总:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS - - 标明是用户发出的sql语句的统计数据的汇总
call count cpu elapsed disk query current rows
------- ------ -------- -------- ----- ------ -------- -----
Parse 7 0.00 0.00 2 201 2 0
62
Execute 7 0.00 0.00 1 2 7 1
Fetch 10 0.00 0.00 2 67 16 52
------- ------ -------- -------- ----- ------ -------- -----
total 24 0.00 0.00 5 270 25 53
Misses in library cache during parse: 5
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS - - 标明是系统发出的sql语句的统计数据的汇总
call count cpu elapsed disk query current rows
------- ------ ------ -------- ----- ------ -------- ----
Parse 75 0.00 0.00 2 3 2 0
Execute 81 0.00 0.00 1 1 5 1
Fetch 153 0.00 0.00 21 355 0 110
------- ------ ------ -------- ----- ------ -------- ----
total 309 0.00 0.00 24 359 7 111
Misses in library cache during parse: 29
8 user SQL statements in session.
74 internal SQL statements in session.
82 SQL statements in session.
5 statements EXPLAINed in this session.
*********************************************************************
Trace file: D:\oracle\admin\xyj\udump\ORA01720.TRC
Trace file compatibility: 8.00.04
Sort options: default
1 session in tracefile.
8 user SQL statements in trace file.
74 internal SQL statements in trace file.
82 SQL statements in trace file.
34 unique SQL statements in trace file.
5 SQL statements EXPLAINed using schema:
SCOTT.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
825 lines in trace file.