Re: parse to execute ratio too high

On Nov 19, 6:32 am, Alberto Frosi <alberto.fr..._at_gmail.com> wrote:
> On Nov 19, 10:25 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
> wrote:
>
>
>
> > "Alberto Frosi" <alberto.fr..._at_gmail.com> wrote in message
>
> >news:a5ff88fb-aabf-4c6a-ace4-8f9cc5d4f9ea_at_p69g2000hsa.googlegroups.com...
>
> > > Hi all,
> > > I know the the question isn't inusual, but i 've a problem with parse
> > > to execute ratio.
> > > In my DB 92070 foran application i've applied this parameter:
> > > cursor_sharing=force
> > > session_cached_cursor=2000 (before it was setting to 0)
> > > My SGA is setting so 700 Mb with Shared pool = 160MB. In PGA i've
> > > 149MB but only 89 are used.
> > > My parse to execute ratio it's 62% and it's very high value the
> > > response times sometimes aren't good.
> > > My application works with bind varibles because if i run this SQL
> > > statement:
> > > select parse_calls, execution, hash_value, sql_text from v$sqlarea the
> > > result for example it's 'select a,b,c from x where a=:B1' but more sql
> > > statement it's
> > > parsed 2344 for 2344 executions.It's very strange. Only few statements
> > > are parsed 1 and execution more.
> > > Thanks a lot in advance for your help.
> > > Regards.
>
> > > Alberto
>
> > The ratio may be a totally misleading indicator.
>
> > If in doubt, the first step is to look at the underlying figures;
> > in this case check
>
> > parse count (total)
> > parse count (hard)
> > session cursor cache hits
> > execute count
>
> > If most of your parse count (total) with the
> > parse count (hard) eliminated is coming from
> > session cursor cache hits, then your problem
> > may simply be the concurrency impact of a
> > large number of executes - i.e. things start to
> > slow down as more people start to work on
> > the system.
>
> > Check, also, the CPU time spent on parsing compared
> > to the total CPU used - this helps give you an idea of
> > how much impact your parsing has on total response time.
>
> > Further reading:http://jonathanlewis.wordpress.com/2006/12/27/analysing-statspack-2/h...
>
> > --
> > Regards
>
> > Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> > Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> > The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Thanks a lot Jonathan for your quickly reply and for your advise.
> I check parse count (total), parse count (hard), session cursor cache
> hits, execute count.
> I reply you soon.
> Regards
> Alberto

I know this doesn't answer your question, but we experienced this about two years ago, and found that our connection manager software was not caching PreparedStatement handles. We had set session cached cursors to 4000, and also saw a 1 to 1 ratio between parses and executes. As Jonathan suggested, we also saw very high hit rate on the session cursor cache (95% of total parses were session cursor cache hits).

However, it drove us bonkers from a performance measurement standpoint (as well as Grid, ironically), so we swapped out the connection manager software to use the OracleDataSource class, and the "problem" went way :).

Regards,

Steve Received on Mon Nov 19 2007 - 09:21:29 CST