RE: merge into an empty table
Date: Wed, 6 Aug 2008 10:04:53 -0400
Message-ID: <7D34378E13274C80964560C2D2861491@rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of genegurevich_at_discover.com
Sent: Tuesday, August 05, 2008 5:21 PM
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 06 2008 - 09:04:53 CDT