Re: merge into an empty table
Date: Tue, 05 Aug 2008 23:02:35 -0500
Message-ID: <4899225B.2050200@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_at_discover.com 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 > > > > > -- > http://www.freelists.org/webpage/oracle-l > > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 05 2008 - 23:02:35 CDT