Re: parse to execute ratio too high

"Alberto Frosi" <alberto.frosi_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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html