最近经常在AWR中看到带有OPAQUE_TRANSFORM提示的SQL语句，根据分析可以确认执行这个SQL的语句是通过数据库链连接到本地，但是测试时发现，普通的数据库链连接并不会导致这个提示的产生。
于是做了一个简单的例子：
-bash-3.2$ sqlplus test/test SQL*Plus: Release 10.2.0.5.0 - Production ON Mon DEC 5 15:05:09 2011 Copyright (c) 1982, 2010, Oracle. ALL Rights Reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> SET pages 100 LINES 120 SQL> CREATE DATABASE link link_10g CONNECT TO test IDENTIFIED BY test USING '192.168.0.20/orcl10g'; DATABASE link created. SQL> SELECT global_name FROM global_name@link_10g; GLOBAL_NAME -------------------------------------------------------------------------------------- ORCL10G |
在10g的数据库上，建立TEST用户和测试表，监控从11g通过数据库链的连接：
[ora10g@hpserver ~]$ sqlplus / AS sysdba SQL*Plus: Release 10.2.0.4.0 - Production ON Mon DEC 5 15:09:27 2011 Copyright (c) 1982, 2007, Oracle. ALL Rights Reserved. Connected TO: Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Production WITH the Partitioning, Oracle Label Security, DATA Mining AND REAL Application Testing options SQL> CREATE USER test IDENTIFIED BY test DEFAULT tablespace users; USER created. SQL> GRANT CONNECT, resource TO test; GRANT succeeded. SQL> conn test/test Connected. SQL> CREATE TABLE t AS SELECT * FROM all_objects; TABLE created. SQL> conn / AS sysdba Connected. SQL> SELECT SID, USERNAME FROM V$SESSION WHERE USERNAME = 'TEST'; SID USERNAME ---------- ------------------------------ 146 TEST |
回到11g环境中执行下面的查询：
SQL> CREATE TABLE t AS SELECT * FROM dba_objects; TABLE created. SQL> SET autot trace SQL> SELECT b.owner, a.object_name FROM t a, t@link_10g b WHERE a.owner = b.owner AND a.object_name = b.object_name; 4626 ROWS selected. Execution Plan ---------------------------------------------------------- Plan hash VALUE: 2085754 ------------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | Inst |IN-OUT| ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5391 | 615K| 59 (2)| 00:00:01 | | | |* 1 | HASH JOIN | | 5391 | 615K| 59 (2)| 00:00:01 | | | | 2 | REMOTE | T | 5391 | 178K| 16 (0)| 00:00:01 | LINK_~ | R->S | | 3 | TABLE ACCESS FULL| T | 13657 | 1106K| 42 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."OBJECT_NAME") Remote SQL Information (IDENTIFIED BY operation id): ---------------------------------------------------- 2 - SELECT "OWNER","OBJECT_NAME" FROM "T" "B" (accessing 'LINK_10G' ) Note ----- - dynamic sampling used FOR this statement (level=2) Statistics ---------------------------------------------------------- 11 recursive calls 1 db block gets 584 consistent gets 214 physical reads 256 redo SIZE 140575 bytes sent via SQL*Net TO client 3788 bytes received via SQL*Net FROM client 310 SQL*Net roundtrips TO/FROM client 1 sorts (memory) 0 sorts (disk) 4626 ROWS processed |
在10g环境中，检查对应的SQL语句：
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = 146); no ROWS selected SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT PREV_SQL_ID FROM V$SESSION WHERE SID = 146); SQL_TEXT -------------------------------------------------------------------------------- SELECT "OWNER","OBJECT_NAME" FROM "T" "B" |
并没有找到预期的OPAQUE_TRANSFORM提示。看来并不是简单的通过数据库链查询的SQL就会导致这个提示，查询了一下MOS发现，最常见的类似INSERT AS SELECT方式就会导致这个HINT的产生，验证一下，在11g数据库中执行：
SQL> SET autot off SQL> ALTER TABLE t DROP (edition_name, namespace); TABLE altered. SQL> EXPLAIN plan FOR INSERT INTO t SELECT * FROM t@link_10g; Explained. SQL> 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 | | 5391 | 673K| 16 (0)| 00:00:01 | | | | 1 | LOAD TABLE CONVENTIONAL | T | | | | | | | | 2 | REMOTE | T | 5391 | 673K| 16 (0)| 00:00:01 | LINK_~ | R->S | ------------------------------------------------------------------------------------------- Remote SQL Information (IDENTIFIED BY operation id): ---------------------------------------------------- 2 - 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" "T" (accessing 'LINK_10G' ) 17 ROWS selected. SQL> INSERT INTO t SELECT * FROM t@link_10g; 4656 ROWS created. |
在执行计划中已经可以看到OPAQUE_TRANSFORM提示的存在了，为了进一步验证，运行一个INSERT INTO SELECT语句，在10g环境中查询本地的SQL：
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT PREV_SQL_ID FROM V$SESSION WHERE SID = 146); SQL_TEXT -------------------------------------------------------------------------------- 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" "T" |
现在可以确认，平常看到的OPAQUE_TRANSFORM提示，都是通过数据库链执行INSERT INTO SELECT语句所致。