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

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

提问by wweicker

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).

我有一个 Oracle 存储过程，它在本地开发实例和运行 Oracle 8、9、10 和最近 11 的多个客户端测试和生产实例上运行了 7 年左右。它一直工作到升级到甲骨文 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 上运行）。

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.

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

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.

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

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

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

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.

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

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.

更新 1：我设法将问题追踪到一个独特的约束。似乎当 11g 中存在唯一约束时，无论我是针对实际对象运行真实代码还是以下简单示例，问题都可以 100% 重现。

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.

更新 2：我能够从等式中完全消除视图。我已经更新了简单示例以显示即使直接对表进行查询时也存在问题。

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

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

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

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

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.

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

Question

题

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

为什么 11g 表现不同？除了重新编写代码，我还能做什么？

采纳答案by wweicker

This appears to be a bug discovered fairly recently. Metalink Bug 1045196describes 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 1045196描述了确切的问题。希望补丁尽快发布。对于那些无法越过 Metalink 墙的人，这里有一些细节：

Metalink

金属链接

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

错误 10425196：PL/SQL 返回引用游标在 11.1.0.6 与 10.2.0.5 上的行为不同

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

类型：缺陷
严重性：2 - 服务严重丢失
状态：代码错误创建时间：2010 年 12 月 22 日

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

诊断分析，从原来的提交案例：
- 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 意外行为

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

我可以确认的更多细节：
- 10.2.0.3 Windows 预期行为
- 11.2.0.1 Windows 意外行为

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.

更改OPTIMIZER_FEATURES_ENABLE='10.2.0.4'参数并不能解决问题。因此，它似乎更多地与 11g 数据库引擎中的设计更改有关，而不是优化器调整。

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.

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

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.

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

    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 RULEhint 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.

方法 2：更改您的查询以使用防止索引被使用的提示。您可能希望NO_INDEX(unique_col1)提示起作用，但事实并非如此。该规则提示不起作用。您可以使用FULL(tbl1)提示，但与使用方法 1相比，这可能会减慢您的查询速度。

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

Oracle's Response and Proposed Workaround

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

Oracle support has finally responded with the following Metalink update:

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

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 workaroundto prevent any unknown instance-wide side effects from affecting other applications and users.

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

回答by Edgar Chupit

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.

从 Oracle 11.1 开始，它确实看起来像是 Oracle 中的行为更改，甚至在 metalink 上也存在类似问题的已确认错误（错误＃10425196）。不幸的是，目前没有关于主题材料的 metalink 的太多信息，但我也打开了与 Oracle 的 SR，要求提供更多信息。

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.

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

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.

那么结果应该和 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:

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

Start
TEST1
Finish

PL/SQL procedure successfully completed

回答by junaling

From Metalink (aka Oracle Support)

来自 Metalink（又名 Oracle 支持）

Status bug 10425196 : 92 - Closed, Not a Bug

状态错误 10425196：92 - 已关闭，不是错误

PROBLEM:

问题：

When calling a stored procedure that returns a REF CURSOR, different behavior is seen in 10.2.0.5 and earlier vs 11.1.0.6 and later databases.

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

Sequence Of Events

事件顺序

1. Call stored procedure passing in a Ref Cursor
2. Open Ref Cursor against TableA
3. Update some data inside TableA from inside the stored procedure
4. COMMIT the update
5. Procedure execution ends returning Ref Cursor back to caller

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

10.2.0.5 and Earlier

10.2.0.5 及更早版本

The returned cursor does not see the updated data as it was opened prior to the data being updated. This is the expected behavior.

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

11.1.0.6 and Later

11.1.0.6 及更高版本

The returned cursor sees the updated data and returns the updated data which is different than the 10.2.0.5 and earlier behavior.

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

DIAGNOSTIC ANALYSIS:

诊断分析：

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

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 意外行为

RELATED BUGS:

相关错误：

None found.

没有找到。

If it is necessary, you can revert back to the pre-10.2.0.5 behavior setting the following startup parameter and restart the database.

如果有必要，您可以恢复到 10.2.0.5 之前的行为，设置以下启动参数并重新启动数据库。

_row_cr = false

_row_cr = 假