RE: merge into an empty table
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <genegurevich@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Aug 2008 10:04:53 -0400
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
Other related posts:
