Re: tkprof interpreatation question

On Mar 1, 1: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?

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b15658/app_odbc.htm#CIHGAAHJ and http://download-west.oracle.com/docs/cd/B19306_01/server.102/b15658/app_odbc.htm#i1093897 and scroll down to fetch buffer size.

> 2) What is the interpretation of the following:
> Misses in library cache during parse (Misses while looking for plan in
> library cache--Hard parse)

This is something you want to avoid. The basic way to avoid it is to use bind variables, so code that is sent through your odbc is identical over many repetitions. Many code generators wind up sending statements identical except for literal equivalencies, which would be a bad thing. This is very well explained in the Tom Kyte books Daniel mentioned, as well as on asktom.oracle.com. CURSOR_SHARING=SIMILAR is kind of half-baked for people stuck with vendor code, you are better off making the code correct.

> Misses in library cache during execute (Misses while about to execute
> the plan and found it missing/invalid in library cache)
>

You can improve the efficiency of your code by reusing cursors to reduce soft parses.

Review the Oracle Concepts manual library cache concepts, where these things are explained. You can also search the docs at tahiti.oracle.com to find out a lot about specific initialization parameters.

Different RDBMS engines work differently, so you can't expect to use identical code in identical ways and have it work well everywhere.

jg

--
@home.com is bogus.
"If you don't mind the government listening to your phone calls, you
aren't having nearly enough phone sex." - Ralph Garman