Igor's Blog

This post is about an Oracle 11gR2 feature. I've been using the dbms_parallel_execute PL/SQL package to parallelise processing of large amounts of data and ran into some issues with chunking of my processing table. What I've noticed is that the number of chunks created seemed to be bound by the number of extents that the table was using, this was not ideal in my case, luckily there is a different solution to create as many chunks as you need.

This was the code that I started off using to chunk up my table. Initial testing seemed to go ok, but then I started noticing that the number of parallel jobs was always stuck at 4, no matter what I used for the parallel_level (in the dbms_parallel_execute.run_task not shown below) or chunk_size. Four parallel jobs was not going to cut it, so I dug a bit deeper because the forums did not appear to have a solution.
begin
dbms_parallel_execute.create_task('FLOOP THE PIG');
dbms_parallel_execute.create_chunks_by_rowid(
task_name => 'FLOOP THE PIG',
table_owner => 'zzz',
table_name => 'xxx',
by_row => true,
chunk_size => 100
);
end;


Looking at the chunks created, I always got the same result, four rows. I've updated table statistics, schema statistics, nothing helped. It was starting to get frustrating.
select * from USER_PARALLEL_EXECUTE_CHUNKS;

chunk2.png




So I was convinced that the number of extents had something to do with this behaviour, the DBA didn't agree. Lo and behold, I was right.
SELECT EXTENTS, BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME = 'xxx';

chunk1.png


Once I knew this was happening, I decided to switch from ROWID chunking to chunking by a numbered column. This needed a couple of changes: a new number column in my table, a sequence, a trigger to populate the sequence number in my table and some minor changes to the parallel execution code.

I added a new NUMBER column named SEQ_N to my table, and created a sequence like this:
CREATE SEQUENCE "xxx"."yyySEQNO" MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 100 NOORDER CYCLE ;


Then I added a trigger to populate this column, this is the way to do in Oracle 11g, in 12c you don't need a trigger any more and can specify it as a column default value.
create or replace TRIGGER yyySEQNO_TRG
BEFORE INSERT ON xxx
FOR EACH ROW
BEGIN
SELECT yyySEQNO.nextval INTO :new.SEQ_N FROM dual;
END;


Then I needed to change the PL/SQL to call the create_chunks_by_number_col procedure instead of the create_chunks_by_rowid procedure, this was a simple change.
dbms_parallel_execute.create_chunks_by_number_col(
task_name => 'FLOOP THE PIG',
table_owner => 'zzz',
table_name => 'xxx',
table_column => 'SEQ_N',
chunk_size => 100
);


The method that the parallel execution code was calling also needed a change to its input parameters, previously their type was ROWID, this had to change to NUMBER for obvious reasons. That was it!

When I chunked the table using the number column, I got more chunks, hooray!
chunk3.png


Now I can really floop the pig!

-i

, , ,
About — I'm an enthusiastic software engineer and consultant interested in many fields including J2EE, programming, electronics, 3D printing, video games, wood working and gardening.
See my Resume for more information.
The views expressed in this blog are my own and not those of my employer.
comments powered by Disqus
My other posts you may like...
Programming, DIY, Games, Hacks, Tech and more.
Follow me on...
Current and Past Projects
See my Resume

Subscribe


RSS Feed

My Other Web Sites

Igor and Elise's Travels
Riverside Expressway Cam
StrFunc() Online
300 George St Blogumentary
Guru JSON-RPC Tester
Extrudifier Object Designer

Recent Blog Posts

Binding extra variables with PHP PDO returns no results

New Nintendo 3DS XL, R4i SDHC RTS Lite and DSDoom

Older versions of JD-GUI crashing on macOS Sierra

How does Seeed Fusion stack up as a low budget PCB fab

Where to find the last item (Robbie's lost lyrics) in GravityFalls Lake level

Detecting print requests to print contents of a DIV with JavaScript

Using Google DFP with AdSense on responsive pages

How to change the email subject line in Gmail

Force PHP GD library to load JPEG images that have invalid data

How to uninstall Samsung Smart Switch from your Mac

Recent Galleries

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

DIY spare parts computer build with a RAIDMAX Anura case

Fake 'Lepin' brand Lego packaging

Hardwood garden bench with clear resin void filler

Fixing a 3D printer extruder that stopped heating up

Easily increase disk space in a Lenovo Ideapad 100S 14" laptop with an M.2 SSD

Making a multi-piece 3D printed solder spool holder stand

DIY indoor apartment grow light wiring

Good Friday Electronics fun Easter Bunny LED PCB Kit IBEABU-01.0

Top Categories

Blogs I follow

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth

Friends

RAWS Parts Online
Alpha Dimensions Hosting
Kristensen Photography
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity
Follow me on... 
     
...or subscribe for updates!

Don't show this again