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

comments powered by Disqus
Other posts you may like...
Programming, DIY, Games, Hacks, Tech and more.

Recent Blog Posts

Learn How to Add Disclaimer to All Outgoing Emails in Office 365

How to securely host your web site for free with Google Cloud and CloudFlare (part 3)

How to securely host your web site for free with Google Cloud and CloudFlare (part 2)

How to securely host your web site for free with Google Cloud and CloudFlare (part 1)

Beware of the subtle difference in date format strings between MySQL and SQLite

Postman reporting Unexpected 'N' error in response body

Adding extra shelves to the Ikea Detolf display cabinet

Posting array data from a web form to a Jersey REST service

How to avoid double encoding XML entities with Apache Commons StringEscapeUtils

Add your commonly used fonts to macOS Mail font drop down

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

My Other Web Sites

Igor and Elise's Travels
Riverside Expressway Cam
300 George St Blogumentary

My Online Tools

UUID to OID Converter
Guru JSON-RPC Tester
Extrudifier Object Designer
Travel ┬ÁBlog

Blogs and Friends

Matt Moores Blog
Georgi's FlatPress Guide
Perplexing Permutations
The Security Sleuth
Ilia Rogatchevski
Travelling Fairy

Blog Activity

Blog Activity
Please follow me for more great content!
     
Don't show this again