Re: session cursor cache

"Billy" <vslabs_at_onwe.co.za> wrote in message news:1129725721.410131.120570_at_g44g2000cwa.googlegroups.com...
> Brian wrote:
>
>> Is it possible to view the contents of the entire cache?
>
> All cursors are stored in the shared pool. The shared pool can be
> queries using V$SQLAREA and the cursor's address and executions
> obtained from it. The address can be used to find the complete SQL
> statement in V$SQLTEXT. Executions are very useful as that indicates
> just how "hot" the SQL is.
>
> Also useful for determining SQL statements that are not sharable
> (usually because lacking bind variables). I invariable run into these
> time and again when dealing with web reporting application servers.
>
>> The 3 paragraphs in the 10g Performance Tuning Guide does not go into
>> much detail.
>
> Performance is a very relative thing. The guide says:
> "To determine whether the session cursor cache is sufficiently large
> for your instance, you can examine the session statistic session cursor
> cache hits in the V$SYSSTAT view. This statistic counts the number of
> times a parse call found a cursor in the session cursor cache. If this
> statistic is a relatively low percentage of the total parse call count
> for the session, then consider setting SESSION_CACHED_CURSORS to a
> larger value."
>
> Just what is a relatively low percentage? Just what is the actual
> performance gain experienced by the end-user when the percentage is 90%
> as oppose to 60%? Just what is the cost ito resources to gain that
> additional 30%?
>
> The answers to these differ from system to system and application to
> application. Therefore no specific numbers in the manual, and why
> no-one will step up to the plate here and provide such percentages. (as
> he knows he will het slobberknockered ;-)
>
>> I am looking for real life answers from people who tune and monitor the
>> cursor cache.
>
> What you should rather be asking is if that is the correct thing to
> monitor. The #1 rule in performance tuning is to determine the actual
> performance problem.
>
> Thus my reference to twidling knobs. Is the re-parsing of SQL
> statements due to a small cursor session cache the actual problem?
>
>> Sometimes web pages have a very slight hesitation. Sometime they
>> don't. This is what tuning is all about.
>
> Disagree. That "slight hesitation" can be anything.. The web browser
> causing it while trying to cache data from the incoming HTTP stream on
> a badly fragmented drive.. More than the usual number of collission on
> that network segments. Etc. Etc.
>
> It can be -anything-. To make the assumption it must be Oracle and then
> jumping to the conclusion it must be the session cursor cache... does
> not sound sensible to me.
>
>> I am curious why sometimes the cache hit rate fluctuates from 30% to
>> 100%.
>
> Be careful with percentages as they can be very misleading. Simple
> example. Proc A is a Good Proc (tm). It is called very often. It uses
> bind variables. It's cursors are nicely cached, thank you.
>
> Proc B to Z are Crappy Procs (tm). Called less often, but when they do
> the quickly age one another's cursors from the session cache due firing
> off SQLs without bind variables. But Proc A's remained cached due to
> the LRU algorithm.
>
> Proc A's performance is just fine. Despite the variances in percentage
> of the cursor session cache.
>
> Changing the session cache will not result in any real performance
> gains for the Crappy Procs (likely the converse).
>
> Looking at the percentages is pretty meaningless without quantifying
> what they mean. They describe a symptom. Not the problem.
>
>> Possibly I might better understand why all
>> my cursors are not being cached as a set of pooled sessions execute the
>> same code over and over again.
>
> Lack of bind variables will be my first suspect. Look at V$SQLAREA for
> SQLs that are the same, but using literals.
>
>> Asking other skilled professions who are able to offer good advice is
>> hardly ignorant.
>
> Did you miss the smiley at the end of that sentence of mine? Was simply
> trying to convey the point that performance tuning is more than
> changing one or two config settings and observing what the result is.
>
> After all, Heisenberg may have been here. ;-)
>
> --
> Billy
>

Is Billy the new Thomas Kyte ? Billy => have you thought about writing a book ? You are very good in explaining things.

Matthias Received on Wed Oct 19 2005 - 15:48:30 CDT