OPAQUE_TRANSFORM Hint 是什麼？

今天在檢視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 是幫助資料庫中某種操作下的資料轉換。例如對象類型。

它也用于遠端資料庫上的插入。

[email protected]>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.
           

轉載于:https://www.cnblogs.com/hawk20110625/p/9309474.html