RE: Cursor Sharing| Soft Parsing

One thing is for sure .....
I will not be telling any developers about the Cursor_sharing parameter and I will continue to insist that they rewrite their application properly with bind variables. Sometimes these developers piss me off though. They are like lazy kids whose parents always bail them out.

FWIW. Mike

-----Original Message-----
Sent: Thursday, July 25, 2002 7:58 AM
To: Multiple recipients of list ORACLE-L

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: Johnson, Michael 
  INET: Michael.Johnson_at_oln-afmc.af.mil

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