Re: Cursor Sharing

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