今天在查看AWR报告时，发现里面很多sql都有/+ OPAQUE_TRANSFORM / 觉得很奇怪，所以就上MOS上查了一下，以下内容基于原文翻译 (Doc ID 780503.1).
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
OPAQUE_TRANSFORM的用法:
OPAQUE_TRANSFORM hint 是帮助数据库中某种操作下的数据转换。例如对象类型。
它也用于远程数据库上的插入。
scott@DBHAWK>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1788691278
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 736 | 136K| 3 (0)| 00:00:01 | | |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | | | |
| 2 | REMOTE | TTT | 736 | 136K| 3 (0)| 00:00:01 | DBLIN~ | R->S |
-------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT /*+ OPAQUE_TRANSFORM */ "OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OB
JECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERA
TED","SECONDARY","NAMESPACE","EDITION_NAME" FROM "TTT" "TTT" (accessing 'DBLINKORCL' )
17 rows selected.
Note : This hint should not interfere with the query optimizer plan.
这个hint不会影响到查询优化器。
解决方法：
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.