最近經常在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

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

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語句所致。

OPAQUE_TRANSFORM提示的產生

相關文章