TKPROFis the basic performance diagnostic tool that can help you monitor and tune applications running against the Oracle Server.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 calledprscpu cpu time parsingprsela elapsed time parsingprsdsk number of disk reads during parseprsqry number of buffers for consistent read during parseprscu number of buffers for current read during parseprsmis number of misses in library cache during parseexecnt number of execute was calledexecpu cpu time spent executingexeela elapsed time executingexedsk number of disk reads during executeexeqry number of buffers for consistent read during executeexecu number of buffers for current read during executeexerow number of rows processed during executeexemis number of library cache misses during executefchcnt number of times fetch was calledfchcpu cpu time spent fetchingfchela elapsed time fetchingfchdsk number of disk reads during fetchfchqry number of buffers for consistent read during fetchfchcu number of buffers for current read during fetchfchrow number of rows fetcheduserid userid of user that parsed the cursor
Trace Output
SQL STATEMENTS PHASES
- Parse
- Execute
- Fetch
count cpu elap disk query current rows Parse: 1 1 2 0 0 0 Execute: 1 0 0 0 0 2 0 Fetch: 2 69 113 142 430 0 36
The output shows 142 disk reads and 430 memory reads (query + current). Having such a high number of disk reads compared to physical reads is certainly a potential problem. The execution path shows a full table scan confirming that we may have a potential problem.
count
The number of times this type of call was made.
cpu
The total CPU time for all of the calls of this type for this statement. If the TIMED_STATISTICS parameter in the init.ora is not set to TRUE, this statistic and the elapsed statistic will be 0.
elapsed
The total elapsed time for this call.
disk
The total number of data blocks retrieved from disk to satisfy this call.
query
The total number of data buffers retrieved from memory for this type SELECT statements usually retrieve buffers in this mode.
current
The total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE the usual access buffers in this mode.
rows
The total number of rows processed by this statement. The rows statements will appear in the row of Fetch statistics. INSERTS, UPDATES, and DELETES will appear in the execute row.