If you have an idea of an equalish eight range split (8 since you're trying to use 8 as the degree of parallelism), then quite possibly running with parallelism 1 and running eight jobs in parallel with the various ranges of the using clause (likely a range of ACCT_NBR would be enough) *might* perform better. If table a is organized in physical order by ACCT_NBR the parallel jobs' blocks would not be competing dirty with each other, but it is doubtful that would be justification to do a reorg unless partitioning by ACCT_NBR ranges is a practical idea for you. To gauge whether 5 hours is a job/hash/bug artifact compared to your hardware, how long does a full index scan (output to dev null or something else fast and cheap) of a and b separately take? How long does an outer join (likewise to dev null) on the "on" key combination take? (either way or both ways, this is just to get a rough idea of the rate of a simple non-updating scan). Regards, mwf -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of genegurevich@xxxxxxxxxxxx Sent: Tuesday, August 05, 2008 5:21 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: merge into an empty table Hi all: I'm trying to tune a MERGE statement in oracle 10.2.0.3. I have 1 table that has about 150Mil rows and the other = about 18mil. The explain plan shows a hash buffer join outer. My pga is 5G. The merge has been running for about 5 hours before I killed it. I saw has join operation running very slowly with a dergree of parallelism set to 8.I was querying v$sess_longops table and the progress of that command was very slow On the host side I noticed was that my server was about 90% idle, 2% IO waits, no paging as far as I could see. So I don't see where the bottleneck is. I am wondering what can be done to speed this command up. I am attaching the SQL if it helps. Would appreciate any suggestions merge into capp.CRD_ACCT_RECM_RSPNS_SUM a using (select * from CAPP.STG_CRD_ACCT_RECM_RSPNS_SUM) b on (a.ACCT_NBR = b.ACCT_NBR and a.RECM_GRP_CDE = b.RECM_GRP_CDE and a.CHAN_CDE = b.CHAN_CDE and a.BUS_ORG_CDE = b.BUS_ORG_CDE and a.CNTCT_DRCTN_CDE = b.CNTCT_DRCTN_CDE and a.CNTCT_RSN_CDE = b.CNTCT_RSN_CDE and a.PREFR_IND = b.PREFR_IND) when matched then update set a.LST_DSPLY_TMS=b.LST_DSPLY_TMS, a.LST_ACCPT_TMS=b.LST_ACCPT_TMS, a.LST_SHRT_PTCH_DCLN_TMS=b.LST_SHRT_PTCH_DCLN_TMS, a.LST_LONG_PTCH_DCLN_TMS=b.LST_LONG_PTCH_DCLN_TMS, a.LST_DCLN_TMS=b.LST_DCLN_TMS, a.LST_TRSFR_TMS=b.LST_TRSFR_TMS when not matched then insert (a.ACCT_NBR, a.RECM_GRP_CDE, a.CHAN_CDE, a.BUS_ORG_CDE, a.CNTCT_DRCTN_CDE, a.CNTCT_RSN_CDE, a.PREFR_IND, a.LST_DSPLY_TMS, a.LST_ACCPT_TMS, a.LST_SHRT_PTCH_DCLN_TMS, a.LST_LONG_PTCH_DCLN_TMS, a.LST_DCLN_TMS, a.LST_TRSFR_TMS) values (b.ACCT_NBR, b.RECM_GRP_CDE, b.CHAN_CDE, b.BUS_ORG_CDE, b.CNTCT_DRCTN_CDE, b.CNTCT_RSN_CDE, b.PREFR_IND, b.LST_DSPLY_TMS, b.LST_ACCPT_TMS, b.LST_SHRT_PTCH_DCLN_TMS, b.LST_LONG_PTCH_DCLN_TMS, b.LST_DCLN_TMS, b.LST_TRSFR_TMS); thank you Gene Gurevich -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l