OWB 11gR2 – Parallel DML and Query

A quick post illustrating conventional (non direct path) parallel inserts and query using OWB following on from some recent posts from Jean-Pierre and Randolf on this topic. The mapping configuration properties is where you can define these hints in OWB, taking JP’s simplistic illustration, the parallel query hints in OWB are defined on the ‘Extraction hint’ property for the source, and the parallel DML hints are defined on the ‘Loading hint’ property on the target table operator.

image

If we then generate the code you can see the intermediate code generated below…

moz-screenshot-172

Finally…remember the parallel enabled session for this all to fly…

image

Anyway, hope this helps join a few dots….

Comments:

Hi, I'm trying to get work an example of data chunking in OWB 11.2... Serial and Parallel mode. I can to get that Serial Mode works, but Parallel don't. I will work in a future project with Match and Merge Operator over a very large dimension and really need the data chunking feature to get raise the performance. Anybody has make Data Chunking Parallel Mode works? Regards

Posted by Luis Gálvez on March 04, 2011 at 07:38 AM PST #

Hi Luis (this is the mail Isent you...the blogging platforn is up again, I just want to record my response for posterity). There were some issues in chunking in the production code, using the DBMS_PARALLEL_EXECUTE procedure manually either through a script or calling in another map is the best choice today I think until the bug fix is available. You will need to parameterize your mapping to have the start/end and filter the source before the match merge operator - below I have a constant value of 1 that I bin on in the match merge operator. Then each map execution although executing match merge only processes one bin, the DBMS_PARALLEL_EXECUTE code will do the parallel chunk assignment for each instance of the map to process. Hope this makes some sense. Match merge has binning. Binning is basically a divide and conquer approach to speed up the processing. For example, if you are loading one million rows into a table with a million rows then you want to avoid doing a million by a million comparison. Binning allows you to subset this processing as matching is only performed within the record set for that bin. So...based on the data you have you will have to make a call what makes sense to create as the bin sets. The smaller the match bin, the faster the processing (since less comparisons), however, rows will only be considered within the match bin so you need to make sure your binning does not exclude any possible matches (that you do not want eliminated). You can have match bins based on any fields passed into the match-merge operator and since these can contain any operator (including constants,expressions and transformations) you have lots of flexibility. Cheers David

Posted by David Allan on April 08, 2011 at 02:00 AM PDT #

Hi, I'm getting automatically a hint (append parallel) when I reimport a Table and sync it to the mapping. The funny thing is that I have 'enable parallel DML' as 'false'. Currently having problems with parallel processing and processs ending unexpectly due parallel and TMP tablespace. Don't think is related but just wondering why OWB is doing this? Thanks!

Posted by guest on June 14, 2012 at 05:59 AM PDT #

Hi

There is a bug logged for this 6043377 which has been there for quite a few years now. Also some other forum topis;
https://forums.oracle.com/forums/thread.jspa?messageID=1805486&#1805486

Cheers
David

Posted by David on June 14, 2012 at 01:46 PM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today