关于在11g里使用bypass_ujvc报错的问题

shentianyun

请教大神，最近系统升级到11.2.0.3，发现有一OLTP系统大量使用bypass_ujvc做update，11g开始不支持了，有何办法，是否只能改应用呢？望赐教！

Maclean Liu(刘相兵

一个好的问题 的基本要素：一个好的标题、　完整的版本信息、 完整的日志信息、 报错信息


ODM TEST1:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production



SQL> create or replace view v_empdept as
  2  select x.empno, x.ename, x.job, y.deptno, y.dname
  3  from emp x, dept y
  4  where x.deptno = y.deptno
  5  /

View created.

SQL> update v_empdept
  2  set dname = 'AP_TUNNING'
  3  where empno = '7369'
  4  /
set dname = 'AP_TUNNING'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> update /*+ bypass_ujvc */ v_empdept
  2  set dname = 'AP_TUNNING'
  3  where empno = '7369'
  4  /

1 row updated.

SQL> commit;

Commit complete.

ODM TEST2:


SQL> select * from v$version
  2  ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


SQL> update v_empdept
  2  set dname = 'AP_TUNNING'
  3  where empno = '7369'
  4  /
set dname = 'AP_TUNNING'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> update /*+ bypass_ujvc */ v_empdept
  2  set dname = 'AP_TUNNING'
  3  where empno = '7369'
  4  /
set dname = 'AP_TUNNING'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


bypass_ujvc 是内部使用的hint ， 在11.2.0.1以后  bypass_ujvc 被默认忽略

修改 optimizer_features_enable='8.1.5'; 无法  workaround 该问题

SQL> alter session set optimizer_features_enable='8.1.5';

Session altered.

SQL>
SQL> update /*+ bypass_ujvc */ v_empdept
  2      set dname = 'AP_TUNNING'
  3      where empno = '7369'
  4     /
    set dname = 'AP_TUNNING'
        *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


在2010年就有类似的bug 被report 但是Oracle开发部门应当是铁了心不恢复这个 hint了
ODM FINDING:

Bug 9595084: /*+ BYPASS_UJVC */ NO LONGER PREVENTS ORA-01779

Hdr: 9595084 11.2.0.1 RDBMS 11.2.0.1 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: /*+ BYPASS_UJVC */  NO LONGER PREVENTS ORA-1779

PROBLEM:
--------
While on 10.2.0.2 was using /*+ BYPASS_UJVC */ hint in application code to
avoid occurance of Ora-1779 when updating through a view.

Once migration done to 11.2.0.1 the hint no longer is preventing the checks
normally ignored by having the hint set and now Ora-1779 errors occuring.

DIAGNOSTIC ANALYSIS:
--------------------
Testcase was sent.

Tested on:
============
10.2.0.4:  update gets 0 rows updated.

11.1.0.7: update gets 0 rows updated.

11.2.0.1: Gets Ora-1779 error.

SQL> update test_v set c2 = 10;
update test_v set c2 = 10
                 *
ERROR at line 1:
ORA-1779: cannot modify a column which maps to a non key-preserved table

WORKAROUND:
-----------

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
Easily reproduced.

11.2.0.1 test performed on:


TEST CASE:
----------
1)Create the testuser.

Create user DNBI_DBA identified by DNBI_DBA
default tablespace users
temporary tablespace temp;

Grant dba to DNBI_DBA;

2)Connect as that user, create 2 tables, and a view that uses the hint, then
query the view

connect DNBI_DBA/DNBI_DBA

create table test1 (c1 number,c2 number);
create table test2 (c1 number,c2 number);
create or replace view test_v as    select /*+ BYPASS_UJVC */ a.c2    from
test1 a,        (select distinct c1,c2 from test2) b where a.c1 = b.c1    and
  a.c1 = 1
/

update test_v set c2 = 10;

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------
Customer has requested sev 1 on this issue for following reasons.
-the application code used uses this hint throughout the code
-this is preventing 10.2.0.2 to 11.2.0.1 migratiuon scheduled for tomorrow
Cannot proceed with migration untl this is resolved.


DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------
Migration being prevented is scheduled for tomorrow.

This is at severity 1.  A warm-hand off is required and the bug
Should be assigned to the engineer you contact for the warm hand-off.
This may require that you override the BAT assignment.
Failure to perform a warm-hand off may result in a delay in the

shentianyun

多谢大神回复所提供的信息，关于提问的方式以后会多加注意！谢谢！