From Jacques.Kilchoer@quest.com Tue, 21 Aug 2001 11:28:34 -0700 From: Jacques Kilchoer Date: Tue, 21 Aug 2001 11:28:34 -0700 Subject: RE: WARNING: CURSOR_SHARING=FORCE on 8.1.7 Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: WARNING: CURSOR_SHARING=FORCE on 8.1.7 > -----Original Message----- > From: Jesse, Rich [mailto:Rich.Jesse@qtiworld.com] > > 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? Here are my results. No rows returned from the third query!!! SQL> select * from v$version ; BANNER ---------------------------------------------------------------- Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE    8.1.7.0.0       Production TNS for 32-bit Windows: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production 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. SQL> SQL> insert into rej_test   2   values (23, 'ESR', 'upd1', -1, 60, 1); 1 row created. SQL> insert into rej_test   2   values (60, 'ESR', 'upd1', -1, 13, 2); 1 row created. SQL> SQL> analyze table rej_test compute statistics; Table analyzed. SQL> SQL> REM SELECT 1 SQL> SQL> SELECT Resource_Name, Action   2  FROM rej_test   3  WHERE   4  (PartyID=60 AND IsAllowed=-1 AND PType=1) OR (PartyID=13  AND IsAllowed=-1   5  AND PType=2); RESOURCE_N ACTION ---------- ---------- ESR        upd1 ESR        upd1 SQL> SQL> ALTER SESSION SET CURSOR_SHARING=EXACT; Session altered. SQL> SQL> REM SELECT 2 SQL> SQL> SELECT Resource_Name, Action   2  FROM rej_test   3  WHERE   4   (PartyID=60 AND IsAllowed=-1 AND PType=1) OR   5   (PartyID=13  AND IsAllowed=-1 AND PType=2); RESOURCE_N ACTION ---------- ---------- ESR        upd1 ESR        upd1 SQL> SQL> ALTER SESSION SET CURSOR_SHARING=FORCE; Session altered. SQL> SQL> REM SELECT 3 SQL> SQL> SELECT Resource_Name, Action   2  FROM rej_test   3  WHERE   4   (PartyID=60 AND IsAllowed=-1 AND PType=1)   5  OR   6  (PartyID=13  AND IsAllowed=-1 AND PType=2); no rows selected