首页 文章

当唯一索引出现时,Oracle 10g和11g之间REF CURSOR的行为不同?

提问于
浏览
18

说明

我有一个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表示false,1表示为真)列表示该数据是否已被处理 .

视图仅限于显示尚未处理的行(视图还连接到其他一些表,进行一些内联查询和函数调用等) . 因此,在打开游标时,视图显示一行或多行,然后在游标打开后运行update语句以翻转头表中的标志,发出提交,然后过程完成 .

在10g上,游标打开,它包含行,然后update语句翻转标志并再次运行该过程将不产生任何数据 .

在11g上,游标永远不会包含该行,就好像在更新语句运行之后光标才会打开 .

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

Update 1: 我设法将问题跟踪到一个独特的约束 . 似乎当11g中存在唯一约束时,无论我是否针对实际对象运行真实世界代码或以下简单示例,该问题在100%的时间内都是可再现的 .

Update 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上的输出示例:
开始第一次测试
TEST1
结束第一次测试
开始第二次测试
TEST1
结束第二次测试

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

澄清

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

问题

为什么11g表现不同?除了重新编写代码之外,还有什么我可以做的吗?

3 回答

  • 1

    这似乎是最近发现的一个错误 . Metalink Bug 1045196描述了确切的问题 . 希望补丁即将发布 . 对于那些无法通过Metalink墙的人来说,这里有一些细节:

    错误10425196:PL / SQL返回11.1.0.6 VS 10.2.0.5上的REF游标行为

    类型:缺陷
    严重程度: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建议可能会影响整个实例的性能(或者在某些情况下可能不可用),因此该选项可能不是有些人可以接受 . 无论哪种方式,此时代码更改似乎是唯一已知的解决方法 .

    Method 1 :更改代码以将列包装在函数中,以防止使用此列上的唯一索引 . 在我的情况下,这是可以接受的,因为尽管列是唯一的,但它永远不会包含小写字符 .

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

    Method 2 :更改查询以使用提示阻止使用索引 . 您可能希望NO_INDEX(unique_col1)提示起作用,但事实并非如此 . RULE提示不起作用 . 您可以使用FULL(tbl1)提示,但这可能会比使用 method 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
    

    在我们的例子中,鉴于我们的客户端环境,并且由于它被隔离到单个存储过程,我们将继续使用 code workaround 来防止任何未知的实例范围的副作用影响其他应用程序和用户 .

  • 2

    这确实很奇怪,感谢分享!

    从Oracle 11.1开始,它看起来像Oracle中的行为更改,甚至在metalink上也存在类似问题的确认错误(错误#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
    
  • 7

    来自Metalink(又名Oracle支持)

    状态错误10425196:92 - 已关闭,不是错误

    问题:

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

    事件顺序

    • 调用存储过程传入Ref Cursor

    • 对TableA打开参考光标

    • 从存储过程内部更新TableA中的一些数据

    • COMMIT更新

    • 过程执行结束,将Ref Cursor返回给调用者

    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 = false

相关问题