SQL & PL/SQL

Hi gems...good evening.

My test database is 11.2.0.2 and the platform is Redhat Linux el6.

I have two tables named ASSET_SUMMARY (175738 rows) and TEMP_ASSET_SUMMARY(25397 rows).

After some processing of some variables, I am merging those two tables on some conditions.

I know the data volume is huge, but it is taking couple of hours to execute...This will never be acceptable.

MERGE INTO ASSET_SUMMARY p
           USING TEMP_ASSET_SUMMARY t
           ON (p.banker = t.t_banker
               AND p.QURTREND = var_qtrend
               AND p.MONTH = var_month
               AND p.halfyrend= var_halfyrend
               AND p.book_typ=t.book_typ
               AND p.product_typ=t.product_typ
               AND (p.cust_part= t.cust_part or (p.cust_part is null and t.cust_part is null ))
               AND (p.group=t.group OR (p.group is null and t.group is null)))
             WHEN MATCHED THEN
                 UPDATE
                 set p.asset = t.asset,
                     p.investment = t.investment,
                     p.fees=t.fees,
                     p.TRAIL_FEES=t.t_trail_comm,
                     p.unit=t.unit,
                     p.report=t.t_report
             WHEN NOT MATCHED THEN
                    INSERT (p.banker,  p.QURTREND, p.MONTH, p.book_typ,p.product_typ,p.cust_part,p.group, p.asset,p.investment,P.fees,p.unit,p.report)
                    values(t.t_banker_id, var_qtrend, var_month, t.book_typ, t.product_typ,t.cust_part, t.group, t.asset,t.investment,t.fees,t.unit,t.report);

I have checked the V$SESSION view EVENT column. It is showing LATCH FREE wait event and sometimes LATCH:CACHE BUFFER CHAINS wait event most of the time.

What can I do in this case to improve the performance...please help..thanks in advance...