RE: WARNING: CURSOR_SHARING=FORCE on 8.1.7

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 21 Aug 2001 11:28:34 -0700
Message-ID: <F001.00372E8F.20010821110627@fatcity.com>

> -----Original Message-----
> From: Jesse, Rich [mailto:Rich.Jesse_at_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 Received on Tue Aug 21 2001 - 13:28:34 CDT