RE: tkprof interpretation

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 2 Mar 2007 09:36:20 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF9B50D0@WIN02.hotsos.com>

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.
http://www.hotsos.com
Nullius in verba

Hotsos Symposium 2007 / March 4-8 / Dallas Visit www.hotsos.com for curriculum and schedule details...

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Thursday, March 01, 2007 3:58 PM
To: oracle-l
Subject: tkprof interpretation

Hi,

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 questions:

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)
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 rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 2 0.02 0.02 0 0 0 0
Execute 7 0.09 0.08 0 5 0 0
Fetch 2017 0.63 0.75 203 47302 0 15000
------- ------ -------- ---------- ---------- ---------- ----------

total 2026 0.75 0.86 203 47307 0 15000

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

Environment : Oracle 10.2.0.1 on Rhat Linux 4

Thanks
--Harvinder
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 02 2007 - 09:36:20 CST