Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cursor Sharing| Soft Parsing

RE: Cursor Sharing| Soft Parsing

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 25 Jul 2002 21:03:22 -0800
Message-ID: <F001.004A3059.20020725210322@fatcity.com>


I could only guess. But if I remember correctly, that's its documented behavior.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas

-----Original Message-----
Jared.Still_at_radisys.com
Sent: Thursday, July 25, 2002 8:53 PM
To: Multiple recipients of list ORACLE-L

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

-- 
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).
Received on Fri Jul 26 2002 - 00:03:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US