关闭

sort merge join导致temp被爆菊

983人阅读 评论(0) 收藏 举报
分类:
SQL_ID  cqsz37256v36j, child number 1
-------------------------------------
INSERT /*+append*/ INTO TMP_ACCT_AF NOLOGGING ( DATA_DATE , ACCT_NO , ACCT_ORD , ACCT_NO_PK , ACCT_BAL , D_CMP_BAL ,
M_CMP_BAL , Y_CMP_BAL , FLAG , ACCT_FLAG , TERM , TERM_FLAG , CUR_CODE , CUR_NAME , SUB_CODE , CUST_NO , CUST_TYPE ,
CUST_NAME , BANK_CORP_CODE , BRAN_NAME , MGR_CODE , MGR_NAME , OPEN_DATE , FIX_BAL , DIV_FIX_FLAG , ADJUST_AMT ,
ADJUST_AMT_AF , Y_AVG_AF , Y_ADD_AF , ACCT_INTR , SIM_PROFIT , SEPA_POR , PRI , BRAN_CODE , UNIT1_CODE , UNIT2_CODE ,
UNIT3_CODE , DEPT1_CODE , INTR_RATE , DUE_DATE ) SELECT /*+use_merge(t1 t2)parallel(t1 16) use_hash(t1 t3)*/ DATA_DATE,
ACCT_NO, ACCT_ORD, ACCT_NO_PK, ACCT_BAL, D_CMP_BAL, M_CMP_BAL, Y_CMP_BAL, FLAG, ACCT_FLAG, TERM, TERM_FLAG, CUR_CODE,
CUR_NAME, SUB_CODE, CUST_NO, CUST_TYPE, CUST_NAME, BANK_CORP_CODE, BRAN_NAME, NVL(T3.MGR_CODE, T1.MGR_CODE), T1.MGR_NAME,
OPEN_DATE, FIX_BAL, DIV_FIX_FLAG, ADJUST_AMT, ADJUST_AMT_AF, Y_AVG_AF, Y_ADD_AF, ACCT_INTR, SIM_PROFIT, SEPA_POR, PRI,
T1.BRAN_CODE, T2.UNIT1_CODE, T2.UNIT2_CODE, T1.BRAN_CODE
 
