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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
