From Jmehta@ctisinc.com Thu, 26 Apr 2001 19:55:23 -0700
From: Jay Mehta <Jmehta@ctisinc.com>
Date: Thu, 26 Apr 2001 19:55:23 -0700
Subject: RE: CURSOR_SHARING=FORCE
Message-ID: <F001.002F3B0B.20010426192527@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain



<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@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@pythian.com">babette@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@ctisinc.com. 

****************************************************************************