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
Received on Tue Apr 24 2001 - 16:25:02 CDT
