Re: session cursor cache

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