Re: session_cached_cursors

There is a very simple answer to this. Most applications execute the following code frequently (psuedo code):

while (some cond) {

    parse SQL statement
    execute
    close SQL
}

What people should do is the following:

parse SQL
while (some cond) {

    execute
}
close SQL

To help applications that are coded following example 1, you can set session_cached_cursors. The close doesn't really happen. The cursor is still cached, so the next parse will find it quickly.

Anjo.

mgogala_at_rocketmail.com wrote:

> 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.

>

>


> -----------== Posted via Deja News, The Discussion Network ==----------


> http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own