You Asked

Below pkg proc execution time is 2-4 hrs.Pls helpto identify areas of improvement

explplan for update stmt :
"STATEMENT_ID","PLAN_ID","TIMESTAMP","REMARKS","OPERATION","OPTIONS","OBJECT_NODE","OBJECT_OWNER","OBJECT_NAME","OBJECT_ALIAS","OBJECT_INSTANCE","OBJECT_TYPE","OPTIMIZER","SEARCH_COLUMNS","ID","PARENT_ID","DEPTH","POSITION","COST","CARDINALITY","BYTES","OTHER_TAG","PARTITION_START","PARTITION_STOP","PARTITION_ID","OTHER","OTHER_XML","DISTRIBUTION","CPU_COST","IO_COST","TEMP_SPACE","ACCESS_PREDICATES","FILTER_PREDICATES","PROJECTION","TIME","QBLOCK_NAME"
"abc",4819,25-MAY-2022,"","UPDATE STATEMENT","","","","","",,"","ALL_ROWS",,0,,0,122105,122105,1622424,121681800,"","","",,"","","",10320898911,121778,,"","","",5,""
"abc",4819,25-MAY-2022,"","UPDATE","","","XRR","COMMERCIAL_LENDING","",,"","",,1,0,1,1,,,,"","","",,"","<other_xml><info type=""nodeid/pflags"">3 1</info><info type=""nodeid/pflags"">3 1</info><info type=""nodeid/pflags"">2 1</info><info type=""db_version"">12.2.0.1</info><info type=""parse_schema""><![CDATA[""XRR""]]></info><info type=""plan_hash_full"">696193857</info><info type=""plan_hash"">3786215340</info><info type=""plan_hash_2"">696193857</info><outline_data><hint><![CDATA[FULL(@""UPD$1"" ""COMMERCIAL_LENDING""@""UPD$1"")]]></hint><hint><![CDATA[OUTLINE_LEAF(@""UPD$1"")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[DB_VERSION('12.2.0.1')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('12.2.0.1')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>","",,,,"","","",,"UPD$1"
"abc",4819,25-MAY-2022,"","PARTITION LIST","SINGLE","","","","",,"","",,2,1,2,1,122105,1622424,121681800,"","KEY","KEY",2,"","","",10320898911,121778,,"","","(upd=6,7; cmp=3) ""COMMERCIAL_LENDING"".ROWID[ROWID,10], ""PRDS_CP_IMMED_NAME""[VARCHAR2,255], ""CRR_P

PROCEDURE P_UPDATE_DSTS (
      arguments...
       
      )
IS
    <declare variables section >
BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';    -- 

    <assign variable section >
    OVERRIDESQL := 'MERGE INTO ' || DSTTABLE || ' DST ';

    OVERRIDESQL := OVERRIDESQL
        ||chr(10)||'USING ( SELECT  LKP.PRDS_IP_ID
    ,LKP.LINE_COUNTERPARTY
    ,''CPOverride''   LINE_COUNTERPARTY_WIN
    FROM LOOKUP.REF_COUNTERPARTY_OVERRIDE LKP
    WHERE LKP.FSBR_EFFECTIVE_end_TIMESTAMP = TO_DATE (''12/31/9999'', ''MM/DD/YYYY'')) LKP ';
    OVERRIDESQL := OVERRIDESQL
        ||chr(10)||'ON (
  DST.PRDS_CP_IMMED_IP_ID   = LKP.PRDS_IP_ID
  AND LKP.PRDS_IP_ID IS NOT NULL
  AND LKP.LINE_COUNTERPARTY IS NOT NULL
 )
WHEN MATCHED THEN
UPDATE SET  DST.LINE_COUNTERPARTY  = LKP.LINE_COUNTERPARTY
   ,DST.LINE_COUNTERPARTY_WIN = LKP.LINE_COUNTERPARTY_WIN
