Re: extremely high number of executions

I haven't read all the replies, but there are two things that you to investigate:

1. The number of executes will not be the reason for the for the library cache contention. I have a feeling that you have a same number of parse count(total) for them. Now they are responsible for a lot of the libarary cache contention. Each execute will require a pin and that needs to be allocated, but that should be fairly quick.
2. This is also a case where the SQL statement is optimized (as you noticed), but the number of executes needs to be tuned. I can't tell what the statement is needed for but some of that data should probably be cached for a session at the client side.

Check the parse calls and see if you can't cache it on the client side.

Anjo.

Johnson Poovathummoottil wrote:

>Hi All,
>
>We have an application which executes one sql
>statement more than 10 million times a day. Everything
>is good about the sql, well tuned, uses indexes, parse
>only once, etc. The number of concurrent users in this
>database seems to around 60, but we see an average
>1500 executions/sec.
>
>We questioned the developers about the sql as we had
>seen 80% to 95% latch sleeps on library cache
>constantly. They seem to be hitting the database every
>time a page is refreshed instead of storing the
>retrieved data some where for later use.
>
>The developers are of the opinion that cookies and
>session variables are considered "the much
>detested and reviled Satan and Lucifer of all
>"stateful" web apps".
>
>Any comments/opinion?
>
>__________________________________________________
>Do You Yahoo!?
>Yahoo! Health - Feel better, live better
>http://health.yahoo.com
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).