| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: WARNING: CURSOR_SHARING=FORCE on 8.1.7
It also causes ORA 600's, it was supposed to be fixed in Patch 8..1.7.1.. I
believe.
KK
-----Original Message-----
Sent: Tuesday, August 21, 2001 1:57 PM
To: Multiple recipients of list ORACLE-L
Although it works for me under Oracle 8.1.7.1.4 on Win/2K as shown below, I do have doubts about CURSOR_SHARING=FORCE. When I had experimented couple of months ago, Oracle didn't substitute bind variables for all literals values, and that wasn't going to help us. So we couldn't use this feature.
Jay
ORADB SQL>CREATE TABLE REJ_TEST (
2 ACCESSCONTROLID NUMBER (10) NOT NULL,
3 RESOURCE_NAME VARCHAR2 (10) NOT NULL,
4 ACTION VARCHAR2 (10) NOT NULL, 5 ISALLOWED NUMBER (5) NOT NULL, 6 PARTYID NUMBER (10) NOT NULL, 7 PTYPE NUMBER (3) NOT NULL);
Table created.
ORADB SQL>
ORADB SQL>insert into rej_test
2 values (23, 'ESR', 'upd1', -1, 60, 1);
1 row created.
ORADB SQL>insert into rej_test
2 values (60, 'ESR', 'upd1', -1, 13, 2);
1 row created.
ORADB SQL>
ORADB SQL>analyze table rej_test compute statistics;
Table analyzed.
ORADB SQL> ORADB SQL>REM SELECT 1 ORADB SQL> ORADB SQL>SELECT Resource_Name, Action
RESOURCE_N ACTION
---------- ----------
ESR upd1 ESR upd1
2 rows selected.
ORADB SQL>
ORADB SQL>ALTER SESSION SET CURSOR_SHARING=EXACT;
Session altered.
ORADB SQL> ORADB SQL>REM SELECT 2 ORADB SQL> ORADB SQL>SELECT Resource_Name, Action
RESOURCE_N ACTION
---------- ----------
ESR upd1 ESR upd1
2 rows selected.
ORADB SQL>
ORADB SQL>ALTER SESSION SET CURSOR_SHARING=FORCE;
Session altered.
ORADB SQL> ORADB SQL>REM SELECT 3 ORADB SQL> ORADB SQL>SELECT Resource_Name, Action
RESOURCE_N ACTION
---------- ----------
ESR upd1 ESR upd1
2 rows selected.
ORADB SQL>
-----Original Message-----
Sent: Tuesday, August 21, 2001 1:12 PM
To: Multiple recipients of list ORACLE-L
Eeeek! It just keeps getting worse! Can someone on 8.1.7.x on a NON-HP platform using CURSOR_SHARING=FORCE and CBO at the system level try the following test for me?
DROP TABLE REJ_TEST CASCADE CONSTRAINTS ;
CREATE TABLE REJ_TEST (
ACCESSCONTROLID NUMBER (10) NOT NULL,
RESOURCE_NAME VARCHAR2 (10) NOT NULL,
ACTION VARCHAR2 (10) NOT NULL,
ISALLOWED NUMBER (5) NOT NULL,
PARTYID NUMBER (10) NOT NULL,
PTYPE NUMBER (3) NOT NULL);
insert into rej_test
values (23, 'ESR', 'upd1', -1, 60, 1);
insert into rej_test
values (60, 'ESR', 'upd1', -1, 13, 2);
analyze table rej_test compute statistics;
REM SELECT 1
SELECT Resource_Name, Action
FROM rej_test
WHERE
(PartyID=60 AND IsAllowed=-1 AND PType=1) OR (PartyID=13 AND IsAllowed=-1
AND PType=2);
ALTER SESSION SET CURSOR_SHARING=EXACT;
REM SELECT 2
SELECT Resource_Name, Action
FROM rej_test
WHERE
(PartyID=60 AND IsAllowed=-1 AND PType=1) OR
(PartyID=13 AND IsAllowed=-1 AND PType=2);
ALTER SESSION SET CURSOR_SHARING=FORCE;
REM SELECT 3
SELECT Resource_Name, Action
FROM rej_test
WHERE
(PartyID=60 AND IsAllowed=-1 AND PType=1)
OR
(PartyID=13 AND IsAllowed=-1 AND PType=2);
I've purposely formatted each SELECT a little differently in order to force a hard parse. The problem I'm seeing is that SELECT #1 and #3 *RETURN NO ROWS*! If #3's formatted to be just like #2, it works fine, which leads me to believe the problem's in the parse.
TIA!
Rich Jesse System/Database Administrator Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: Rich.Jesse_at_qtiworld.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Aug 21 2001 - 13:48:07 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). **************************************************************************** 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. **************************************************************************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta INET: Jmehta_at_ctisinc.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Kostyszyn INET: kevin_at_dulcian.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |