Re: cursor_sharing

From: Martin Haltmayer <Martin.Haltmayer_at_d2mail.de>
Date: Fri, 09 Aug 2002 11:02:54 +0200
Message-ID: <3D53853E.1085249E@d2mail.de>

Thomas Kyte once posted an example why this gives unexpected behaviour even if not hit by a bug (search result from www.google.com but alas German messages from google):

Groups  

Erweiterte Groups-Suche Einstellungen    

Groups search result 1 for kyte rpad cursor_sharing force group:comp.databases.oracle.server

Von:Thomas Kyte (tkyte_at_us.oracle.com)
Search Result 1

Betrifft:Re: Weird SQLPLUS Behaviour using RPAD Newsgroups:comp.databases.oracle.server
View: Complete Thread (2 articles) | Original Format Datum:2001-06-14 06:52:21 PST

A copy of this was sent to "Stephen Ashmore" <sashmore_at_neonramp.com> (if that email address didn't require changing) On Wed, 13 Jun 2001 16:05:52 -0000, you wrote:

>When we connect to two different databases (8.1.6) from the
>same sqlplus session we see our output truncated on one database and
> not the other. This is consistent with SQLPLUS for unix or Windows.
>We have not set the column,
>
>Any body got any ideas?
>
>DEVDB1> select rpad(username,4),rpad(username,4) from dba_users where rownum
>< 5;
>
>RPAD(USERNAME,4)
>----------------------------------------------------------------------------
>----
>RPAD(USERNAME,4)
>----------------------------------------------------------------------------
>----
>SCOT
>SCOT
>
>ANYO
>ANYO
>
>OUTL
>OUTL
>
>DBSN
>DBSN
>
>
>DEVDB2> select rpad(username,4),rpad(username,4) from dba_users where rownum
>< 5;
>
>RPAD RPAD
>---- ----
>OUTL OUTL
>DBSN DBSN
>IRAD IRAD
>IRAD IRAD
>
>
>
>

DEVDB1 has cursor_sharing set to force so that rpad( username, 4 ) is being seen as rpad( username, :SYS_BV1 ) -- the length is no longer known. This is just one side effect of cursor sharing

tkyte_at_TKYTE816> select rpad( username, 4 ) from all_users where rownum = 1;

RPAD

---

SYS tkyte_at_TKYTE816> alter session set cursor_sharing=force;

Session altered.

tkyte_at_TKYTE816> select rpad( username, 4 ) from all_users where rownum <= 1;

RPAD(USERNAME,4)

---

SYS

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag

Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/ 

Opinions are mine and do not necessarily reflect those of Oracle Corp

 



©2002 Google

HTH.

Martin



Daud wrote:


> 

> Oracle 8.1.7.3.1

> 

> In 8i there is a new parameter (new to me!) cursor_sharing. From the

> doc, setting it to FORCE means forcing oracle to convert all sqls that

> use literal values to use bind variables. This is good especially when

> you have an app that does not use bind variables.

> What's the negative impact of setting it to force? I am a little

> curious because this is not the default setting which one would expect

> it to be if it is really beneficial. Can someone also pls explain what

> extra effort does Oracle need to do when it is set to force and

> whether this extra effort is, in your opinion, worthwhile.

> 

> rgds

> Daud

Received on Fri Aug 09 2002 - 04:02:54 CDT