Re: tkprof interpreatation question

On Mar 1, 4:56 pm, harvinde..._at_gmail.com 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. Received on Fri Mar 02 2007 - 21:51:17 CST