Hi All,
I have merge statement with just update in it and it never executes just keep on running and running. I have also separately execute the SRC query in it and seems like that query also never got executed there are only 2 tables involves in it. Below are sample script with table structures, Merge Statement and explain Plan for it.
there are Wait events which got from Monitor Session for 3 runs (which never completes)
gc cr multi block mixed, library cache pin, pga memory operation
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 ;
Row Count in 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 ;
Row Count in TEST_TAB2 -- 300711
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
Please If you see any scope to Tune it please let me know I will Try.
I have merge statement with just update in it and it never executes just keep on running and running. I have also separately execute the SRC query in it and seems like that query also never got executed there are only 2 tables involves in it. Below are sample script with table structures, Merge Statement and explain Plan for it.
there are Wait events which got from Monitor Session for 3 runs (which never completes)
gc cr multi block mixed, library cache pin, pga memory operation
Please If you see any scope to Tune it please let me know I will Try.
****EDIT SAMPLE DATA ****
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);