cusor_sharing

[fraze]

I've got some strange results from an insert query I've been asked to look at and I was hoping someone could shed some light on what Oracle is doing under the covers.

This is 9.2.0.4 on Solaris with cursor_sharing = similar.

The statement checks to see if a row exists in a table and inserts it if it doesn't exist. The statement is failing with ORA-01400: cannot insert NULL into even though the statement is trying to insert literals.

Another issue is the statement is trying to insert strings into number fields so there is some implicit conversion going on.

Here's the weird bit:

1. The statement fails when cursor_sharing=similar but works when cursor_sharing=exact.
2. If run with cursor_sharing=exact followed by a run with cursor_sharing=similar both work.
3. if the shared pool is flushed the next run fails if cursor_sharing=similar.

The issue has been fixed bu using to_number on the string but I cant understand why Oracle is trying to insert NULL's in the first place.

1. cursor_sharing=similar - FAILS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR


SQL> drop table t1;

Table dropped.

SQL> 
SQL> create table T1
  2  (
  3    COL1          number(4) NOT NULL,
  4    COL2          number(4) NOT NULL,
  5    COL3          number(4) NOT NULL
  6  );

Table created.

SQL> 
SQL> 
SQL> INSERT WHEN NVL(n,'0') != '1' THEN
  2  INTO T1(COL1,COL2,COL3)
  3  VALUES
  4  (COL1,COL2, COL3)
  5  SELECT (SELECT '1' FROM T1 WHERE
  6  NVL(COL1,'-1')=NVL('10','-1') AND
  7  NVL(COL2,'-1')=NVL('10','-1') AND
  8  NVL(COL3,'-1')=NVL('5','-1') ) n,
  9  '10' COL1,
 10  '10' COL2,
 11  '5' COL3
 12  FROM dual;
INSERT WHEN NVL(n,'0') != '1' THEN
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BUSFYC"."T1"."COL1")

2. cursor_sharing = exact - SUCCESS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT


SQL> drop table t1;

Table dropped.

SQL> 
SQL> create table T1
  2  (
  3    COL1          number(4) NOT NULL,
  4    COL2          number(4) NOT NULL,
  5    COL3          number(4) NOT NULL
  6  );

Table created.

SQL> 
SQL> 
SQL> INSERT WHEN NVL(n,'0') != '1' THEN
  2  INTO T1(COL1,COL2,COL3)
  3  VALUES
  4  (COL1,COL2, COL3)
  5  SELECT (SELECT '1' FROM T1 WHERE
  6  NVL(COL1,'-1')=NVL('10','-1') AND
  7  NVL(COL2,'-1')=NVL('10','-1') AND
  8  NVL(COL3,'-1')=NVL('5','-1') ) n,
  9  '10' COL1,
 10  '10' COL2,
 11  '5' COL3
 12  FROM dual;

1 row created.

3. cursor_sharing = similar now a successful one has been run - SUCCESS

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      SIMILAR


SQL> drop table t1;

Table dropped.

SQL> 
SQL> create table T1
  2  (
  3    COL1          number(4) NOT NULL,
  4    COL2          number(4) NOT NULL,
  5    COL3          number(4) NOT NULL
  6  );

Table created.

SQL> 
SQL> 
SQL> INSERT WHEN NVL(n,'0') != '1' THEN
  2  INTO T1(COL1,COL2,COL3)
  3  VALUES
  4  (COL1,COL2, COL3)
  5  SELECT (SELECT '1' FROM T1 WHERE
  6  NVL(COL1,'-1')=NVL('10','-1') AND
  7  NVL(COL2,'-1')=NVL('10','-1') AND
  8  NVL(COL3,'-1')=NVL('5','-1') ) n,
  9  '10' COL1,
 10  '10' COL2,
 11  '5' COL3
 12  FROM dual;

1 row created.

4. flush shared pool then cursor_sharing = similar - FAILS

SQL> alter system flush shared_pool;

System altered.

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR


SQL> drop table t1;

Table dropped.

SQL> 
SQL> create table T1
  2  (
  3    COL1          number(4) NOT NULL,
  4    COL2          number(4) NOT NULL,
  5    COL3          number(4) NOT NULL
  6  );

Table created.

