当存在唯一索引时，Oracle 10g 和 11g 之间的 REF CURSOR 行为有何不同？

Question

描述

我有一个 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 上，游标打开，它包含该行，然后更新语句翻转标志，第二次运行该过程将不会产生任何数据。

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

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

11g 上的输出示例：
  开始第一次测试
测试1
  结束第一次测试
  开始第二次测试
  结束第二次测试

说明

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

问题

为什么 11g 的行为不同？除了重新编写代码之外，我还能做些什么吗？

Answer 1

这似乎是最近发现的一个错误。 Metalink Bug 1045196 描述了确切的问题。希望补丁很快就会发布。对于那些无法越过 Metalink 墙的人，这里有一些详细信息：

Metalink

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

Type: Defect
严重性：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 数据库引擎中的设计更改有关，而不是优化器调整。

代码解决方法

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

在发布补丁之前，影响查询的性能可能暂时可以接受，但我相信按照 @Edgar Chupit 建议使用 FLASHBACK 可能会影响整个实例的性能（或者可能在某些实例上不可用），因此该选项可能不可用对于某些人来说可以接受。无论哪种方式，此时代码更改似乎是唯一已知的解决方法。

方法 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

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

Answer 2

这确实是一个奇怪的问题，感谢分享！

从 Oracle 11.1 开始，它确实看起来像是 Oracle 中的行为变化，甚至在 metalink 上也确认了类似问题的错误 (bug#10425196)。不幸的是，目前在Metalink 上没有太多关于该主题的信息，但我也向Oracle 提出了SR，要求提供更多信息。

虽然目前我无法向您提供解释为什么会发生这种情况，以及是否有一个（隐藏）参数可以将此行为反转回 10g 风格，但我想我可以为您提供解决方法。您可以使用 Oracle 闪回查询功能强制 Oracle 按预期时间点检索数据。

如果您按如下方式更改代码：

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

那么结果应该与 10g 中的结果相同。

这是原始测试用例的简化版本：

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

如果您注释掉第 16 行的提交，则输出将是：

Start
TEST1
Finish

PL/SQL procedure successfully completed

Answer 3

来自 Metalink（又名 Oracle 支持）

状态 bug 10425196：92 - 已关闭，不是 Bug

问题：

调用返回 REF CURSOR 的存储过程时，不同的行为
出现在 10.2.0.5 及更早版本与 11.1.0.6 及更高版本数据库中。

事件序列

1. 调用存储过程，传入引用游标
2. 打开针对 TableA 的引用游标
3. 从存储过程内部更新 TableA 内的一些数据
4. 提交更新
5. 过程执行结束，将引用游标返回给调用方

10.2.0.5 及更早版本

返回的游标看不到更新之前打开的数据
正在更新的数据。这是预期的行为。

11.1.0.6及更高版本

返回的游标看到更新的数据并返回更新的数据
与 10.2.0.5 及更早版本的行为不同。

诊断分析：

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.5 之前的行为，设置以下启动参数并重新启动数据库。

_row_cr = 假