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




--
http://www.freelists.org/webpage/oracle-l

Other related posts: