RE: tkprof interpretation

  • From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
  • To: <Harvinder.Singh@xxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 2 Mar 2007 09:36:20 -0600

Tkprof doesn't tell you the answer to #2, but the information you need
to answer it is in the trace file. Grep for "^PARSE.*mis=[^0]" to find
out how many library cache misses you had during parse calls. Grep for
"^EXEC.*mis=[^0]" to find out how many occurred during execute calls.

All the information you need is in the trace file, including whether the
misses that occurred actually cost you any appreciable response time.
Our Hotsos Profiler reveals all that information in a useful way.

Cary Millsap
Hotsos Enterprises, Ltd.
Nullius in verba
Hotsos Symposium 2007 / March 4-8 / Dallas
Visit for curriculum and schedule details...

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Harvinder Singh
Sent: Thursday, March 01, 2007 3:58 PM
To: oracle-l
Subject: tkprof interpretation


From the following tkprof output it looks like we are doing lot of fetch
calls and also lot of misses during parsing and we have following 2

1) Which parameter we need to set at ODBC connection level to increase
the rows/buffer size for rows per fetch? (I can see the option at system
dsn level but developers need to know the parameter name to specify as
connection property)
2)  What is the interpretation of the following?
Misses in library cache during parse (Misses while looking for plan in
library cache--Hard parse)
Misses in library cache during execute (Misses while about to execute
the plan and found it missing/invalid in library cache)

call     count       cpu    elapsed       disk      query    current
------- ------  -------- ---------- ---------- ---------- ----------
Parse        2      0.02       0.02          0          0          0
Execute      7      0.09       0.08          0          5          0
Fetch     2017      0.63       0.75        203      47302          0
------- ------  -------- ---------- ---------- ---------- ----------
total     2026      0.75       0.86        203      47307          0

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

Environment : Oracle on Rhat Linux 4



Other related posts: