Oracle 19c Merge语句执行超时问题及调优方案分享

问题背景 #

在Oracle Database 19c环境中，执行一条仅包含UPDATE操作的MERGE语句时，语句始终无法执行完成，单独执行其SRC子查询也无响应。本次操作涉及两张表：

• TEST_TAB1：约17369608行
• TEST_TAB2：约300711行

通过会话监控捕获到以下等待事件：gc cr multi block mixed、library cache pin、pga memory operation。

原表结构、MERGE语句及执行计划 #

表结构 #

CREATE TABLE TEST_TAB1 
(    
    EFFECTIVE_DT DATE NOT NULL ENABLE, 
    CMPNY_CD VARCHAR2(3 BYTE) NOT NULL ENABLE, 
    CURRENCY VARCHAR2(3 BYTE) NOT NULL ENABLE, 
    EXCHNG_RT NUMBER(28,13) NOT NULL ENABLE, 
    LAST_CHNG TIMESTAMP (6) NOT NULL ENABLE
) 
TABLESPACE APP_TS ;

CREATE UNIQUE INDEX TEST_TAB1_IDX1 ON TEST_TAB1 (EFFECTIVE_DT, CMPNY_CD, CURRENCY)
TABLESPACE APP_TS ;

ALTER TABLE TEST_TAB1 ADD CONSTRAINT TEST_TAB1_PK PRIMARY KEY (EFFECTIVE_DT, CMPNY_CD, CURRENCY)
USING INDEX TEST_TAB1_IDX1  ENABLE;

CREATE INDEX TEST_TAB1_IDX2 ON TEST_TAB1 (LAST_CHNG)
TABLESPACE APP_TS ;

-- TEST_TAB1 行数：17369608

CREATE TABLE TEST_TAB2 
(    
    BUSINESS_DT DATE NOT NULL ENABLE,           
    ORG_CRCY VARCHAR2(3 BYTE) NOT NULL ENABLE,  
    ORG_CRCY_A NUMBER(23,3) NOT NULL ENABLE,     
    CMPNY_CD VARCHAR2(3 BYTE) NOT NULL ENABLE, 
    FUNC_CRCY NUMBER(23,3) NOT NULL ENABLE
) 
TABLESPACE APP_TS ;

-- TEST_TAB2 行数：300711

原MERGE语句 #

MERGE INTO TEST_TAB2 wk
USING (
    With temp_tab2 as (select distinct CMPNY_CD,ORG_CRCY ,BUSINESS_DT from TEST_TAB2 )
    ,temp_tab1 as (select max(EFFECTIVE_DT) as EFFECTIVE_DT, g1.CMPNY_CD,g1.ORG_CRCY,g1.BUSINESS_DT, 0.0  EXCHNG_RT
                   from temp_tab2 g1 inner join  TEST_TAB1 flx on g1.CMPNY_CD=flx.CMPNY_CD and g1.ORG_CRCY=flx.CURRENCY
                   and flx.EFFECTIVE_DT < g1.BUSINESS_DT group by g1.CMPNY_CD,g1.ORG_CRCY,g1.BUSINESS_DT)
    ,temp_tab1Final as ( select t1.EFFECTIVE_DT, t1.CMPNY_CD,t1.ORG_CRCY,t1.BUSINESS_DT,
       case when flx.CURRENCY  is not null then flx.EXCHNG_RT else t1.EXCHNG_RT end rate
       from temp_tab1 t1 left join TEST_TAB1 flx
       on  t1.CMPNY_CD=flx.CMPNY_CD and t1.ORG_CRCY=flx.CURRENCY and t1.EFFECTIVE_DT=flx.EFFECTIVE_DT
       )select * from temp_tab1Final
)src ON ( wk.CMPNY_CD=src.CMPNY_CD and src.ORG_CRCY =wk.ORG_CRCY )
WHEN MATCHED  THEN
UPDATE  SET FUNC_CRCY = src.rate * wk.ORG_CRCY_A;               

执行计划 #

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |                                   |     1 |    39 |  2132   (1)| 00:00:01 |
|   1 |  MERGE                         | TEST_TAB2                         |       |       |            |          |
|   2 |   VIEW                         |                                   |       |       |            |          |
|   3 |    NESTED LOOPS OUTER          |                                   |     1 |   266 |  2132   (1)| 00:00:01 |
|*  4 |     HASH JOIN                  |                                   |     1 |   242 |  2130   (1)| 00:00:01 |
|   5 |      TABLE ACCESS FULL         | TEST_TAB2                         |     1 |   216 |   307   (0)| 00:00:01 |
|   6 |      VIEW                      |                                   |     1 |    26 |  1823   (1)| 00:00:01 |
|   7 |       SORT GROUP BY            |                                   |     1 |    31 |  1823   (1)| 00:00:01 |
|   8 |        NESTED LOOPS            |                                   |     1 |    31 |  1822   (1)| 00:00:01 |
|   9 |         TABLE ACCESS FULL      | TEST_TAB2                         |     1 |    15 |   307   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN       | TEST_TAB1_IDX1                    |    48 |   768 |  1515   (1)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID| TEST_TAB1                         |     1 |    24 |     2   (0)| 00:00:01 |
|* 12 |      INDEX UNIQUE SCAN         | TEST_TAB1_IDX1                    |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("WK"."CMPNY_CD"="T1"."CMPNY_CD" AND "T1"."ORG_CRCY"="WK"."ORG_CRCY")
  10 - access("CMPNY_CD"="FLX"."CMPNY_CD" AND "ORG_CRCY"="FLX"."CURRENCY" AND "FLX"."EFFECTIVE_DT"<"BUSINESS_DT")
       filter("ORG_CRCY"="FLX"."CURRENCY" AND "CMPNY_CD"="FLX"."CMPNY_CD")
  12 - access("T1"."EFFECTIVE_DT"="FLX"."EFFECTIVE_DT"(+) AND "T1"."CMPNY_CD"="FLX"."CMPNY_CD"(+) AND 
              "T1"."ORG_CRCY"="FLX"."CURRENCY"(+))            

Oracle version -- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

示例数据 #

Insert into TEST_TAB1 (EFFECTIVE_DT,CMPNY_CD,CURRENCY,EXCHNG_RT,LAST_CHNG) values (to_date('15-AUG-22','DD-MON-RR'),'XYZ','USD',1.328991959599,to_timestamp('22-JAN-15 01.19.38.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST_TAB1 (EFFECTIVE_DT,CMPNY_CD,CURRENCY,EXCHNG_RT,LAST_CHNG) values (to_date('25-FEB-22','DD-MON-RR'),'XYZ','USD',1.331292018904,to_timestamp('22-JAN-15 01.19.38.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST_TAB1 (EFFECTIVE_DT,CMPNY_CD,CURRENCY,EXCHNG_RT,LAST_CHNG) values (to_date('21-JUL-05','DD-MON-RR'),'XYZ','USD',1.31743626902,to_timestamp('22-JAN-15 01.19.38.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TEST_TAB1 (EFFECTIVE_DT,CMPNY_CD,CURRENCY,EXCHNG_RT,LAST_CHNG) values (to_date('22-JUL-05','DD-MON-RR'),'XYZ','USD',1.306421059507,to_timestamp('22-JAN-15 01.19.38.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
              
Insert into TEST_TAB2 (BUSINESS_DT,ORG_CRCY,ORG_CRCY_A,CMPNY_CD,FUNC_CRCY) values (to_date('28-FEB-22','DD-MON-RR'),'USD',50979531.06,'XYZ',0);
Insert into TEST_TAB2 (BUSINESS_DT,ORG_CRCY,ORG_CRCY_A,CMPNY_CD,FUNC_CRCY) values (to_date('28-FEB-22','DD-MON-RR'),'USD',1663875,'XYZ',0);
Insert into TEST_TAB2 (BUSINESS_DT,ORG_CRCY,ORG_CRCY_A,CMPNY_CD,FUNC_CRCY) values (to_date('28-FEB-22','DD-MON-RR'),'USD',-17354778.41,'XYZ',0);
Insert into TEST_TAB2 (BUSINESS_DT,ORG_CRCY,ORG_CRCY_A,CMPNY_CD,FUNC_CRCY) values (to_date('28-FEB-22','DD-MON-RR'),'USD',17216450,'XYZ',0);

优化方案 #

改写后的MERGE语句 #

优化后处理1700万+30万条数据耗时60秒：

MERGE INTO TEST_TAB2 wk USING (
select t2.CMPNY_CD,t2.org_crcy,t2.business_dt
,max(EXCHNG_RT) keep (dense_rank last order by effective_dt) rate
from TEST_TAB1 t1
join test_tab2 t2 on t1.cmpny_cd = t2.cmpny_cd and t1.currency = t2.org_crcy and t2.business_dt > t1.effective_dt
group by t2.CMPNY_CD,t2.org_crcy,t2.business_dt
) src ON ( wk.CMPNY_CD=src.CMPNY_CD and wk.org_crcy = src.org_crcy and wk.business_dt = src.business_dt)
WHEN MATCHED THEN UPDATE SET FUNC_CRCY = src.rate * wk.ORG_CRCY_A;

新增TEST_TAB2索引 #

CREATE INDEX TEST_TAB2_IDX1 ON TEST_TAB2 (CMPNY_CD, ORG_CRCY, BUSINESS_DT)
TABLESPACE APP_TS ; 

内容的提问来源于stack exchange，提问作者mradul