Different behavior for REF CURSOR between Oracle 10g and 11g when unique index present?

Question

Description

I have an Oracle stored procedure that has been running for 7 or so years both locally on development instances and on multiple client test and production instances running Oracle 8, then 9, then 10, and recently 11. It has worked consistently until the upgrade to Oracle 11g. Basically, the procedure opens a reference cursor, updates a table then completes. In 10g the cursor will contain the expected results but in 11g the cursor will be empty. No DML or DDL changed after the upgrade to 11g. This behavior is consistent on every 10g or 11g instance I've tried (10.2.0.3, 10.2.0.4, 11.1.0.7, 11.2.0.1 - all running on Windows).

The specific code is much more complicated but to explain the issue in somewhat realistic overview: I have some data in a header table and a bunch of child tables that will be output to PDF. The header table has a boolean (NUMBER(1) where 0 is false and 1 is true) column indicating whether that data has been processed yet.

The view is limited to only show rows in that have not been processed (the view also joins on some other tables, makes some inline queries and function calls, etc). So at the time when the cursor is opened, the view shows one or more rows, then after the cursor is opened an update statement runs to flip the flag in the header table, a commit is issued, then the procedure completes.

On 10g, the cursor opens, it contains the row, then the update statement flips the flag and running the procedure a second time would yield no data.

On 11g, the cursor never contains the row, it's as if the cursor does not open until after the update statement runs.

I'm concerned that something may have changed in 11g (hopefully a setting that can be configured) that might affect other procedures and other applications. What I'd like to know is whether anyone knows why the behavior is different between the two database versions and whether the issue can be resolved without code changes.

Update 1: I managed to track the issue down to a unique constraint. It seems that when the unique constraint is present in 11g the issue is reproducible 100% of the time regardless of whether I'm running the real world code against the actual objects or the following simple example.

Update 2: I was able to completely eliminate the view from the equation. I have updated the simple example to show the problem exists even when querying directly against the table.

Simple Example

CREATE TABLE tbl1
(
  col1  VARCHAR2(10),
  col2  NUMBER(1)
);

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

/* View is no longer required to demonstrate the problem
CREATE OR REPLACE VIEW vw1 (col1, col2) 
AS 
SELECT col1, col2 
  FROM tbl1 
 WHERE col2 = 0;
*/

CREATE OR REPLACE PACKAGE pkg1
AS
   TYPE refWEB_CURSOR IS REF CURSOR;

   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR);

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR)
   IS
   BEGIN

      OPEN crs FOR
        SELECT col1
          FROM tbl1
         WHERE col1 = 'TEST1'
           AND col2 = 0;

      UPDATE tbl1
         SET col2 = 1
       WHERE col1 = 'TEST1';

      COMMIT;

   END proc1;

END pkg1;

Anonymous Block Demo

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin first test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end first test');

END; 

/* After creating this index, the problem is seen */
CREATE UNIQUE INDEX unique_col1 ON tbl1 (col1);

/* Reset data to initial values */
TRUNCATE TABLE tbl1;

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin second test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end second test');

END; 

Example of what the output on 10g would be:
  begin first test
  TEST1
  end first test
  begin second test
  TEST1
  end second test

Example of what the output on 11g would be:
  begin first test
  TEST1
  end first test
  begin second test
  end second test

Clarification

I can't remove the COMMIT because in the real world scenario the procedure is called from a web application. When the data provider on the front end calls the procedure it will issue an implicit COMMIT when disconnecting from the database anyways. So if I remove the COMMIT in the procedure then yes, the anonymous block demo would work but the real world scenario would not because the COMMIT would still happen.

Question

Why is 11g behaving differently? Is there anything I can do other than re-write the code?

Answer 1

This appears to be a bug discovered fairly recently. Metalink Bug 1045196 describes the exact problem. Hopefully a patch will be released soon. For those of you who can't get past the Metalink wall here are a few details:

Metalink

Bug 10425196: PL/SQL RETURNING REF CURSOR ACTS DIFFERENTLY ON 11.1.0.6 VS 10.2.0.5

Type: Defect
Severity: 2 - Severe Loss of Service
Status: Code Bug Created: 22-Dec-2010

DIAGNOSTIC ANALYSIS from original case submission:
- 10.2.0.4 Windows Expected Behavior
- 10.2.0.5 Solaris Expected Behavior
- 11.1.0.6 Solaris Un-Expected Behavior
- 11.1.0.7 Windows Un-Expected Behavior
- 11.2.0.1 Solaris Un-Expected Behavior
- 11.2.0.2 Solaris Un-Expected Behavior

FURTHER DETAILS I can confirm:
- 10.2.0.3 Windows Expected Behavior
- 11.2.0.1 Windows Un-Expected Behavior

Additional Details

Changing the OPTIMIZER_FEATURES_ENABLE='10.2.0.4' parameter does not resolve the problem. So it seems to be related more to a design change in the 11g database engine rather than an optimizer tweak.

Code Workaround

