说明
我有一个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 回答
这似乎是最近发现的一个错误 . Metalink Bug 1045196描述了确切的问题 . 希望补丁即将发布 . 对于那些无法通过Metalink墙的人来说,这里有一些细节:
Metalink
错误10425196:PL / SQL返回11.1.0.6 VS 10.2.0.5上的REF游标行为
类型:缺陷
严重程度:2 - 严重损失服务
状态:代码错误创建时间:2010年12月22日
原始案件提交的诊断分析:
更多细节我可以确认:
其他细节
更改OPTIMIZER_FEATURES_ENABLE = '10 .2.0.4'参数无法解决问题 . 所以它似乎更多地与11g数据库引擎中的设计更改有关,而不是优化器调整 .
代码解决方法
这似乎是使用的结果查询表时的索引,而不是更新表和/或提交的行为 . 使用上面的示例,这里有两种方法可以确保查询不使用索引 . 两者都可能影响查询的性能 .
影响查询的性能可能暂时可以接受,直到发布补丁但我认为使用FLASHBACK作为@Edgar Chupit建议可能会影响整个实例的性能(或者在某些情况下可能不可用),因此该选项可能不是有些人可以接受 . 无论哪种方式,此时代码更改似乎是唯一已知的解决方法 .
Method 1 :更改代码以将列包装在函数中,以防止使用此列上的唯一索引 . 在我的情况下,这是可以接受的,因为尽管列是唯一的,但它永远不会包含小写字符 .
Method 2 :更改查询以使用提示阻止使用索引 . 您可能希望NO_INDEX(unique_col1)提示起作用,但事实并非如此 . RULE提示不起作用 . 您可以使用FULL(tbl1)提示,但这可能会比使用 method 1 更慢 .
Oracle的响应和建议的解决方法
Oracle支持终于响应了以下Metalink更新:
经过一些进一步的通信后,听起来好像这不是一个错误,而是一个设计决策向前推进:
在我们的例子中,鉴于我们的客户端环境,并且由于它被隔离到单个存储过程,我们将继续使用 code workaround 来防止任何未知的实例范围的副作用影响其他应用程序和用户 .
这确实很奇怪,感谢分享!
从Oracle 11.1开始,它看起来像Oracle中的行为更改,甚至在metalink上也存在类似问题的确认错误(错误#10425196) . 不幸的是,目前关于主题的metalink上没有太多信息,但我还与Oracle一起开设了SR,要求提供更多信息 .
虽然目前我无法解释为什么会发生这种情况,并且如果有一个(隐藏的)参数可以将此行为转回10g样式,我想我可以为您提供解决方法 . 您可以使用Oracle闪回查询功能强制Oracle根据预期的时间点检索数据 .
如果您更改代码如下:
然后结果应该与10g相同 .
这是原始测试用例的简化版本:
如果你在第16行注释掉提交,那么输出将是:
来自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