联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用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的方式屏蔽这个,但是我测试均未成功)
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.