This appears to be a result of the use of the index when querying the table and not the act of updating the table and/or committing. Using my example above, here are two ways to ensure the query does not use the index. Both may affect the performance of the query.

Affecting the performance of the query might be temporarily acceptable until a patch is released but I believe that using FLASHBACK as @Edgar Chupit suggested could affect the performance of the entire instance (or may not be available on some instances) so that option may not be acceptable for some. Either way, at this point in time code changes appear to be the only known workaround.

Method 1: Change your code to wrap the column in a function to prevent the unique index on this one column from being used. In my case this is acceptable because although the column is unique it will never contain lower case characters.

    SELECT col1
      FROM tbl1
     WHERE UPPER(col1) = 'TEST1'
       AND col2 = 0;

Method 2: Change your query to use a hint preventing the index from being used. You might expect the NO_INDEX(unique_col1) hint to work, but it does not. The RULE hint does not work. You can use the FULL(tbl1) hint but it's likely that this may slow down your query more than using method 1.

    SELECT /*+ FULL(tbl1) */ col1
      FROM tbl1
     WHERE col1 = 'TEST1'
       AND col2 = 0;

Oracle's Response and Proposed Workaround

Oracle support has finally responded with the following Metalink update:

Oracle Support - July 20, 2011 5:51:19 AM GMT-07:00 [ODM Proposed Solution(s)]
Development has reported this will be a significant issue to fix and 
has suggested that the following workaround be applied:

edit init.ora/spfile with the following undocumented parameter:

"_row_cr" = false

Oracle Support - July 20, 2011 5:49:20 AM GMT-07:00 [ODM Cause Justification]
Development has determined this to be a defect

Oracle Support - July 20, 2011 5:48:27 AM GMT-07:00 [ODM Cause Determination]
Cause has been traced to a row source cursor optimization

Oracle Support - July 20, 2011 5:47:27 AM GMT-07:00 [ODM Issue Verification]
Development has confirmed this to be an issue in 11.2.0.1

After some further correspondence it sounds as though this isn't being treated as a bug so much as a design decision moving forward:

Oracle Support - July 21, 2011 5:58:07 AM GMT-07:00 [ODM Proposed Solution Justif]
From 10.2.0.5 onward (which includes 11.2.0.2) we have an optimization called
ROW CR it is only applicable to queries which use an unique index to
determine the row in the table.

A brief overview of this optimization is that we try to avoid rollbacks while
constructing a CR block if the present block has no uncommitted changes.

So the difference seen in 11.2.0.2 is because of this optimization. The
suggested workaround is to turn off of this optimization so that things will
work exactly as they used to work in 10.2.0.4

In our case, given our client environments and since it is isolated to a single stored procedure we will continue to use our code workaround to prevent any unknown instance-wide side effects from affecting other applications and users.

Answer 2

This is indeed strange issue, thanks for sharing!

It really looks like a behavior change in Oracle starting with Oracle 11.1 and there is even confirmed bug with similar issue on metalink (bug#10425196). Unfortunately at the moment there is no much information available on metalink on subject mater, but I've also opened SR with Oracle asking to provide more information.

While at the moment I can not provide you an explanation why it happens and if there is a (hidden) parameter that can reverse this behavior back to 10g style, I think I can provide you with workaround. You can use Oracle flashback query functionality to force Oracle to retrieve data as to expected point in time.

If you change your code as follows:

OPEN crs FOR 
  SELECT col1
>>> FROM vw1 as of scn dbms_flashback.get_system_change_number
   WHERE col1 = 'TEST1';

then result should be the same as in 10g.

And this is simplified version of original test case:

SQL> drop table tbl1;
Table dropped
SQL> create table tbl1(col1 varchar2(10), col2 number);
Table created
SQL> create unique index tbl1_idx on tbl1(col1);
Index created
SQL> insert into tbl1(col1,col2) values('TEST1',0);
1 row inserted
SQL> DECLARE
  2    cursor web_cursor is
  3          SELECT col1
  4            FROM tbl1
  5           WHERE col2 = 0 and col1 = 'TEST1';
  6  
  7    rec1  web_cursor%rowtype;
  8  BEGIN
  9    OPEN web_cursor;
 10  
 11    UPDATE tbl1
 12       SET col2 = 1
 13     WHERE col1 = 'TEST1';
 14  
 15    -- different result depending on commit!
 16    commit;
 17  
 18     DBMS_OUTPUT.PUT_LINE('Start');
 19     LOOP
 20        FETCH web_cursor
 21         INTO rec1;
 22  
 23        EXIT WHEN web_cursor%NOTFOUND;
 24  
 25        DBMS_OUTPUT.PUT_LINE(rec1.col1);
 26     END LOOP;
 27     DBMS_OUTPUT.PUT_LINE('Finish');
 28  END;
 29  /

Start
Finish

PL/SQL procedure successfully completed

If you comment out commit on line 16 than the output will be:

Start
TEST1
Finish

PL/SQL procedure successfully completed