"RTM" :-)
Performance Guide 9.2
"Caching Session Cursors
(blah blah blah)
Oracle checks the library cache to determine whether
more than three parse requests have been issued on a
given statement.
(more blah blah blah)"
hth
connor
- Jared.Still_at_radisys.com wrote: > I wasn't aware
of it requiring three calls before
> being useful.
>
> Why is that?
>
> Jared
>
>
>
>
>
> "Cary Millsap" <cary.millsap_at_hotsos.com>
> Sent by: root_at_fatcity.com
> 07/25/2002 07:58 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: Cursor Sharing| Soft
> Parsing
>
>
> Well, three times, right? I think it takes three
> parse calls before
> session_cached_cursors begins to help. But 3 is
> still O(1). Once per
> call is O(#executions).
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic, Jul 23-25 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart
> Denmark
> - 2003 Hotsos Symposium on OracleR System
> Performance, Feb 9-12 Dallas
>
>
>
> -----Original Message-----
> Still
> Sent: Thursday, July 25, 2002 2:38 AM
> To: Multiple recipients of list ORACLE-L
>
> On Wednesday 24 July 2002 22:08, MacGregor, Ian A.
> wrote:
> > Please define soft parsing. Oracle needs to check
> that the user
> > submitting a SQL statement has permissions to run
> it. It has to do
> this
> > every time a statement is run, bind variables or
> not.
>
> No, code that uses bind variables need only parse
> SQL statements
> once if session_cached_cursors is set. Further
> executions of the same
> SQL don't require a hard or soft parse.
>
> Jared
>
> > When "cursor-sharing" converts a statement to use
> bind variables it
> would
> > save on hard parsing, if a match were found the
> pool; also, it could
> lessen
> > the number of statements present in the pool.
> >
> > Ian MacGregor
> > Stanford Linear Accelerator Center
> > ian_at_SLAC.Stanford.edu
> >
> > -----Original Message-----
> > Sent: Wednesday, July 24, 2002 9:23 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Mike, Kirti,
> >
> > Try page 441
> >
> > CURSOR_SHARING=FORCE does improve badly written
> applications that use
> lots
> > of literals.
> > However coding should be done using bind variables
> in almost all
> occasions.
> >
> > CURSOR_SHARING=FORCE reduces the hard parsing.
> >
> > What CURSOR_SHARING=FORCE does is rewrites all
> queries to use bind
> > variables before parsing.
> >
> > eg. select ename from emp where empno = 10;
> > rewritten as
> > select ename from emp where empno =:SYS_B_0
> > or in 8.1.6 , 8.1.7
> > select name from emp where empno =:"SYS_B_0"
> >
> > So it substitutes the literal with bind variables
> but incurs the cost
> of
> > soft parsing the statement.
> > Soft Parsing too frequently limits the scalability
> of applications and
> > sacrifices optimal performance which could have
> been achieved in the
> first
> > place if written using bind variables.
> >
> > Parse once and execute as many times as we like.
> >
> > Also check out Bjorn's paper on bind variables and
> cursor sharing at
> >
>
http://technet.oracle.com/deploy/performance/pdf/cursor.pdf
> >
> > So CURSOR sharing is not the "silver bullet" as
> one may expect.
> >
> > Regards
> > Suhen
> >
> > On Thu, 25 Jul 2002 10:23, you wrote:
> > > Mike,
> > > What is the version of the database? Some
> versions of 8.1.7 had a
> few
> > > bugs when this parameter was set to FORCE. I
> suggest searching
> Metalink.
> > > But it does work as advertised in later
> releases. I would also
> recommend
> > > reviewing Tom Kytes' book to read about his
> views in using this
> parameter
> > > at the instance level (my boss is reading my
> copy, so I can't give
> you
> > > page #s).
> > >
> > > - Kirti
> > >
> > > -----Original Message-----
> > > Sent: Wednesday, July 24, 2002 6:08 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Has anyone set Cursor Sharing to Force ?
> > > I have a new system that we have to support
> > > and there is alot literals filling up the
> > > pool. I have never changed this parameter
> > > from the default as many seemed to think the
> > > jury was still out on it. However, due to
> > > my situation, I figured I would try it out.
> > > If anyone has any experience with this one
> > > I would be curious to know what happened.
> > >
> > > Mike
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Suhen Pather
> > INET: Suhen.Pather_at_strandbags.com.au
> >
> > 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: ListGuru_at_fatcity.com (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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> 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: ListGuru_at_fatcity.com (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).
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> 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: ListGuru_at_fatcity.com (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).
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author:
> INET: Jared.Still_at_radisys.com
>
> 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: ListGuru_at_fatcity.com (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).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Fri Jul 26 2002 - 06:33:18 CDT