\u6700\u8fd1\u7ecf\u5e38\u5728AWR\u4e2d\u770b\u5230\u5e26\u6709OPAQUE_TRANSFORM\u63d0\u793a\u7684SQL\u8bed\u53e5\uff0c\u6839\u636e\u5206\u6790\u53ef\u4ee5\u786e\u8ba4\u6267\u884c\u8fd9\u4e2aSQL\u7684\u8bed\u53e5\u662f\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u8fde\u63a5\u5230\u672c\u5730\uff0c\u4f46\u662f\u6d4b\u8bd5\u65f6\u53d1\u73b0\uff0c\u666e\u901a\u7684\u6570\u636e\u5e93\u94fe\u8fde\u63a5\u5e76\u4e0d\u4f1a\u5bfc\u81f4\u8fd9\u4e2a\u63d0\u793a\u7684\u4ea7\u751f\u3002
\n\u4e8e\u662f\u505a\u4e86\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\uff1a<\/p>\n
-bash-3.2$ sqlplus test\/test\r\nSQL*Plus: Release 10.2.0.5.0 - Production on Mon Dec 5 15:05:09 2011\r\nCopyright (c) 1982, 2010, Oracle. All Rights Reserved.\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\nSQL> set pages 100 lines 120\r\nSQL> create database link link_10g connect to test identified by test using '192.168.0.20\/orcl10g';\r\nDatabase link created.\r\nSQL> select global_name from global_name@link_10g;\r\nGLOBAL_NAME\r\n--------------------------------------------------------------------------------------\r\nORCL10G<\/pre>\n\u572810g\u7684\u6570\u636e\u5e93\u4e0a\uff0c\u5efa\u7acbTEST\u7528\u6237\u548c\u6d4b\u8bd5\u8868\uff0c\u76d1\u63a7\u4ece11g\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u7684\u8fde\u63a5\uff1a<\/p>\n
[ora10g@hpserver ~]$ sqlplus \/ as sysdba\r\nSQL*Plus: Release 10.2.0.4.0 - Production on Mon Dec 5 15:09:27 2011\r\nCopyright (c) 1982, 2007, Oracle. All Rights Reserved.\r\nConnected to:\r\nOracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production\r\nWith the Partitioning, Oracle Label Security, Data Mining and Real Application Testing options\r\nSQL> create user test identified by test default tablespace users;\r\nUser created.\r\nSQL> grant connect, resource to test;\r\nGrant succeeded.\r\nSQL> conn test\/test\r\nConnected.\r\nSQL> create table t as select * from all_objects;\r\nTable created.\r\nSQL> conn \/ as sysdba\r\nConnected.\r\nSQL> SELECT SID, USERNAME FROM V$SESSION WHERE USERNAME = 'TEST';\r\nSID USERNAME\r\n---------- ------------------------------\r\n146 TEST<\/pre>\n\u56de\u523011g\u73af\u5883\u4e2d\u6267\u884c\u4e0b\u9762\u7684\u67e5\u8be2\uff1a<\/p>\n
SQL> create table t as select * from dba_objects;\r\nTable created.\r\nSQL> set autot trace\r\nSQL> 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;\r\n4626 rows selected.\r\nExecution Plan\r\n----------------------------------------------------------\r\nPlan hash value: 2085754\r\n-------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|\r\n-------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 5391 | 615K| 59 (2)| 00:00:01 | | |\r\n|* 1 | HASH JOIN | | 5391 | 615K| 59 (2)| 00:00:01 | | |\r\n| 2 | REMOTE | T | 5391 | 178K| 16 (0)| 00:00:01 | LINK_~ | R->S |\r\n| 3 | TABLE ACCESS FULL| T | 13657 | 1106K| 42 (0)| 00:00:01 | | |\r\n-------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n 1 - access(\"A\".\"OWNER\"=\"B\".\"OWNER\" AND \"A\".\"OBJECT_NAME\"=\"B\".\"OBJECT_NAME\")\r\nRemote SQL Information (identified by operation id):\r\n----------------------------------------------------\r\n 2 - SELECT \"OWNER\",\"OBJECT_NAME\" FROM \"T\" \"B\" (accessing 'LINK_10G' )\r\nNote\r\n-----\r\n - dynamic sampling used for this statement (level=2)\r\nStatistics\r\n----------------------------------------------------------\r\n 11 recursive calls\r\n 1 db block gets\r\n 584 consistent gets\r\n 214 physical reads\r\n 256 redo size\r\n 140575 bytes sent via SQL*Net to client\r\n 3788 bytes received via SQL*Net from client\r\n 310 SQL*Net roundtrips to\/from client\r\n 1 sorts (memory)\r\n 0 sorts (disk)\r\n 4626 rows processed<\/pre>\n\u572810g\u73af\u5883\u4e2d\uff0c\u68c0\u67e5\u5bf9\u5e94\u7684SQL\u8bed\u53e5\uff1a<\/p>\n
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT SQL_ID FROM V$SESSION WHERE SID = 146);\r\nno rows selected\r\nSQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT PREV_SQL_ID FROM V$SESSION WHERE SID = 146);\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\nSELECT \"OWNER\",\"OBJECT_NAME\" FROM \"T\" \"B\"<\/pre>\n\u5e76\u6ca1\u6709\u627e\u5230\u9884\u671f\u7684OPAQUE_TRANSFORM\u63d0\u793a\u3002\u770b\u6765\u5e76\u4e0d\u662f\u7b80\u5355\u7684\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u67e5\u8be2\u7684SQL\u5c31\u4f1a\u5bfc\u81f4\u8fd9\u4e2a\u63d0\u793a\uff0c\u67e5\u8be2\u4e86\u4e00\u4e0bMOS\u53d1\u73b0\uff0c\u6700\u5e38\u89c1\u7684\u7c7b\u4f3cINSERT AS SELECT\u65b9\u5f0f\u5c31\u4f1a\u5bfc\u81f4\u8fd9\u4e2aHINT\u7684\u4ea7\u751f\uff0c\u9a8c\u8bc1\u4e00\u4e0b\uff0c\u572811g\u6570\u636e\u5e93\u4e2d\u6267\u884c\uff1a<\/p>\n
SQL> set autot off\r\nSQL> alter table t drop (edition_name, namespace);\r\nTable altered.\r\nSQL> explain plan for insert into t select * from t@link_10g;\r\nExplained.\r\nSQL> select * from table(dbms_xplan.display);\r\nPLAN_TABLE_OUTPUT\r\n-------------------------------------------------------------------------------------------\r\nPlan hash value: 1788691278\r\n-------------------------------------------------------------------------------------------\r\n|Id | Operation | Name| Rows | Bytes |Cost(%CPU)| Time | Inst |IN-OUT|\r\n-------------------------------------------------------------------------------------------\r\n| 0 | INSERT STATEMENT | | 5391 | 673K| 16 (0)| 00:00:01 | | |\r\n| 1 | LOAD TABLE CONVENTIONAL | T | | | | | | |\r\n| 2 | REMOTE | T | 5391 | 673K| 16 (0)| 00:00:01 | LINK_~ | R->S |\r\n-------------------------------------------------------------------------------------------\r\nRemote SQL Information (identified by operation id):\r\n----------------------------------------------------\r\n 2 - SELECT \/*+ OPAQUE_TRANSFORM *\/ \"OWNER\",\"OBJECT_NAME\",\"SUBOBJECT_NAME\",\"OBJECT_ID\",\r\n \"DATA_OBJECT_ID\",\"OBJECT_TYPE\",\"CREATED\",\"LAST_DDL_TIME\",\"TIMESTAMP\",\"STATUS\",\"TEMPORARY\"\r\n ,\"GENERATED\",\"SECONDARY\" FROM \"T\" \"T\" (accessing 'LINK_10G' )\r\n17 rows selected.\r\nSQL> insert into t select * from t@link_10g;\r\n4656 rows created.<\/pre>\n\u5728\u6267\u884c\u8ba1\u5212\u4e2d\u5df2\u7ecf\u53ef\u4ee5\u770b\u5230OPAQUE_TRANSFORM\u63d0\u793a\u7684\u5b58\u5728\u4e86\uff0c\u4e3a\u4e86\u8fdb\u4e00\u6b65\u9a8c\u8bc1\uff0c\u8fd0\u884c\u4e00\u4e2aINSERT INTO SELECT\u8bed\u53e5\uff0c\u572810g\u73af\u5883\u4e2d\u67e5\u8be2\u672c\u5730\u7684SQL\uff1a<\/p>\n
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (SELECT PREV_SQL_ID FROM V$SESSION WHERE SID = 146);\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\nSELECT \/*+ OPAQUE_TRANSFORM *\/ \"OWNER\",\"OBJECT_NAME\",\"SUBOBJECT_NAME\",\"OBJECT_ID\r\n\",\"DATA_OBJECT_ID\",\"OBJECT_TYPE\",\"CREATED\",\"LAST_DDL_TIME\",\"TIMESTAMP\",\"STATUS\",\r\n\"TEMPORARY\",\"GENERATED\",\"SECONDARY\" FROM \"T\" \"T\"<\/pre>\n\u73b0\u5728\u53ef\u4ee5\u786e\u8ba4\uff0c\u5e73\u5e38\u770b\u5230\u7684OPAQUE_TRANSFORM\u63d0\u793a\uff0c\u90fd\u662f\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u6267\u884cINSERT INTO SELECT\u8bed\u53e5\u6240\u81f4\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u6700\u8fd1\u7ecf\u5e38\u5728AWR\u4e2d\u770b\u5230\u5e26\u6709OPAQUE_TRANSFORM\u63d0\u793a\u7684SQL\u8bed\u53e5\uff0c\u6839\u636e\u5206\u6790\u53ef\u4ee5\u786e\u8ba4\u6267\u884c\u8fd9\u4e2aSQL\u7684\u8bed\u53e5\u662f\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u8fde\u63a5\u5230\u672c\u5730\uff0c\u4f46\u662f\u6d4b\u8bd5\u65f6\u53d1\u73b0\uff0c\u666e\u901a\u7684\u6570\u636e\u5e93\u94fe\u8fde\u63a5\u5e76\u4e0d\u4f1a\u5bfc\u81f4\u8fd9\u4e2a\u63d0\u793a\u7684\u4ea7\u751f\u3002 \u4e8e\u662f\u505a\u4e86\u4e00\u4e2a\u7b80\u5355\u7684\u4f8b\u5b50\uff1a -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 … Continue reading