oracle opaque_transform,oracle databse link

关于这个hint 是10.2.0.3 之后出现的，在insert into table select * from [email protected]_link 这一类型的语句， 在执行时，源端数据库中出现的。

在MOS doc 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 [email protected]) ;

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.