| What is OPAQUE_TRANSFORM. Hint and how to Control it [ID 780503.1] | |||||
|
| |||||
| 修改时间 12-SEP-2010 类型 HOWTO 状态 PUBLISHED | |||||
In this Document
Goal
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3 and later [Release: 10.2 and later ]Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 10.2.0.3
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.
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.
References
BUG:5565178 - ORA-07445 [EVAOPN2()+220] ON SELECT WITH HINTBUG:7417255 - /*+ OPAQUE_TRANSFORM. */ HINT BEING REWRITTEN INTO QUERY AND CAUSING SLOWNESS