you're reading...
Oracle Database Performance Tuning

Oracle Performance Analysis Section2: TKPROF Introduction

Oracle’s own tkprof is a command-line tool, the main role is to trace the original files into formatted text file, the easiest use is as follows:

tkprof ly_ora_128636.trc ly_ora_128636.txt

tkprof with many parameters, in most cases, the use of your analysis of these parameters would be helpful

tkprof parameters

Without any parameters tkprof, it will print out the complete list of parameters, and with a simple description. The following is a description of the parameters:


Provide an implementation plan for each SQL statement. The parameters specify the user and password, you can also specify the database connection string, such as:

explain = user/password@connect_string


explain = user/password


Tray explain the parameters used together table specifies execution plan to use, typically do not need, only when you can not create a table needs (such as the lack of create table permission).


For limiting the number of the output file generated SQL statements, such as: print = 10.


Specifies whether a separate deal with the same SQL statement, the default is not treated separately. Designated as the aggregate = no, see separate each SQL statement.


Generate SQL scripts, SQL scripts, can be used to store information in a database, SQL script name is specified by the parameters, such as: insert = load.sql.


SQL statement specifies whether the user is running sys written to the output file, the default yes.


Generate SQL scripts, which contains all non-recursive statements found in the trace file, the script name specified by the argument itself, for example: record = replay.sql.


Whether to add the wait event information added by default.


Specify the output file in the SQL statement sequence. The default is SQL trace file sequence found.

Here is an example:

tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela

prsela: The first time it takes to resolve a cursor

exeela: the time it takes to execute against the cursor

fchela: Cursor Gets the time it takes data rows

tkprof output

Output file with a header, which has a description of the parameters, as follows:

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

count: number of database calls execute

cpu: processing the data call CPU time spent, in seconds

elapsed: process database called the total time spent, in seconds

disk: the number of data blocks physically read, if greater than the number of logical reads (disk> query + current), that the use of the temporary table space.

query: consistent pattern in the number of cache logic from the fast read, as a query.

current: the number of blocks in the current mode is read from the cache logic for insert, delete, merge and update operations.

rows: the number of rows of data processing. Inquiries, the number of rows retrieved, and insert, delete, merge and update, etc. indicates the number of rows affected.

Let’s look at a specific example:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      701      0.04       0.17          0       1765          0       70001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      703      0.04       0.17          0       1765          0       70001

The above correspond to the parse, execute, and fetch these three stages, execution fetch stage 501 times fetch, get the 50001 rows of data, each fetch obtain 100 rows of data.

Next is:

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 

The first two lines represents a hard number to resolve occurred in parsing and execute the call stage, if not hard to resolve, does not exist.

“Optimizer mode” represents optimization mode.

“Parsing user id” SQL statement is parsed users.

After this we can see the implementation plan, where only a simple explanation:

Rows     Row Source Operation
-------  ---------------------------------------------------
  50001  COUNT STOPKEY (cr=1465 pr=0 pw=0 time=300125 us)
  50001   VIEW  (cr=1465 pr=0 pw=0 time=200088 us)
  50001    INDEX FULL SCAN IDX_HISTORYALARM$CLEAR (cr=1465 pr=0 pw=0 time=100049 us)(object id 53743)

cr: the consistency of data in the readout mode logic blocks

pr: number of blocks read from disk physical

pw: physical data blocks written to disk

time: the total time-consuming subtle represented attention inaccurate data

cost: the cost of operation Assessment (only 11g only available)

size: the operation returns to pre-estimate the amount of data (in bytes) (only 11g only availabl

card: Pre estimated number of rows returned by the operation (only 11g only available)

The next step is to wait for the event:

Event waited on                             Times Waited   Max. Wait  Total Waited
SQL*Net message to client                        702           0.00          0.00
SQL*Net message from client                      702           0.05         17.82
SQL*Net more data to client                      700           0.00          0.01

Times Waited: waiting time takes time

Max Wait: wait a single event maximum waiting time in seconds

Total Waited: wait event for a total of seconds to wait, imprecise

Here you can see the wait event encountered in the implementation, the analysis of these wait events to help you understand what resources are waiting query bottlenecks have to make optimized for the. You can find a brief description of the most common wait events in the appendix Oracle Database Reference manual.


About daviewning

I am an Oracle DBA


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: