Re: merge into an empty table

From: <genegurevich_at_discover.com>
Date: Tue, 5 Aug 2008 16:21:26 -0500
Message-ID: <OF749BB046.79ED1E00-ON8625749C.00749D86-8625749C.007568DB@discover.com>

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

Received on Tue Aug 05 2008 - 16:21:26 CDT