Re: tkprof interpreatation question

On Mar 1, 4:56 pm, harvinde...@xxxxxxxxx wrote:
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:

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

Thanks
--Harvinder

1. If you are using Visual Basic and using ADO, your can set the rows
to fetch as follows, after the recordset (snpData) has been opened:
snpData.CacheSize = 100

2. I don't use TKPROF much, it is much more interesting to look at raw
trace data:
PARSE #18:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2003405941
EXEC #18:c=0,e=199,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2003554840
FETCH
#18:c=0,e=104,p=0,cr=9,cu=0,mis=0,r=1,dep=0,og=1,tim=2003555219

"Misses in library cache during parse" - this will be indicated by
mis=# on a line in the trace file that begins with PARSE. If this is
greater than 0, it means that the SQL statement was not found in the
shared pool, and Oracle was forced to perform a hard parse - to
determine the optimal execution plan. If I recall correctly, this
number may be greater than 0 when a soft parse is performed also (SQL
statement is already in the shared pool), or if the client explicitly
closes a cursor and then reopened a cursor with the same SQL statement
(this is where the SESSION_CACHE_CURSOR parameter helps).

"Misses in library cache during execute" - this will be indicated by
mis=# on a line in the trace file that begins with EXEC. This may
indicate that the shared pool is so small that the SQL statement ages
out of the shared pool between executions of the SQL statement. In
other words:
Open the recordset
Pass in the bind variables
Execute
(do something else for a couple seconds)
Pass in the next set of bind variables
Execute - SQL statement was flushed from the shared pool and had to
be hard parsed again
(do something else for a couple seconds)
Pass in the next set of bind variables
Execute - SQL statement was flushed from the shared pool and had to
be hard parsed again
...
I don't believe that the above is the case. Instead, I believe that
it has to do with the CURSOR_SHARING=SIMILAR setting that you
mentioned in another post. A paraphrase from "Cost-Based Oracle
Fundamentals" by Jonathan Lewis "CURSOR_SHARING=SIMILAR is potentially
dangerous as it can cause additional parsing, potentially on every
change of bind variable values. A new child cursor will be added to V
$SQL when this happens."

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

.

Relevant Pages

  • Re: meaning of "query" column
    ... Misses in library cache during parse: ... Misses in library cache during execute: ... QUERY Total number of buffers retrieved in consistent mode for all ...
    (comp.databases.oracle.server)
  • Re: meaning of "query" column
    ... Misses in library cache during parse: ... Misses in library cache during execute: ... Total number of buffers retrieved in consistent mode for all ...
    (comp.databases.oracle.server)
  • Re: Excessive parsing of queries involving views
    ... and exists (select connection from perspective where connection = ... If a single query does indeed cause 580 parse ... By processing the SQL statement that accessed the view, ... Execute the SELECT that accesses the view, ...
    (comp.databases.oracle.server)
  • Re: Excessive parsing of queries involving views
    ... and exists (select connection from perspective where connection = ... If a single query does indeed cause 580 parse ... By processing the SQL statement that accessed the view, ... Execute the SELECT that accesses the view, ...
    (comp.databases.oracle.server)
  • meaning of "query" column
    ... Misses in library cache during parse: ... Misses in library cache during execute: ... Parsing user id: 257 ...
    (comp.databases.oracle.server)