Plan hash value: 1366440900
 
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                    |       |       |    24 (100)|          |        |      |            |
|   1 |  LOAD AS SELECT                  |                    |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR                 |                    |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)           | :TQ10005           |     1 |   672 |    24  (30)| 00:00:01 |  Q1,05 | P->S | QC (RAND)  |
|   4 |     VIEW                         |                    |     1 |   672 |    24  (30)| 00:00:01 |  Q1,05 | PCWP |            |
|   5 |      SORT UNIQUE                 |                    |     1 |   650 |    24  (30)| 00:00:01 |  Q1,05 | PCWP |            |
|   6 |       PX RECEIVE                 |                    |     1 |   650 |    23  (27)| 00:00:01 |  Q1,05 | PCWP |            |
|   7 |        PX SEND HASH              | :TQ10004           |     1 |   650 |    23  (27)| 00:00:01 |  Q1,04 | P->P | HASH       |
|*  8 |         HASH JOIN OUTER          |                    |     1 |   650 |    23  (27)| 00:00:01 |  Q1,04 | PCWP |            |
|   9 |          PX RECEIVE              |                    |     1 |   622 |    18  (28)| 00:00:01 |  Q1,04 | PCWP |            |
|  10 |           PX SEND HASH           | :TQ10003           |     1 |   622 |    18  (28)| 00:00:01 |  Q1,03 | P->P | HASH       |
|  11 |            MERGE JOIN OUTER      |                    |     1 |   622 |    18  (28)| 00:00:01 |  Q1,03 | PCWP |            |
|  12 |             SORT JOIN            |                    |     1 |   601 |     3  (34)| 00:00:01 |  Q1,03 | PCWP |            |
|  13 |              PX RECEIVE          |                    |     1 |   601 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |
|  14 |               PX SEND HASH       | :TQ10002           |     1 |   601 |     2   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |
|  15 |                PX BLOCK ITERATOR |                    |     1 |   601 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |
|* 16 |                 TABLE ACCESS FULL| TMP_ACCT_AF2       |     1 |   601 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|* 17 |             SORT JOIN            |                    |  8158 |   167K|    15  (27)| 00:00:01 |  Q1,03 | PCWP |            |
|  18 |              BUFFER SORT         |                    |       |       |            |          |  Q1,03 | PCWC |            |
|  19 |               PX RECEIVE         |                    |  8158 |   167K|    12   (9)| 00:00:01 |  Q1,03 | PCWP |            |
|  20 |                PX SEND HASH      | :TQ10000           |  8158 |   167K|    12   (9)| 00:00:01 |        | S->P | HASH       |
|  21 |                 TABLE ACCESS FULL| S_PM_MGR_DEPT_RELA |  8158 |   167K|    12   (9)| 00:00:01 |        |      |            |
|  22 |          BUFFER SORT             |                    |       |       |            |          |  Q1,04 | PCWC |            |
|  23 |           PX RECEIVE             |                    |  3902 |   106K|     5  (20)| 00:00:01 |  Q1,04 | PCWP |            |
|  24 |            PX SEND HASH          | :TQ10001           |  3902 |   106K|     5  (20)| 00:00:01 |        | S->P | HASH       |
|* 25 |             INDEX FAST FULL SCAN | MGR_DEPT_RELA_IDX2 |  3902 |   106K|     5  (20)| 00:00:01 |        |      |            |
------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   8 - access("T1"."MGR_CODE"="T3"."MGR_CODE")
  16 - access(:Z>=:Z AND :Z<=:Z)
  17 - access("T1"."BRAN_CODE"="T2"."UNIT3_CODE")
       filter("T1"."BRAN_CODE"="T2"."UNIT3_CODE")
  25 - filter("T3"."DEPT1_CODE"<>'999999999')


 TMP_ACCT_AF2 T1

(SELECT DISTINCT T2.UNIT3_CODE, T2.UNIT2_CODE, T2.UNIT1_CODE
                 FROM S_PM_MGR_DEPT_RELA T2) T2


S_PM_MGR_DEPT_RELA T3

 

T1 和 T2 进行 sort merge join 在 T1和 T3进行 hash join,直接导致temp被耗尽

 

 (SELECT /*+use_merge(t1 t2)parallel(t1 16) use_hash(t1 t3)*/
   DATA_DATE,
   ACCT_NO,
   ACCT_ORD,
   ACCT_NO_PK,
   ACCT_BAL,
   D_CMP_BAL,
   M_CMP_BAL,
   Y_CMP_BAL,
   FLAG,
   ACCT_FLAG,
   TERM,
   TERM_FLAG,
   CUR_CODE,
   CUR_NAME,
   SUB_CODE,
   CUST_NO,
   CUST_TYPE,
   CUST_NAME,
   BANK_CORP_CODE,
   BRAN_NAME,
   NVL(T3.MGR_CODE, T1.MGR_CODE),
   T1.MGR_NAME,
   OPEN_DATE,
   FIX_BAL,
   DIV_FIX_FLAG,
   ADJUST_AMT,
   ADJUST_AMT_AF,
   Y_AVG_AF,
   Y_ADD_AF,
   ACCT_INTR,
   SIM_PROFIT,
   SEPA_POR,
   PRI,
   T1.BRAN_CODE,
   T2.UNIT1_CODE,
   T2.UNIT2_CODE,
   T1.BRAN_CODE AS UNIT3_CODE,
   NVL(T3.DEPT1_CODE, '999999999'),
   T1.INTR_RATE,
   T1.DUE_DATE
    FROM TMP_ACCT_AF2 T1
    LEFT JOIN S_PM_MGR_DEPT_RELA T3
      ON T1.MGR_CODE = T3.MGR_CODE
     AND T3.DEPT1_CODE <> '999999999'
    LEFT JOIN (SELECT DISTINCT T2.UNIT3_CODE, T2.UNIT2_CODE, T2.UNIT1_CODE
                 FROM S_PM_MGR_DEPT_RELA T2) T2
      ON T1.BRAN_CODE = T2.UNIT3_CODE)

