当独特的索引存在时,Oracle 10g和11g之间的REF CURSOR有不同的行

我有一个Oracle存储过程，在本地开发实例和运行Oracle 8，然后是9，然后是10，最近11的多个客户端测试和生产实例上运行了7年以上。它一直工作直到升级到Oracle 11g。基本上，该过程打开一个参考游标，更新一个表然后完成。在10g中，光标将包含预期的结果，但在11g光标将为空。升级到11g后，DML或DDL没有改变。我试过的每个10g或11g实例(10.2.0.3，10.2.0.4，11.1.0.7，11.2.0.1 – 所有在Windows上运行)的行为是一致的。

具体的代码要复杂得多，但是在一些现实的概述中解释这个问题：我有一些头表中的数据和一堆将被输出到PDF的子表。标题表有一个布尔值(NUMBER(1)，其中0是假，1是真)，表示该数据是否已被处理。

该视图仅限于显示尚未处理的行(该视图还加入其他表，进行一些内联??查询和函数调用等)。所以当光标打开时，视图显示一行或多行，然后在游标打开后，运行更新语句来翻转头表中的标志，发出提交，然后过程完成。

在10g上，游标打开，它包含行，然后update语句翻转标志，并且第二次运行该过程将不会产生任何数据。

在11g上，游标从不包含该行，就像游标在更新语句运行之后不会打开。

我担心可能会影响其他程序和其他应用程序的11g内容(希望是可以配置的设置)有所改变。我想知道的是有没有人知道为什么两个数据库版本之间的行为是不同的，以及是否可以在没有代码更改的情况下解决问题。

更新1：我设法跟踪问题到一个独特的约束。看起来当11g中唯一的约束出现在100％的时间内，这个问题是可以重现的，不管我是针对实际的对象运行真实世界的代码还是下面的简单的例子。

更新2：我能够从方程式中彻底消除这一观点。我已经更新了简单的例子来显示即使直接查询表时问题也存在。

简单的例子

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;

匿名块演示

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,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;

10g的输出示例是：
开始第一次测试
TEST1
结束第一测试
开始第二次测试
TEST1
第二次测试

11g的输出是什么样的例子：
开始第一次测试
TEST1
结束第一测试
开始第二次测试
第二次测试

澄清

我不能删除COMMIT，因为在现实世界的场景中，程序是从Web应用程序调用的。当前端的数据提供者调用该过程时，它将在断开与数据库的连接时发出隐式COMMIT。所以如果我删除程序中的COMMIT然后是的，匿名块演示将工作，但现实世界的场景不会因为COMMIT仍然会发生。

题

为什么11g行为不同？有什么我可以做的，而不是重写代码？

Metalink知识库

错误10425196：PL / SQL返回参考游标不同于11.1.0.6 VS 10.2.0.5

类型：缺陷
严重程度：2 – 服务严重损失
状态：代码错误
创建日期：2010年12月22日

原始案件提交的诊断分析：
– 10.2.0.4 Windows预期行为
– 10.2.0.5 Solaris预期行为
– 11.1.0.6 Solaris非预期行为
– 11.1.0.7 Windows非预期行为
– 11.2.0.1 Solaris非预期行为
– 11.2.0.2 Solaris非预期行为

更多细节我可以确认：
– 10.2.0.3 Windows预期行为
– 11.2.0.1 Windows非预期行为

额外细节

更改OPTIMIZER_FEATURES_ENABLE = ’10 .2.0.4’参数无法解决问题。因此，它似乎与11g数据库引擎中的设计更改相关，而不是优化器调整。

代码解决方法

这似乎是在查询表时使用索引的结果，而不是更新表和/或提交的行为。使用上面的例子，这里有两种确保查询不使用索引的方法。两者都可能影响查询的性能。

影响查询的性能可能暂时可以接受，直到修补程序被释放，但我相信使用FLASHBACK作为@Edgar Chupit建议可能会影响整个实例的性能(或者可能在某些实例上可能不可用)，以便选项可能不会一些可以接受无论哪种方式，在这个时候，代码变化似乎是唯一已知的解决方法。

方法1：更改代码以将列包装在函数中，以防止使用该列上的唯一索引。在我的情况下，这是可以接受的，因为尽管列是唯一的，但它绝不会包含小写字符。

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

方法2：将查询更改为使用提示，防止使用索引。您可能期望NO_INDEX(unique_col1)提示可以工作，但不会。 RULE提示不行。您可以使用FULL(tbl1)提示，但这可能会比使用方法1减慢查询速度。

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

Oracle的响应和建议的解决方法

Oracle支持终于响应了以下Metalink更新：

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

在进一步的通信之后，听起来好像这不像处理设计决策那样被视为一个错误：

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

在我们的情况下，考虑到我们的客户端环境，由于它被隔离到单个存储过程，我们将继续使用我们的代码解决方法来防止任何未知的实例范围的副作用影响其他应用程序和用户。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!