We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.
MERGE INTO HDFCMPR.MPR_TB_MPRMASTER
USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */
MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF,
FROM APP_MRCH_DETHOLD A, MSTBL_TEMP_PG B
WHERE A.HD_APPCODE = AUTH_CODE
AND A.TERM_SEQ_NO = REF_ID
AND TXN_CATEGORY ='ACQ'
and REF_ID IS NOT NULL) TAB1
ON (TAB1.ID = MPR_TB_MPRMASTER.ID)
WHEN MATCHED THEN
SET MERCHANT_TRACKID = TAB1.MER_TRACKID, TRAN_ID = TAB1.TRANID,
UPVALUE = TAB1.UCAF
Why the following hints /*+ USE_HASH(A,B) FULL(A) FULL(B) */ were put in place?
Do the tables involved have good statistics at all times? If they do, you should consider removing the hints – Oracle should be able to get to a good execution plan by itself.