Re: merge into an empty table
- From: <genegurevich@xxxxxxxxxxxx>
- To: riyaj.shamsudeen@xxxxxxxxx, "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Wed, 6 Aug 2008 10:59:03 -0500
Riyaj and Mark:
Thank you for your posts. I did not have time to try them, but what
appeared to help my command is /*+ append */
hint suggested by someone else on this list. I don't know how append works
with updates, but my merge completed
in about 95min after I added it (and analyzed one of the tables).
thank you
Gene Gurevich
Riyaj Shamsudeen
<riyaj.shamsudeen
@gmail.com> To
Sent by: genegurevich@xxxxxxxxxxxx
oracle-l-bounce@f cc
reelists.org oracle-l@xxxxxxxxxxxxx
Subject
Re: merge into an empty table
08/05/2008 11:02
PM
Please respond to
riyaj.shamsudeen@
gmail.com
Hi Gene
I was testing merge statement with smaller table. Looks like 'hash
join outer buffered' step is kicking in. Be aware that there are couple
of bugs with this. Search for 'hash join outer buffered' and one such
bug is 6855738. That bug aside, you might want to trace and see where
the time is spent. Also, since parallel slaves will be transferring
enormous amount of data, What value does your large_pool_size set to ?
PX buffers are stored in shared pool, if large pool is not appropriately
configured. Do you have statspack or AWR report when this query was
running ?
What is your parallel_execution_message_size set to? Value of dbfmrc?
Is there RAC involved here? inter-instance parallelism can cause few
performance issues too...
In some case, PQ hash distribution does not perform well. Try
switching to broadcast mode to see if you see any performance
improvement. Again ,better approach might be to check v$pq_tqstat, but I
believe, needs query to complete successfully.
Here is a small test case:
create table t1_crd as select n n1, n n2, n n3, lpad (n, 512,'x') v1
from (select level n from dual connect by level <10001);
exec dbms_stats.gather_table_stats(ownname => user, tabname => 't1_crd',
estimate_percent => 99);
create table t1_stg as select n n1, n n2, n n3, lpad (n, 512,'x') v1
from (select level n from dual connect by level <10001);
exec dbms_stats.gather_table_stats(ownname => user, tabname => 't1_stg',
estimate_percent => 99);
explain plan for
merge into t1_crd a using
(select * from t1_stg) b on
(a.n1 = b.n1 and
a.n2= b.n2 and
a.n3=b.n3)
when matched then update set a.v1=b.v1
when not matched then insert values
(b.n1, b.n2, b.n3, b.v1)
;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1070587476
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 9315 | 7750K|
61 (2)| 00:00:01 | | | |
| 1 | MERGE | T1_CRD | |
| | | | | |
| 2 | PX COORDINATOR | | |
| | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 9315 | 5512K|
61 (2)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 4 | VIEW | | |
| | | Q1,02 | PCWP | |
|* 5 | HASH JOIN OUTER BUFFERED| | 9315 | 5512K|
61 (2)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 9315 | 2701K|
30 (0)| 00:00:01 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 9315 | 2701K|
30 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 9315 | 2701K|
30 (0)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL | T1_STG | 9315 | 2701K|
30 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 10296 | 3106K|
30 (0)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 10296 | 3106K|
30 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 10296 | 3106K|
30 (0)| 00:00:01 | Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL | T1_CRD | 10296 | 3106K|
30 (0)| 00:00:01 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."N3"(+)="T1_STG"."N3" AND "A"."N2"(+)="T1_STG"."N2"
AND "A"."N1"(+)="T1_STG"."N1")
Note
-----
- dynamic sampling used for this statement
Adding pq_distribute hint to avoid bug:
explain plan for
merge /*+ parallel (a 8) parallel (b 8)
pq_distribute(a, None, Broadcast) pq_distribute(b, none,Broadcast)
*/ into t1_crd a using
(select * from t1_stg) b on
(a.n1 = b.n1 and
a.n2= b.n2 and
a.n3=b.n3)
when matched then update set a.v1=b.v1
when not matched then insert values
(b.n1, b.n2, b.n3, b.v1)
;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2161756169
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 9995 | 10M| 61
(2)| 00:00:01 | | | |
| 1 | MERGE | T1_CRD | |
| | | | | |
| 2 | PX COORDINATOR | | |
| | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 9995 | 9M| 61
(2)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 4 | VIEW | | |
| | | Q1,01 | PCWP | |
|* 5 | HASH JOIN OUTER | | 9995 | 9M| 61
(2)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 9995 | 5114K| 30
(0)| 00:00:01 | Q1,01 | PCWC | |
| 7 | TABLE ACCESS FULL | T1_STG | 9995 | 5114K| 30
(0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | |
| | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | 10002 | 5118K| 30
(0)| 00:00:01 | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | 10002 | 5118K| 30
(0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 11 | PX BLOCK ITERATOR | | 10002 | 5118K| 30
(0)| 00:00:01 | Q1,00 | PCWC | |
| 12 | TABLE ACCESS FULL| T1_CRD | 10002 | 5118K| 30
(0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."N3"(+)="T1_STG"."N3" AND "A"."N2"(+)="T1_STG"."N2"
AND "A"."N1"(+)="T1_STG"."N1")
24 rows selected.
Cheers
Riyaj
The Pythian Group www.pythian.com/blogs
blog: http://orainternals.wordpress.com
genegurevich@xxxxxxxxxxxx wrote:
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