WHERE  SET_CODE = '''|| p_SETCODE|| '''
AND  CYCLE_ID = '|| TO_CHAR (p_CYCLEID);

    IF UPPER (p_LINEPRD) = 'Y'
    THEN
        IF (X123FIELINCLAUSE IS NULL)
        THEN
            X123FIELINCLAUSE := '''LINE_PRODUCT''';
        ELSE
            X123FIELINCLAUSE := X123FIELINCLAUSE || ',''LINE_PRODUCT''';
        END IF;
    ELSE
        IF (X123FIELNOTINCLAUSE IS NULL)
        THEN
            X123FIELNOTINCLAUSE := '''LINE_PRODUCT''';
        ELSE
            X123FIELNOTINCLAUSE := X123FIELNOTINCLAUSE || ',''LINE_PRODUCT''';
        END IF;
    END IF;

    IF UPPER (p_LINECOLT) = 'Y'
    THEN
        IF (X123FIELINCLAUSE IS NULL)
        THEN
            X123FIELINCLAUSE := '''LINE_COLLATERAL''';
        ELSE
            X123FIELINCLAUSE := X123FIELINCLAUSE || ',''LINE_COLLATERAL''';
        END IF;
    ELSE
        IF (X123FIELNOTINCLAUSE IS NULL)
        THEN
            X123FIELNOTINCLAUSE := '''LINE_COLLATERAL''';
        ELSE
            X123FIELNOTINCLAUSE :=
                X123FIELNOTINCLAUSE || ',''LINE_COLLATERAL''';
        END IF;
    END IF;

    IF UPPER (p_LINEPURP) = 'Y'
    THEN
        IF (X123FIELINCLAUSE IS NULL)
        THEN
            X123FIELINCLAUSE := '''LINE_PURPOSE''';
        ELSE
            X123FIELINCLAUSE := X123FIELINCLAUSE || ',''LINE_PURPOSE''';
        END IF;
    ELSE
        IF (X123FIELNOTINCLAUSE IS NULL)
        THEN
            X123FIELNOTINCLAUSE := '''LINE_PURPOSE''';
        ELSE
            X123FIELNOTINCLAUSE := X123FIELNOTINCLAUSE || ',''LINE_PURPOSE''';
        END IF;
    END IF;

    IF UPPER (p_LINEGRNTR) = 'Y'
    THEN
        IF (X123FIELINCLAUSE IS NULL)
        THEN
            X123FIELINCLAUSE := '''LINE_GUARANTOR''';
        ELSE
            X123FIELINCLAUSE := X123FIELINCLAUSE || ',''LINE_GUARANTOR''';
        END IF;
    ELSE
        IF (X123FIELNOTINCLAUSE IS NULL)
        THEN
            X123FIELNOTINCLAUSE := '''LINE_GUARANTOR''';
        ELSE
            X123FIELNOTINCLAUSE := X123FIELNOTINCLAUSE || ',''LINE_GUARANTOR''';
        END IF;
    END IF;

    IF UPPER (p_LINECP) = 'Y'
    THEN
        IF (X123FIELINCLAUSE IS NULL)
        THEN
            X123FIELINCLAUSE := '''LINE_COUNTERPARTY''';
        ELSE
            X123FIELINCLAUSE := X123FIELINCLAUSE || ',''LINE_COUNTERPARTY''';
        END IF;
    ELSE
        IF (X123FIELNOTINCLAUSE IS NULL)
        THEN
            X123FIELNOTINCLAUSE := '''LINE_COUNTERPARTY''';
        ELSE
            X123FIELNOTINCLAUSE :=
                X123FIELNOTINCLAUSE || ',''LINE_COUNTERPARTY''';
        END IF;
    END IF;
 
    IF UPPER (p_LINEIMSEC) = 'Y'
    THEN
        IF (X123FIELINCLAUSE IS NULL)
        THEN
            X123FIELINCLAUSE := '''X123_CP_IMMED_SECTOR''';
        ELSE
            X123FIELINCLAUSE := X123FIELINCLAUSE || ',''X123_CP_IMMED_SECTOR''';
        END IF;
    ELSE
        IF (X123FIELNOTINCLAUSE IS NULL)
        THEN
            X123FIELNOTINCLAUSE := '''X123_CP_IMMED_SECTOR''';
        ELSE
            X123FIELNOTINCLAUSE :=
                X123FIELNOTINCLAUSE || ',''X123_CP_IMMED_SECTOR''';
        END IF;
    END IF;
 --
     --
    SQLSELECT := 'SELECT DISTINCT X123_PRODUCT_SET
FROM '|| DSTTABLE||'
WHERE CYCLE_ID = '|| TO_CHAR(p_CYCLEID)||'
AND  SET_CODE = '''|| p_SETCODE ||'''
ORDER BY X123_PRODUCT_SET' ;

    OPEN DSTROWNUM FOR SQLSELECT;  -- DSTROWNUM ;
 LOOP
     FETCH DSTROWNUM INTO PRODUCTSET;

        EXIT WHEN DSTROWNUM%NOTFOUND;


  
  LOOPINDEXCNT := LOOPINDEXCNT + 1;


  SQLSTRING := '';
  SQLSTRINGMapping := '';
  SUBSTRING1 := '';
  SUBSTRING11 := '';


  SQLSTRINGMapping := 'SELECT  MAP.INSTRUCTION_CODE
   ,UPPER(trim(MAP.X123_FIELD))
   ,MAP.INSTRUCTION_ORDER, MAP.SOURCE_FIELD, UPPER(MAP.SOURCE_VALUE)
   ,CASE
    WHEN UPPER(TRIM(NVL(X123_CONDITION,''NULL''))) = ''INCLUDE''
     THEN ''IN''
    WHEN UPPER(TRIM(NVL(X123_CONDITION,''NULL'')))  = ''EXCLUDE''
     THEN ''NOT IN''
    WHEN UPPER(TRIM(NVL(X123_CONDITION,''NULL'')))  = ''NULL''
     THEN ''IS NULL''
    WHEN UPPER(TRIM(NVL(X123_CONDITION,''NULL'')))  = ''CONTAINS''
     THEN ''LIKE''';
  
  SQLSTRINGMapping := SQLSTRINGMapping||'
    ELSE UPPER(TRIM(X123_CONDITION))
    END                       AS CONDITIONOPERATOR
   ,MAP.X123_VALUE
   ,NVL(COALESCE(DEF_NONLNL.X123_VALUE_DEFAULT,DEF_LNL.X123_VALUE_DEFAULT), ''NotFound'')   X123_VALUE_DEFAULT_COL
   ,NVL(COALESCE(DEF_NONLNL.X123_VALUE_WIN_DEFAULT,DEF_LNL.X123_VALUE_WIN_DEFAULT), ''NotFound'') X123_VALUE_WIN_DEF_COL';

  SQLSTRINGMapping := SQLSTRINGMapping||'
 FROM LOOKUP.REF_LINEITEM_MAPPING MAP
    LEFT OUTER JOIN LOOKUP.REF_LINEITEM_MAPPING_DEFAULTS DEF_LNL
     ON  UPPER(trim(MAP.X123_FIELD))   = UPPER(trim(DEF_LNL.X123_FIELD))
     AND  DEF_LNL.X123_PRODUCT_SET    IS NULL
     AND  MAP.FSBR_EFFECTIVE_END_TIMESTAMP = DEF_LNL.FSBR_EFFECTIVE_END_TIMESTAMP
    LEFT OUTER JOIN LOOKUP.REF_LINEITEM_MAPPING_DEFAULTS DEF_NONLNL
     ON  UPPER(trim(MAP.X123_FIELD))   = UPPER(trim(DEF_NONLNL.X123_FIELD))
     AND  MAP.X123_PRODUCT_SET     = DEF_NONLNL.X123_PRODUCT_SET
     AND  MAP.FSBR_EFFECTIVE_END_TIMESTAMP = DEF_NONLNL.FSBR_EFFECTIVE_END_TIMESTAMP
     AND  DEF_NONLNL.X123_WRITE_DEFAULT_IND = ''Y''
 WHERE MAP.FSBR_EFFECTIVE_end_TIMESTAMP = TO_DATE (''12/31/9999'', ''MM/DD/YYYY'')  ';

  IF PRODUCTSET IS NOT NULL
  THEN
   SQLSTRINGMapping := SQLSTRINGMapping||chr(10)||'AND  MAP.X123_PRODUCT_SET IN ('''||PRODUCTSET||''',''PRDS'')';
  ELSE
   SQLSTRINGMapping := SQLSTRINGMapping||chr(10)||'AND  MAP.X123_PRODUCT_SET IN (''PRDS'')';
  END IF;

  IF (LENGTH (TRIM (X123FIELINCLAUSE)) > 0)
  THEN
   SQLSTRINGMapping := SQLSTRINGMapping||chr(10)||'AND  MAP.X123_FIELD IN ('|| X123FIELINCLAUSE|| ')';
  END IF;

  IF (LENGTH (TRIM (X123FIELNOTINCLAUSE)) > 0)
  THEN
   SQLSTRINGMapping := SQLSTRINGMapping||chr(10)||'AND  MAP.X123_FIELD NOT IN ('|| X123FIELNOTINCLAUSE|| ')';
  END IF;

  SQLSTRINGMapping := SQLSTRINGMapping||chr(10)||'AND ROWNUM < 11 ORDER BY MAP.X123_FIELD, INSTRUCTION_ORDER, SOURCE_FIELD, UPPER(SOURCE_VALUE) ';

  OPEN ROWNUM FOR SQLSTRINGMapping;
  
  dbms_output.put_line('elapsed time 1 = '|| dbms_utility.get_cpu_time - l_start);

  LOOP
   FETCH ROWNUM
    INTO INSTRUCTIONCODE,
      X123FIELD,
      INSTRUCTIONORDER,
      SOURCEFIELD,
      SOURCEVALUE,
      X123CONDITION,
      X123VALUE,
      X123VALUEDEFAULT,
      X123VALUEWINDEFAULT;

   EXIT WHEN ROWNUM%NOTFOUND;

   IF X123FIELD = X123FIELDCOMP
   THEN
    IF INSTRUCTIONORDER = INSTRUCTIONORDERCOMP
    THEN
     IF SOURCEFIELD = SOURCEFIELDCOMP
     THEN
      SUBSTRING1 := SUBSTRING1 || ',''' || SOURCEVALUE || '''';
      SUBSTRING11 :=
       SUBSTRING11 || ',''' || SOURCEVALUE || '''';
     ELSE
      IF PREVCONDITION IN ('IN', 'NOT IN')
      THEN
       SUBSTRING1 := SUBSTRING1 || ') ';
       SUBSTRING11 := SUBSTRING11 || ') ';
      END IF;

      IF X123CONDITION IN ('IS NULL', 'IS NOT NULL')
      THEN
       SUBSTRING1 :=
           SUBSTRING1
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || X123CONDITION
        || ' ';
       SUBSTRING11 :=
           SUBSTRING11
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || X123CONDITION
        || ' ';
      ELSIF X123CONDITION = 'LIKE'
      THEN           -- BEGINS WITH
       SUBSTRING1 :=
           SUBSTRING1
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || X123CONDITION
        || ' ''%'
        || SOURCEVALUE
        || '%'' ';
       SUBSTRING11 :=
           SUBSTRING11
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || X123CONDITION
        || ' ''%'
        || SOURCEVALUE
        || '%'' ';
      ELSIF X123CONDITION = 'BEGINS'
      THEN           -- BEGINS WITH
       SUBSTRING1 :=
           SUBSTRING1
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || 'LIKE'
        || ' '''
        || SOURCEVALUE
        || '%'' ';
       SUBSTRING11 :=
           SUBSTRING11
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || 'LIKE'
        || ' '''
        || SOURCEVALUE
        || '%'' ';
      ELSIF X123CONDITION = 'ENDS'
      THEN           -- BEGINS WITH
       SUBSTRING1 :=
           SUBSTRING1
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || 'LIKE'
        || ' ''%'
        || SOURCEVALUE
        || ''' ';
       SUBSTRING11 :=
           SUBSTRING11
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || 'LIKE'
        || ' ''%'
        || SOURCEVALUE
        || ''' ';
      ELSE
       SUBSTRING1 :=
           SUBSTRING1
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || X123CONDITION
        || ' ('''
        || SOURCEVALUE
        || '''';
       SUBSTRING11 :=
           SUBSTRING11
        || ' AND UPPER(TRIM('
        || SOURCEFIELD
        || ')) '
        || X123CONDITION
        || ' ('''
        || SOURCEVALUE
        || '''';
      END IF;
     END IF;

     PREVCONDITION := X123CONDITION;
    ELSE
     IF PREVCONDITION IN ('IN', 'NOT IN')
     THEN
      SUBSTRING1 := SUBSTRING1 || ') ';
      SUBSTRING11 := SUBSTRING11 || ') ';
     END IF;

     SQLSTRING := SQLSTRING || SUBSTRING1 || SUBSTRING2;
     SQLSTRING2 := SQLSTRING2 || SUBSTRING11 || SUBSTRING12;

     IF X123CONDITION IN ('IS NULL', 'IS NOT NULL')
     THEN
      SUBSTRING1 :=
       'WHEN UPPER(TRIM(' || SOURCEFIELD || ')) ' || X123CONDITION || ' ';
      SUBSTRING11 :=
       'WHEN UPPER(TRIM(' || SOURCEFIELD || ')) ' || X123CONDITION || ' ';
     ELSIF X123CONDITION = 'LIKE'
     THEN
      SUBSTRING1 :=
          'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || X123CONDITION
       || ' ''%'
       || SOURCEVALUE
       || '%'' ';
      SUBSTRING11 :=
       'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || X123CONDITION
       || ' ''%'
       || SOURCEVALUE
       || '%'' ';
     ELSIF X123CONDITION = 'BEGINS'
     THEN             -- BEGINS WITH
      SUBSTRING1 :=
         'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || 'LIKE'
       || ' '''
       || SOURCEVALUE
       || '%'' ';
      SUBSTRING11 :=
         'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || 'LIKE'
       || ' '''
       || SOURCEVALUE
       || '%'' ';
     ELSIF X123CONDITION = 'ENDS'
     THEN             -- BEGINS WITH
      SUBSTRING1 :=
          'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || 'LIKE'
       || ' ''%'
       || SOURCEVALUE
       || ''' ';
      SUBSTRING11 :=
         'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || 'LIKE'
       || ' ''%'
       || SOURCEVALUE
       || ''' ';
     ELSE
      SUBSTRING1 :=
         'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || X123CONDITION
       || ' ('''
       || SOURCEVALUE
       || '''';
      SUBSTRING11 :=
          'WHEN UPPER(TRIM('
       || SOURCEFIELD
       || ')) '
       || X123CONDITION
       || ' ('''
       || SOURCEVALUE
       || '''';
     END IF;

     SUBSTRING2  := 'THEN ''' || X123VALUE || ''' ';
     SUBSTRING12  := 'THEN ''' || INSTRUCTIONCODE || ''' ';
     PREVCONDITION := X123CONDITION;
    END IF;
   ELSE
    IF PREVCONDITION IN ('IN', 'NOT IN')
    THEN
     SUBSTRING1 := SUBSTRING1 || ') ';
     SUBSTRING11 := SUBSTRING11 || ') ';
    END IF;

    SUBSTRING0 := X123FIELD || ' = ';
    SUBSTRING10 := X123FIELD || '_WIN = ';
    SQLSTRING :=
        SQLSTRING
     || SUBSTRING1
     || SUBSTRING2
     || SUBSTRING3
     || SUBSTRING0;
    SQLSTRING2 :=
        SQLSTRING2
     || SUBSTRING11
     || SUBSTRING12
     || SUBSTRING13
     || SUBSTRING10;

    IF X123CONDITION IN ('IS NULL', 'IS NOT NULL')
    THEN
     SUBSTRING1 :=
      'CASE WHEN UPPER(TRIM(' || SOURCEFIELD || ')) ' || X123CONDITION || ' ';
     SUBSTRING11 :=
      'CASE WHEN UPPER(TRIM(' || SOURCEFIELD || ')) ' || X123CONDITION || ' ';
    ELSIF X123CONDITION = 'LIKE'
    THEN           -- CONTAINS WITH THEN
     SUBSTRING1 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' ''%'
      || SOURCEVALUE
      || '%'' ';
     SUBSTRING11 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' ''%'
      || SOURCEVALUE
      || '%'' ';
    ELSIF X123CONDITION = 'BEGINS'
    THEN           -- BEGINS WITH THEN
     SUBSTRING1 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' '''
      || SOURCEVALUE
      || '%'' ';
     SUBSTRING11 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' '''
      || SOURCEVALUE
      || '%'' ';
    ELSIF X123CONDITION = 'ENDS'
    THEN              -- BEGINS WITH THEN
     SUBSTRING1 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' ''%'
      || SOURCEVALUE
      || ''' ';
     SUBSTRING11 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' ''%'
      || SOURCEVALUE
      || ''' ';
    ELSE
     SUBSTRING1 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' ('''
      || SOURCEVALUE
      || '''';
     SUBSTRING11 :=
         'CASE WHEN UPPER(TRIM('
      || SOURCEFIELD
      || ')) '
      || X123CONDITION
      || ' ('''
      || SOURCEVALUE
      || '''';
    END IF;

    SUBSTRING2  := 'THEN ''' || X123VALUE || ''' ';
    SUBSTRING12  := 'THEN ''' || INSTRUCTIONCODE || ''' ';
    --
    -- Two spaces character after END are important for update to select query conversion
    --
    SUBSTRING3  := 'ELSE '''||X123VALUEDEFAULT||''' END  , ';
    --|| X123FIELD || ', ';
    SUBSTRING13  := 'ELSE '''||X123VALUEWINDEFAULT||''' END  , ';
   END IF;

   PREVCONDITION   := X123CONDITION;
   X123FIELDCOMP    := X123FIELD;
   INSTRUCTIONORDERCOMP := INSTRUCTIONORDER;
   SOURCEFIELDCOMP   := SOURCEFIELD;
  END LOOP;

  CLOSE ROWNUM;
  
  dbms_output.put_line('elapsed time 2 = '|| dbms_utility.get_cpu_time - l_start);

  IF PREVCONDITION IN ('IN', 'NOT IN')
  THEN
   SUBSTRING1 := SUBSTRING1 || ') ';
   SUBSTRING11 := SUBSTRING11 || ') ';
  END IF;

  SQLSTRING := SQLSTRING || SUBSTRING1 || SUBSTRING2;
  SQLSTRING2 := SQLSTRING2 || SUBSTRING11 || SUBSTRING12;

  IF NVL(SQLSTRING, 'NULL') <> 'NULL'
  THEN
   SQLSTRING :=
       'UPDATE '
    || DSTTABLE
    || ' SET '
    || SQLSTRING
    || ' ELSE '''
    || X123VALUEDEFAULT
    ||''' END  ';
   SQLSTRING := SQLSTRING || ', ';
   -- Two spaces character after END are important for update to select query conversion
   SQLSTRING := SQLSTRING || SQLSTRING2 || ' ELSE '''||X123VALUEWINDEFAULT||''' END  ';
   CONVERTUPDTOSELECT := REPLACE(SQLSTRING,'UPDATE '||DSTTABLE||' SET','SELECT ');

   if regexp_instr(CONVERTUPDTOSELECT,'LINE_COLLATERAL = ') > 0 THEN
   CONVERTUPDTOSELECT := regexp_replace(CONVERTUPDTOSELECT
          ,'END  ',' END AS LINE_COLLATERAL  ',1,1);
   end if;
CONVERTUPDTOSELECT := CONVERTUPDTOSELECT
                              || ' FROM '||DSTTABLE
                              || ' WHERE X123_PRODUCT_SET IN ('''|| PRODUCTSET|| ''')'
         || ' AND CYCLE_ID = '|| To_Char(p_CycleID)
                              || ' AND SET_CODE = '''|| p_SetCode||'';

        SQLSTRING :=
               SQLSTRING
            || ' WHERE X123_PRODUCT_SET IN ('''
            || PRODUCTSET
            || ''') AND CYCLE_ID = '
            || To_Char(p_CycleID)
            || ' AND SET_CODE = '''
            || p_SetCode
            || '''';
   EXECUTE IMMEDIATE SQLSTRING;
            COMMIT;
   
   dbms_output.put_line('elapsed time 3 = '|| dbms_utility.get_cpu_time - l_start);

   IF SQL%FOUND
   THEN
    NUMOFRECUPDATE := SQL%ROWCOUNT;
   END IF;

   o_Message :=
    ' Total number of records updated: ' || TO_CHAR (NUMOFRECUPDATE);

     IF UPPER (p_LINECP) = 'Y'  AND LOOPINDEXCNT = DSTROWNUM%ROWCOUNT
   THEN
    -- ===eXECUTE OVERRIDE SQL

                EXECUTE IMMEDIATE OVERRIDESQL || ' AND DST.X123_PRODUCT_SET = ''' || PRODUCTSET || '''';
                COMMIT;
    
    dbms_output.put_line('override sql '||OVERRIDESQL );
    dbms_output.put_line('elapsed time 4 = '|| dbms_utility.get_cpu_time - l_start);

   END IF;
   ELSE
   o_Message :=
    ' No record found in mapping table for provided parameters ';
   END IF;
   
 END LOOP;
 close DSTROWNUM;
EXCEPTION
 WHEN OTHERS THEN -- handles all other errors
 ...
END;

and Connor said...

So this is the *smallest* test case ?

Lets start with
- show us the final MERGE statement that gets generated
- run that
- then show us the output of

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST +PEEKED_BINDS'))

after it has been run.

Then we'll try help you via followup Comments

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Followup on performance tuning in dyanmic sql statement

Manoj Gurnani, May 27, 2022 - 4:57 pm UTC

merge statement generated at run time:

MERGE INTO XRR.COMMERCIAL_LENDING DST  USING (SELECT LKP.PRDS_IP_ID 
,LKP.LINE_COUNTERPARTY ,'CPOverride' LINE_COUNTERPARTY_WIN FROM 
LOOKUP.REF_COUNTERPARTY_OVERRIDE LKP WHERE 
LKP.FSBR_EFFECTIVE_end_TIMESTAMP = TO_DATE ('12/31/9999', 
'MM/DD/YYYY')) LKP  ON( DST.PRDS_CP_IMMED_IP_ID= LKP.PRDS_IP_ID AND 
LKP.PRDS_IP_ID IS NOT NULL AND LKP.LINE_COUNTERPARTY IS NOT NULL ) WHEN 
MATCHED THEN UPDATE SET DST.LINE_COUNTERPARTY= LKP.LINE_COUNTERPARTY 
,DST.LINE_COUNTERPARTY_WIN= LKP.LINE_COUNTERPARTY_WIN WHERE  SET_CODE = 
'/peakscom/exposure' AND CYCLE_ID= 1077

Plan hash value: 1824491629

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | 122K(100)| | | |
| 1 | MERGE | COMMERCIAL_LENDING | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 39002 | 122K (1)| 2740K| 2740K| 6539K (0)|
| 4 | PARTITION LIST ALL | | 2120 | 946 (1)| | | |
|* 5 | TABLE ACCESS STORAGE FULL | REF_COUNTERPARTY_OVERRIDE | 2120 | 946 (1)| 1025K| 1025K| |
| 6 | PARTITION LIST ALL | | 3725K| 121K (1)| | | |
| 7 | PARTITION LIST SINGLE | | 3725K| 121K (1)| | | |
| 8 | TABLE ACCESS STORAGE FULL| COMMERCIAL_LENDING | 3725K| 121K (1)| 1025K| 1025K| |
---------------------------------------------------------------------------------------------------------------------

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

3 - access("DST"."PRDS_CP_IMMED_IP_ID"="LKP"."PRDS_IP_ID")
5 - storage(("LKP"."LINE_COUNTERPARTY" IS NOT NULL AND "LKP"."FSBR_EFFECTIVE_END_TIMESTAMP"=TIMESTAMP'
9999-12-31 00:00:00'))
filter(("LKP"."LINE_COUNTERPARTY" IS NOT NULL AND "LKP"."FSBR_EFFECTIVE_END_TIMESTAMP"=TIMESTAMP'
9999-12-31 00:00:00'))

May 31, 2022 - 1:54 am UTC

We need this

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST +PEEKED_BINDS'))

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Followup on performance tuning in dyanmic sql statement

More to Explore

Performance

Performance tuning of dynamic sql statements

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Followup on performance tuning in dyanmic sql statement

More to Explore

Performance