RE: CURSOR_SHARING=FORCE

<SPAN

class=160342320-26042001>We are also experimenting with CURSOR_SHARING to reduce excessive parsing in the application, and made few observations. It appears that Oracle doesn't replace literals with system generated bind variables if SQL statement has both literals and bind variables, as shown here:
 
SELECT RV_VALUE FROM
REF_CODES
WHERE RV_DOMAIN = 'YESNO' AND RV_ABBREVIATION =
RTRIM(:b1)
 
<SPAN

class=160342320-26042001>Jay

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Babette Turner-Underwood   [mailto:babattt_at_home.com]Sent: Tuesday, April 24, 2001 5:38   PMTo: Multiple recipients of list ORACLE-LSubject:   CURSOR_SHARING=FORCE
  We have noticed an interesting side "effect" of using   CURSOR_SHARING=force.When using SQL (simple INSERT, UPDATE, SELECT...),   and you check v$SQLAREAyou see that yes, Oracle indeed replaced hard-coded   values with bindvariablesTRY: SELECT DEPTNO, LOC from DEPT where   LOC='Boston';select sql_text from v$sqlarea where sql_text like 'SELECT   DEPTNO';BUT this does not work for parameters to procedures or   functions.TRY:create procedure upd_dept ( in_deptno number, in_loc   varchar2) begin   update dept set loc =   in_loc;end;exec upd_dept ( 20, 'BOSTON');select sql_text from   v$sqlarea where sql_text like '%upd_dept%';Thus we need to change   calling our table APIs from :upd_dept( 20, 'BOSTON');todefine   my_deptno := 20;define my_location := 'BOSTON';upd_dept( :my_deptno,   :my_location);To use bind variables. Thus making extensive use of   table APIs will havemultiple copiesof SQL in shared pool UNLESS done   this way and CURSOR_SHARING has no effect.According to Oracle, it is how   it is supposed to work, but we were notexpecting the   behaviour.Just another one of those pleasant surprises from Oracle   :-)Babette<A
  href="mailto:babette_at_pythian.com">babette_at_pythian.com

• This electronic message contains information from CTIS, Inc., which

may be company sensitive, proprietary, privileged or otherwise protected

from disclosure. The information is intended to be used solely by the

recipients named above. If you are not an intended recipient, be aware

that any review, disclosure, copying, distribution or use of this

transmission or its contents is prohibited. If you have received this

transmission in error, please notify us immediately at MIS_at_ctisinc.com.