2012-04-29

Partition wise merge

Having a sub partitioned target table and a staging table that should be merged into the target. Will a partition wise merge work.

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

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.