2011-11-30 dblink与执行计划

巩固知识点1： DBLINK 可能导致糟糕的执行计划：
mig_atgcore@SEPMIGDB-UnknownIP>explain plan for SELECT DCC.CREDIT_CARD_ID,
  2          DCCE.ENCRYPTED_NUMBER,
  3          DCC.CREDIT_CARD_TYPE,
  4          DCC.EXPIRATION_MONTH,
  5          DCC.EXP_DAY_OF_MONTH,
  6          DCC.EXPIRATION_YEAR,
  7          DCC.EXPIRATION_DATE,
  8          DCC.BILLING_ADDR_ID,
  9          DCC.CARD_STATUS_CODE
 10  FROM  MIG_ETL.DPS_CREDIT_CARD DCC
 11  ,MIG_ATGCORE.DPS_CREDIT_CARD_ENCRYPTED DCCE
 12  WHERE DCC.CREDIT_CARD_ID=DCCE.CREDIT_CARD_ID
 13  AND EXISTS (SELECT 1 FROM SEPH_OWNER.dps_user_credit_card@LNK_SEREC DUC
 14  WHERE DCC.CREDIT_CARD_ID = DUC.CREDIT_CARD_ID
 15  and EXISTS
 16  (SELECT 1 FROM atgcore.DPS_USER_TEMP DPU
 17    WHERE dpu.USER_ID = duc.USER_ID)
 18   );

Explained.

