Re: session_cached_cursors

In article <366AD648.8777D2B2_at_NOT.com>,   snowden_at_NOT.com wrote:
> There is often a bit of confusion surrounding the mystery of parsing.
> Parse calls in a tkprof report may be the same, but these are soft
> parses as well as hard parses. If you run tkprof and look just below the
> sql stats area, you should see a line referring to 'misses', which
> should be either 1 or 0 in a healthy query. If this is higher, you may
> have a sql problem. Make sure you are using bind variables.
>
> Roger Snowden
> Senior Systems Engineering Specialist
> Oracle Corporation
> NOTrsnowden_at_NOTus.oracle.com <-- to reply, remove the obvious
>
> Doug Cha wrote:
> >
> > I've only been able to find bits and pieces of documentation on
> > the init.ora param SESSION_CACHED_CURSORS. (14-18 of O8 Tuning
> > guide).
> >
> > It says that session cursors should be reused but I'm finding that
> > the parse calls are still the same (sqltrace and v$sqlarea).
> >
> > My guess is that the number of parse calls recorded in both
> > v$sqlarea and from sqltrace does not distinguish between a logical
> > (cached) parse and a normal parse.
> >
> > v$sesstat/v$sysstat values for session cursor cache hits do go
> > up after setting the parameter.
> >
> > Does anyone know for sure what is happening?
>

You should examine V$SQLAREA to see if the number of invalidations and version count are increasing. If the version count for your SQL is always the same, then what you are seeing is the number of "soft parses", ie, the parses resolved from the shared pool. If your version count is increasing, then I suggest increasing the shared pool ASAP.

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Dec 06 1998 - 16:57:04 CST