Using tkprof and dbms_monitor

While I was writing my next post I realized that first I should include some information about how to trace sessions, how to convert traces with tkprof and how to interpreted them.

You can find tkprof and dbms_monitor quite well documented. So I don’t think to explain them with details.

In general you can use dbms_monitor in this way:

--for other session
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,waits => TRUE, binds => FALSE);
--for current session
alter session set tracefile_identifier='MY_TRACE'; -- to find your trace easier
EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => '', serial_num => '',waits => TRUE, binds => FALSE);

and tkprof:

tkprof intrace.trc outtrace.trc waits=yes

For more information how to trace sessions and how to use tkprof, check this link.
Also you may find useful:
QREF: TKPROF Usage - Quick Reference (Doc ID 29012.1)
TKProf Interpretation (9i and above) (Doc ID 760786.1)
How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)
Interpreting Raw SQL_TRACE output (Doc ID 39817.1)

In my next post we will look at the “row source plan stats”, so just to be able to understand them, you have to know the following:

Rows Row Source Operation
------- ---------------------------------------------------
[A] 1 TABLE ACCESS FULL DUAL [B] (cr=3 [C] pr=0 [D] pw=0 [E] time=21 us [F] cost=7 [G] size=7 [H] card=1 [I])

Row count [A]– the row counts output in this section are the actual number of rows returned at each step in the query execution. These actual counts can be compared with the estimated cardinalities (row counts) from an optimizer explain plan. Any differences may indicate a statistical problem that may result in a poor plan choice.
Document 214106.1 Using TKProf to compare actual and predicted row counts
Row Source Operation [B] – Shows the operation executed at this step in the plan.
IO Stats – For each step in the plan, [C] is the consistent reads, [D] is the physical reads and [E] is the writes. These statistics can be useful in identifying steps that read or write a particularly large proportion of the overall data.
Timing – [F] shows the cumulative elapsed time for the step and the steps that preceded it in microseconds (µs: 1/1000000 of a second). This section is very useful when looking for the point in an access path that takes all the time. By looking for the point at where the majority of the time originates it is possible to narrow down a number of problems.
On later releases, row source trace has been enhanced to include some optimizer information. [G] is the estimated cost of the operation used by the optimizer for internal comparison, [H] is the estimated space usage of the operation in bytes and [I] is estimated cardinality (number of rows returned) of that particular operation.



One thought on “Using tkprof and dbms_monitor

Leave a Reply

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

You are commenting using your 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