Elapsed: 00:00:00.61
mig_atgcore@SEPMIGDB-UnknownIP>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
-----------------------
Plan hash value: 369892978

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time  | Inst      |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                           |     1 |   107 |       |    18M  (1)| 62:52:18 |        |      |
|*  1 |  FILTER             |                           |       |       |       |            |       |   |      |
|*  2 |   HASH JOIN         |                           |  3763K|   384M|   226M| 44719   (2)| 00:08:57 |        |      |
|   3 |    TABLE ACCESS FULL| DPS_CREDIT_CARD           |  3763K|   183M|       |  9835   (2)| 00:01:59 |        |      |
|   4 |    TABLE ACCESS FULL| DPS_CREDIT_CARD_ENCRYPTED |  3763K|   201M|       | 11406   (1)| 00:02:17 |        |      |
|   5 |   NESTED LOOPS SEMI |                           |     1 |    59 |       |     5   (0)| 00:00:01 |        |      |
|   6 |    REMOTE           | DPS_USER_CREDIT_CARD      |     1 |    49 |       |     4   (0)| 00:00:01 | LNK_S~ | R->S |
|*  7 |    INDEX UNIQUE SCAN| SYS_C0032626              |   922K|  9010K|       |     1   (0)| 00:00:01 |        |      |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ )
   2 - access("DCC"."CREDIT_CARD_ID"="DCCE"."CREDIT_CARD_ID")
   7 - access("DPU"."USER_ID"="DUC"."USER_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   6 - SELECT "USER_ID","CREDIT_CARD_ID" FROM "SEPH_OWNER"."DPS_USER_CREDIT_CARD" "DUC" WHERE
       "CREDIT_CARD_ID"=:1 (accessing 'LNK_SEREC' )


28 rows selected.

Elapsed: 00:00:01.25


mig_atgcore@SEPMIGDB-UnknownIP>explain plan for SELECT DCC.CREDIT_CARD_ID,
  2          DCCE.ENCRYPTED_NUMBER,
  3          DCC.CREDIT_CARD_TYPE,
  4          DCC.EXPIRATION_MONTH,
  5          DCC.EXP_DAY_OF_MONTH,
  6          DCC.EXPIRATION_YEAR,
  7          DCC.EXPIRATION_DATE,
  8          DCC.BILLING_ADDR_ID,
  9          DCC.CARD_STATUS_CODE
 10  FROM  MIG_ETL.DPS_CREDIT_CARD DCC
 11  ,MIG_ATGCORE.DPS_CREDIT_CARD_ENCRYPTED DCCE,
 12  SEPH_OWNER.dps_user_credit_card@LNK_SEREC DUC
 13  WHERE DCC.CREDIT_CARD_ID=DCCE.CREDIT_CARD_ID
 14  AND DCC.CREDIT_CARD_ID = DUC.CREDIT_CARD_ID
 15  and EXISTS
 16  (SELECT 1 FROM atgcore.DPS_USER_TEMP DPU
 17    WHERE dpu.USER_ID = duc.USER_ID);

Explained.

Elapsed: 00:00:00.62
mig_atgcore@SEPMIGDB-UnknownIP>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
-----------------------
Plan hash value: 2199490643

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                           |  1114K|   176M|       | 63378   (2)| 00:12:41 |        |      |
|*  1 |  HASH JOIN              |                           |  1114K|   176M|   129M| 63378   (2)| 00:12:41 |        |      |
|*  2 |   HASH JOIN             |                           |  1114K|   116M|    75M| 33323   (2)| 00:06:40 |        |      |
|*  3 |    HASH JOIN RIGHT SEMI |                           |  1114K|    62M|    19M|  8428   (2)| 00:01:42 |        |      |
|   4 |     INDEX FAST FULL SCAN| SYS_C0032626              |   922K|  9010K|       |  1097   (2)| 00:00:14 |        |      |
|   5 |     REMOTE              | DPS_USER_CREDIT_CARD      |  1705K|    79M|       |  1392   (4)| 00:00:17 | LNK_S~ | R->S |
|   6 |    TABLE ACCESS FULL    | DPS_CREDIT_CARD           |  3763K|   183M|       |  9826   (2)| 00:01:58 |        |      |
|   7 |   TABLE ACCESS FULL     | DPS_CREDIT_CARD_ENCRYPTED |  3763K|   201M|       | 11406   (1)| 00:02:17 |        |      |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DCC"."CREDIT_CARD_ID"="DCCE"."CREDIT_CARD_ID")
   2 - access("DCC"."CREDIT_CARD_ID"="DUC"."CREDIT_CARD_ID")
   3 - access("DPU"."USER_ID"="DUC"."USER_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "USER_ID","CREDIT_CARD_ID" FROM "SEPH_OWNER"."DPS_USER_CREDIT_CARD" "DUC" (accessing 'LNK_SEREC' )


27 rows selected.

Elapsed: 00:00:01.25


mig_atgcore@SEPMIGDB-UnknownIP>explain plan for SELECT DCC.CREDIT_CARD_ID,
  2          DCCE.ENCRYPTED_NUMBER,
  3          DCC.CREDIT_CARD_TYPE,
  4          DCC.EXPIRATION_MONTH,
  5          DCC.EXP_DAY_OF_MONTH,
  6          DCC.EXPIRATION_YEAR,
  7          DCC.EXPIRATION_DATE,
  8          DCC.BILLING_ADDR_ID,
  9          DCC.CARD_STATUS_CODE
 10  FROM  MIG_ETL.DPS_CREDIT_CARD DCC
 11  ,MIG_ATGCORE.DPS_CREDIT_CARD_ENCRYPTED DCCE
 12  WHERE DCC.CREDIT_CARD_ID=DCCE.CREDIT_CARD_ID
 13  AND EXISTS (SELECT /*+unnest*/1 FROM SEPH_OWNER.dps_user_credit_card@LNK_SEREC DUC
 14  WHERE DCC.CREDIT_CARD_ID = DUC.CREDIT_CARD_ID
 15  and EXISTS
 16  (SELECT 1 FROM atgcore.DPS_USER_TEMP DPU
 17    WHERE dpu.USER_ID = duc.USER_ID)
 18    );

Explained.

Elapsed: 00:00:00.61
mig_atgcore@SEPMIGDB-UnknownIP>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
-----------------------
Plan hash value: 1850591627

-------------------------------------------------------------------------------------------------------------------------------
------
| Id  | Operation                    | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |I
N-OUT|
-------------------------------------------------------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT             |                              |     1 |   169 |       | 33483   (2)| 00:06:42 |        |
    |
|   1 |  NESTED LOOPS                |                              |       |       |       |         |     |        |      |
|   2 |   NESTED LOOPS               |                              |     1 |   169 |       | 33483   (2)| 00:06:42 |        |
    |
|*  3 |    HASH JOIN RIGHT SEMI      |                              |     1 |   113 |    78M| 33481   (2)| 00:06:42 |        |
    |
|   4 |     VIEW                     | VW_SQ_1                      |  1114K|    65M|       |  8428   (2)| 00:01:42 |        |
    |
|*  5 |      HASH JOIN RIGHT SEMI    |                              |  1114K|    62M|    19M|  8428   (2)| 00:01:42 |        |
    |
|   6 |       INDEX FAST FULL SCAN   | SYS_C0032626                 |   922K|  9010K|       |  1097   (2)| 00:00:14 |        |
    |
|   7 |       REMOTE                 | DPS_USER_CREDIT_CARD         |  1705K|    79M|       |  1392   (4)| 00:00:17 | LNK_S~ |
R->S |
|   8 |     TABLE ACCESS FULL        | DPS_CREDIT_CARD              |  3763K|   183M|       |  9826   (2)| 00:01:58 |        |
    |
|*  9 |    INDEX UNIQUE SCAN         | PK_DPS_CREDIT_CARD_ENCRYPTED |     1 |       |       |     1   (0)| 00:00:01 |        |
    |
|  10 |   TABLE ACCESS BY INDEX ROWID| DPS_CREDIT_CARD_ENCRYPTED    |     1 |    56 |       |     2   (0)| 00:00:01 |        |
    |
-------------------------------------------------------------------------------------------------------------------------------
------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DCC"."CREDIT_CARD_ID"="ITEM_0")
   5 - access("DPU"."USER_ID"="DUC"."USER_ID")
   9 - access("DCC"."CREDIT_CARD_ID"="DCCE"."CREDIT_CARD_ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   7 - SELECT "USER_ID","CREDIT_CARD_ID" FROM "SEPH_OWNER"."DPS_USER_CREDIT_CARD" "DUC" (accessing 'LNK_SEREC' )


30 rows selected.

Elapsed: 00:00:01.25

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

转载于:http://blog.itpub.net/24383181/viewspace-712460/