Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

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

Please leave your comments or feedback below!
comments powered by Disqus
Other posts you may like...
Hi! You can search my blog here ⤵
Or browse the recent top tags...

Recent Blog Posts

Mini review of the Sonoff B1 WiFi light bulb

Coherence cache performance optimistic vs replicated schemes

Coherence and weblogic.xml in different types of J2EE web app deployments

Atari Lynx repair - Part 5 - McWill LED screen mod installation

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 3 - broken speaker replacement

Atari Lynx repair - Part 2 - re-capping the motherboard

Atari Lynx repair - Part 1 - introduction and case disassembly

jPhotoFrame updated to version 0.3.1 with an image rotation correction utility

iOS 11 pre-GM mini review before it gets revealed next week

Recent Galleries

Atari Lynx repair - Part 5 - McWill LED screen mod installation

Atari Lynx repair - Part 4 - screen cover replacement

Atari Lynx repair - Part 2 - re-capping the motherboard

Atari Lynx repair - Part 3 - broken speaker replacement

Atari Lynx repair - Part 1 - introduction and case disassembly

Building a custom Atari Lynx game box storage shelf unit in a day

Protecting old Atari Lynx game boxes with snug fit plastic sleeves

Monument Valley 2 is released and does not disappoint

Space Food - Chocolate Ice Cream with Chocolate Chips

Legeod Star Wars AT-DP kit

Blogs and Friends

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

Blog Activity

Blog Activity