Re: parse to execute ratio too high

On 19 Nov, 10:25, "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/http://jonathanlewis.wordpress.com/2007/07/03/parse-calls/
>
> --
> 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

Hi Jonathan,
I've following items:
Parse time CPU 25
Parse count (total) 1529263
Parse count (Hard) 6219
Session cursor cache hit 981646

The ratio it may be a totally misleading indicator sometimes. I don't see anything wrong in this way.
There are more soft parse than hard and the session cursor seems OK. My bottleneck problems maybe don't depend on this ratio, now it's 70% about.
Regards,
Alberto Received on Mon Nov 19 2007 - 10:34:35 CST