SQL> 
SQL> 
SQL> INSERT WHEN NVL(n,'0') != '1' THEN
  2  INTO T1(COL1,COL2,COL3)
  3  VALUES
  4  (COL1,COL2, COL3)
  5  SELECT (SELECT '1' FROM T1 WHERE
  6  NVL(COL1,'-1')=NVL('10','-1') AND
  7  NVL(COL2,'-1')=NVL('10','-1') AND
  8  NVL(COL3,'-1')=NVL('5','-1') ) n,
  9  '10' COL1,
 10  '10' COL2,
 11  '5' COL3
 12  FROM dual;
INSERT WHEN NVL(n,'0') != '1' THEN
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BUSFYC"."T1"."COL1")

[fraze]

Some additional information to confuse things a bit more:

1. If the string literals are replaced with numbers,the insert still fails
2. If to_number is used on the literals then it succeeds

1. Use number literals - FAIL

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR


SQL> drop table t1;

Table dropped.

SQL> 
SQL> create table T1
  2  (
  3    COL1          number(4) NOT NULL,
  4    COL2          number(4) NOT NULL,
  5    COL3          number(4) NOT NULL
  6  );

Table created.

SQL> 
SQL> 
SQL> INSERT WHEN NVL(n,'0') != '1' THEN
  2  INTO T1(COL1,COL2,COL3)
  3  VALUES
  4  (COL1,COL2, COL3)
  5  SELECT (SELECT '1' FROM T1 WHERE
  6  NVL(COL1,'-1')=NVL('10','-1') AND
  7  NVL(COL2,'-1')=NVL('10','-1') AND
  8  NVL(COL3,'-1')=NVL('5','-1') ) n,
  9  10 COL1,
 10  10 COL2,
 11  5 COL3
 12  FROM dual;
INSERT WHEN NVL(n,'0') != '1' THEN
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BUSFYC"."T1"."COL1")

2. Use to_number - SUCCEEDS

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           SIMILAR


SQL> drop table t1;

Table dropped.

SQL> 
SQL> create table T1
  2  (
  3    COL1          number(4) NOT NULL,
  4    COL2          number(4) NOT NULL,
  5    COL3          number(4) NOT NULL
  6  );

Table created.

SQL> 
SQL> 
SQL> INSERT WHEN NVL(n,'0') != '1' THEN
  2  INTO T1(COL1,COL2,COL3)
  3  VALUES
  4  (COL1,COL2, COL3)
  5  SELECT (SELECT '1' FROM T1 WHERE
  6  NVL(COL1,'-1')=NVL('10','-1') AND
  7  NVL(COL2,'-1')=NVL('10','-1') AND
  8  NVL(COL3,'-1')=NVL('5','-1') ) n,
  9  to_number(10) COL1,
 10  to_number(10) COL2,
 11  to_number(5) COL3
 12  FROM dual;

1 row created.

[Bore]

That seems to be a bug.
Definitely statement success or failure should not be influenced by the cursor_sharing parameter.
About the second case with to_number, this is easily to explain, just when using to_number statements are not considered similar and are reparsed, so the optimizer behaves the same as if the cursor_sharing is exact.

So, search the metalink with cursor_sharing, similar, and ora... error and see if any bugs appears
if not, as long as you have a test case, log an SR.
First they will tell u to go for 9.2.0.6 and if it does not disappears, Oracle will probably work on fixing the issue in 11g

[fraze]

I was thinking bug too so I've already got an SR raised with Oracle but have had no reply as yet

Thanks for the insight on to_number.

[Bore]

Well,
push them a bit :-)
If there have passed more than 6 hours without a replay, update the SR and tell them u r waiting for replay. They should start moving ;-)

[abhaysk]

Oracle will probably work on fixing the issue in 11g

not reproducable in 10.2.0.3.0..

[fraze]

Well,
push them a bit :-)
If there have passed more than 6 hours without a replay, update the SR and tell them u r waiting for replay. They should start moving ;-)

Well its been 48 hrs since I raised and nothing yet! Raised as a Sev 4 - good old Oracle Support! No doubt they'll tell us to go to 9.2.0.8 which we cant do because of vendor restrictions.

[abhaysk]

then better live with Exact.. as they will never give you the requried fix..

[PAVB]

Well its been 48 hrs since I raised and nothing yet! Raised as a Sev 4

Sev=4? better for you to take a seat, wait is gonna be long... it's gonna take forever.

[Bore]

Sev 4 - Minor error, no lost of service :-)
Or to translate that, it means you NOTIFIED them about that. You almost told them that you do not expect a fix ;-)