drop table tgt; drop table stg; create table tgt(cust int, prd int, dt date, num int) partition by range (dt) subpartition by hash (prd) (partition p1 values less than (to_date(20120101,'yyyymmdd')) subpartitions 4,partition pmax values less than (maxvalue) subpartitions 4); insert into tgt values(1,1,to_date(20110101,'yyyymmdd'),1); create table stg(cust int, prd int, dt date, num int) partition by hash (prd) partitions 4 parallel 4; insert into stg select level, level, trunc(sysdate), level from dual connect by level < 3; select * from stg; 1 1 29.04.2012 00:00:00 1 2 2 29.04.2012 00:00:00 2 select * from tgt; 1 1 01.01.2011 00:00:00 1 alter session set statistics_level = all; merge into tgt using (select cust, prd, dt, num from stg) sou on (tgt.dt=sou.dt and tgt.cust=sou.cust and tgt.prd=sou.prd) when matched then update set tgt.num=tgt.num+sou.num when not matched then insert (tgt.cust,tgt.prd,tgt.dt,tgt.num) values(sou.cust,sou.cust,sou.dt,sou.num) ;Just ask how did it execute...
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.02 | 84 |
| 1 | MERGE | TGT | 1 | | 0 |00:00:00.02 | 84 |
| 2 | PX COORDINATOR | | 1 | | 4 |00:00:00.02 | 24 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 4 | 0 |00:00:00.01 | 0 |
| 4 | VIEW | | 0 | | 0 |00:00:00.01 | 0 |
| 5 | NESTED LOOPS OUTER | | 0 | 4 | 0 |00:00:00.01 | 0 |
| 6 | PX BLOCK ITERATOR | | 0 | 4 | 0 |00:00:00.01 | 0 |
|* 7 | TABLE ACCESS FULL | STG | 0 | 4 | 0 |00:00:00.01 | 0 |
| 8 | PARTITION RANGE ITERATOR| | 0 | 1 | 0 |00:00:00.01 | 0 |
| 9 | PARTITION HASH ITERATOR| | 0 | 1 | 0 |00:00:00.01 | 0 |
|* 10 | TABLE ACCESS FULL | TGT | 0 | 1 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
10 - filter(("TGT"."PRD"="PRD" AND "TGT"."CUST"="CUST" AND "TGT"."DT"="DT"))
Well that did not tell us anything about partitioning. How about without allstats.
merge into tgt
using (select cust, prd, dt, num from stg) sou
on (tgt.dt=sou.dt and tgt.cust=sou.cust and tgt.prd=sou.prd)
when matched then update set tgt.num=tgt.num+sou.num
when not matched then insert (tgt.cust,tgt.prd,tgt.dt,tgt.num)
values(sou.cust,sou.cust,sou.dt,sou.num)
;
select * from table(dbms_xplan.display_cursor());
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 4 (100)| | | | | | |
| 1 | MERGE | TGT | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 16 | 1728 | 4 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | VIEW | | | | | | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS OUTER | | 16 | 1728 | 4 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 6 | PX BLOCK ITERATOR | | 16 | 768 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWC | |
|* 7 | TABLE ACCESS FULL | STG | 16 | 768 | 2 (0)| 00:00:01 | 1 | 8 | Q1,00 | PCWP | |
| 8 | PARTITION RANGE ITERATOR| | 1 | 60 | 1 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
| 9 | PARTITION HASH ITERATOR| | 1 | 60 | 1 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
|* 10 | TABLE ACCESS FULL | TGT | 1 | 60 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
10 - filter(("TGT"."PRD"="PRD" AND "TGT"."CUST"="CUST" AND "TGT"."DT"="DT"))
Seems more informative about partitioning. There you can see pstart and pstop. Seems like staging table partitioning matches target table sub partitioning. Yet another approach is monitoring.
merge into /*+monitor*/ tgt
using (select cust, prd, dt, num from stg) sou
on (tgt.dt=sou.dt and tgt.cust=sou.cust and tgt.prd=sou.prd)
when matched then update set tgt.num=tgt.num+sou.num
when not matched then insert (tgt.cust,tgt.prd,tgt.dt,tgt.num)
values(sou.cust,sou.cust,sou.dt,sou.num)
;
select dbms_sqltune.report_sql_monitor(sql_id,sid,session_serial#) from v$sql_monitor m where sid=userenv('sid') order by last_refresh_time desc;
And the latest line there tells us
SQL Monitoring Report SQL Text ------------------------------ merge into /*+monitor*/ tgt using (select cust, prd, dt, num from stg) sou on (tgt.dt=sou.dt and tgt.cust=sou.cust and tgt.prd=sou.prd) when matched then update set tgt.num=tgt.num+sou.num when not matched then insert (tgt.cust,tgt.prd,tgt.dt,tgt.num) values(sou.cust,sou.cust,sou.dt,sou.num) Global Information ------------------------------ Status : DONE Instance ID : 1 Session : RAFU (140:5746) SQL ID : cqakadxwrc3yz SQL Execution ID : 16777221 Execution Started : 04/29/2012 00:01:02 First Refresh Time : 04/29/2012 00:01:02 Last Refresh Time : 04/29/2012 00:01:02 Duration : .004028s Module/Action : SQL Developer/- Service : SYS$USERS Program : SQL Developer Global Stats =============================== | Elapsed | Other | Buffer | | Time(s) | Waits(s) | Gets | =============================== | 0.01 | 0.01 | 58 | =============================== Parallel Execution Details (DOP=4 , Servers Allocated=4) ================================================================================ | Name | Type | Server# | Elapsed | Other | Buffer | Wait Events | | | | | Time(s) | Waits(s) | Gets | (sample #) | ================================================================================ | PX Coordinator | QC | | 0.00 | 0.00 | 14 | | | p000 | Set 1 | 1 | 0.00 | 0.00 | 16 | | | p001 | Set 1 | 2 | 0.00 | 0.00 | 9 | | | p002 | Set 1 | 3 | 0.00 | 0.00 | 10 | | | p003 | Set 1 | 4 | 0.00 | 0.00 | 9 | | ================================================================================ SQL Plan Monitoring Details (Plan Hash Value=659647117) ========================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ========================================================================================================================================= | 0 | MERGE STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | MERGE | TGT | | | 1 | +0 | 1 | 1 | | | | 2 | PX COORDINATOR | | | | 1 | +0 | 5 | 2 | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 3 | 1 | +0 | 4 | 2 | | | | 4 | VIEW | | | | 1 | +0 | 4 | 2 | | | | 5 | NESTED LOOPS OUTER | | 2 | 3 | 1 | +0 | 4 | 2 | | | | 6 | PX BLOCK ITERATOR | | 2 | 2 | 1 | +0 | 4 | 2 | | | | 7 | TABLE ACCESS FULL | STG | 2 | 2 | 1 | +0 | 10 | 2 | | | | 8 | PARTITION RANGE ITERATOR | | 1 | 3 | 1 | +0 | 2 | 2 | | | | 9 | PARTITION HASH ITERATOR | | 1 | 3 | 1 | +0 | 2 | 2 | | | | 10 | TABLE ACCESS FULL | TGT | 1 | 3 | 1 | +0 | 2 | 2 | | | =========================================================================================================================================Parallel four of the stg table was obeyed(DOP=4). There was only two lines to merge. Processes p000 and p002 did more than others. Seems like partition wise parallel merge is possible even in this case when source partitioning matches target sub partitioning.
How about partition wising. The plan join strategy is nested loops joining so is it actually doing stuff partition wise? This example was constructed with small amount of lines, but how about more lines and stuff to do. To be continued...
No comments:
Post a Comment