Re: merge into an empty table
- From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx
- Date: Tue, 05 Aug 2008 23:02:35 -0500
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
Other related posts:
