使用dblink导致的/*+ OPAQUE_TRANSFORM */

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:使用dblink导致的/*+ OPAQUE_TRANSFORM */

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

数据库版本

--目标端
SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------

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

--源端
SQL> select * from v$version;

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

目标端创建dblink

SQL> create database link dblink_xff connect to test identified by
  2  test using 'ip/mcrm';

数据库链接已创建。

dblink查询操作测试

--目标端
SQL> select count(*) from t_xifenfei@dblink_xff;

  COUNT(*)
----------
     50645

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
-------------------------------------------------------------------
SELECT COUNT(*) FROM "T_XIFENFEI" "A1"
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

dblink创建空表测试

--目标端
SQL> create table  chf.t_xifenfei as select * from t_xifenfei@dblink_xff where 1=0;

表已创建。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
----------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P

dblink创建表插入数据

--目标端
SQL> create table  chf.t_xifenfei_new as select * from t_xifenfei@dblink_xff;

表已创建。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P
SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE

CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",

"SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

dblink insert select插入数据测试

--目标端
SQL> insert into chf.t_xifenfei
  2  select * from t_xifenfei@dblink_xff;

已创建 50645 行。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' 
and sql_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P
SELECT /*+ OPAQUE_TRANSFORM */ "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID

","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS",

"TEMPORARY","GENERATED","SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

除掉OPAQUE_TRANSFORM 提示

--目标端
SQL> alter session set events '22825 trace name context forever, level 1' ;

会话已更改。

SQL> insert into chf.t_xifenfei
  2  select * from t_xifenfei@dblink_xff;

已创建 50645 行。

--源端
SQL> select sql_text from v$sql where lower(sql_text) like '%t_xifenfei%' and sq
l_text not like '%lower(%' and sql_text not like '%OPT_DYN_SAMP%';

SQL_TEXT
--------------------------------------------------------------------------------

SELECT /*+ FULL(P) +*/ * FROM "T_XIFENFEI" P
SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJE

CT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED",

"SECONDARY" FROM "T_XIFENFEI" "T_XIFENFEI"

通过dblink的相关实验可以得出,在 insert-as-remote-select的时候,源端库上会出现/*+ OPAQUE_TRANSFORM */的hint提示.该hint的作用是:给出源端目标端要求的数据类型的明确信息(The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain type of operations are done within the database).屏蔽盖hint的方法是设置event:22825 trace name context forever, level 1(官方文档还提供了另外两种hint的方式屏蔽这个,但是我测试均未成功)

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

使用dblink导致的/*+ OPAQUE_TRANSFORM */》有 1 条评论

  1. 惜分飞 说:

    What is OPAQUE_TRANSFORM Hint and how to Control it [ID 780503.1]

    Applies to:
    
    Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later
    Information in this document applies to any platform.
    Oracle Server Enterprise Edition - Version: 10.2.0.3
    ***Checked for relevance on 02-MAR-2012***
    Goal
    
    What is OPAQUE_TRANSFORM usage :
    
    The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain 
    type of operations are done within the database.  For example object types .
    It is also used for a insert-as-remote-select operation on a remote database 
    
     
    
    Example : insert into emp (select * from emp@rep102b)   ;
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.50          0          0          0           0
    Execute      1      0.00       0.51          0          1         44          12
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       1.01          0          1         44          12
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 57  (SCOTT)
    
    Rows     Row Source Operation
    -------  ---------------------------------------------------
         12  REMOTE  EMP (cr=0 pr=0 pw=0 time=508808 us)
    
    
    Rows     Execution Plan
    -------  ---------------------------------------------------
          0  INSERT STATEMENT   MODE: ALL_ROWS
         12   REMOTE OF 'EMP' (REMOTE) [REP102B]
                 SELECT /*+ OPAQUE_TRANSFORM */ "EMPNO","ENAME","JOB","MGR",
                   "HIREDATE","SAL","COMM","DEPTNO" FROM "EMP" "EMP"
    
    
    Note :  This hint should not interfere with the query optimizer plan.
    
    Solution
    
    The below event can be set on the client (local) in order to turn the opaque_transform hint on and off..
    - To switch on :
    
    alter session set events '22825 trace name context off' ;
    
    - To switch off :
    
    1) alter session set events '22825 trace name context forever, level 1' ;
    
    2)  or using the following hint : /*+ NO_QUERY_TRANSFORMATION */ 
    3)  using  RULE hint.
    
     
    
    -Note that if local site is 11g and remote is  11g server, this opens 2 sessions 
       on the remote database and OPAQUE_TRANSFORM hint gives DX LOCK deadlock.
    
    - If  the local is 10g client and remote is 11g server, this opens 1 session on 
       the remote and no DX deadlock.