Plan hash value: 4109009912
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                    |   142M|    28G|       |   629  (69)| 00:00:02 |        |      |            |
|   1 |  LOAD AS SELECT               | TMP_ACCT_AF        |       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR              |                    |       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10002           |   142M|    28G|       |   629  (69)| 00:00:02 |  Q1,02 | P->S | QC (RAND)  |
|   4 |     MERGE JOIN OUTER          |                    |   142M|    28G|       |   629  (69)| 00:00:02 |  Q1,02 | PCWP |            |
|   5 |      SORT JOIN                |                    |  1500K|   208M|   450M|   254  (27)| 00:00:01 |  Q1,02 | PCWP |            |
|*  6 |       HASH JOIN RIGHT OUTER   |                    |  1500K|   208M|       |   225  (17)| 00:00:01 |  Q1,02 | PCWP |            |
|   7 |        BUFFER SORT            |                    |       |       |       |            |          |  Q1,02 | PCWC |            |
|   8 |         PX RECEIVE            |                    |  3902 | 62432 |       |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND BROADCAST    | :TQ10000           |  3902 | 62432 |       |     5  (20)| 00:00:01 |        | S->P | BROADCAST  |
|* 10 |           INDEX FAST FULL SCAN| MGR_DEPT_RELA_IDX2 |  3902 | 62432 |       |     5  (20)| 00:00:01 |        |      |            |
|  11 |        PX BLOCK ITERATOR      |                    |  1407K|   174M|       |   217  (16)| 00:00:01 |  Q1,02 | PCWC |            |
|  12 |         TABLE ACCESS FULL     | TMP_ACCT_AF2       |  1407K|   174M|       |   217  (16)| 00:00:01 |  Q1,02 | PCWP |            |
|* 13 |      SORT JOIN                |                    |  7315 |   471K|       |    17  (36)| 00:00:01 |  Q1,02 | PCWP |            |
|  14 |       BUFFER SORT             |                    |       |       |       |            |          |  Q1,02 | PCWC |            |
|  15 |        PX RECEIVE             |                    |  7315 |   471K|       |    15  (27)| 00:00:01 |  Q1,02 | PCWP |            |
|  16 |         PX SEND BROADCAST     | :TQ10001           |  7315 |   471K|       |    15  (27)| 00:00:01 |        | S->P | BROADCAST  |
|  17 |          VIEW                 |                    |  7315 |   471K|       |    15  (27)| 00:00:01 |        |      |            |
|  18 |           SORT UNIQUE         |                    |  7315 |   150K|       |    15  (27)| 00:00:01 |        |      |            |
|  19 |            TABLE ACCESS FULL  | S_PM_MGR_DEPT_RELA |  8158 |   167K|       |    12   (9)| 00:00:01 |        |      |            |
-----------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T1"."MGR_CODE"="T3"."MGR_CODE"(+))
  10 - filter("T3"."DEPT1_CODE"(+)<>'999999999')
  13 - access("T1"."BRAN_CODE"="T2"."UNIT3_CODE"(+))
       filter("T1"."BRAN_CODE"="T2"."UNIT3_CODE"(+))

 正确关联顺序为T1 和 T3进行HASH JOIN  在通过T1和T2 进行sort merge join

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1385948次
    • 积分:41603
    • 等级:
    • 排名:第92名
    • 原创:2896篇
    • 转载:14篇
    • 译文:0篇
    • 评论:54条
    文章分类
    最新评论