RE: CURSOR_SHARING=FORCE

[Jay Mehta]

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)   Jay -----Original Message----- From: Babette Turner-Underwood [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 24, 2001 5:38 PM To: Multiple recipients of list ORACLE-L Subject: 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$SQLAREA you see that yes, Oracle indeed replaced hard-coded values with bind variables TRY: 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'); to define 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 have multiple copies of 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 not expecting the behaviour. Just another one of those pleasant surprises from Oracle :-) Babette [EMAIL PROTECTED] **************************************************************************** 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 [EMAIL PROTECTED] ****************************************************************************