OPAQUE_TRANSFORM提示的产生

最近经常在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语句所致。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-712773/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-712773/