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/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
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
Received on Mon Nov 19 2007 - 05:32:41 CST
