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

NOTE: This article is 3 years or older so its information may no longer be relevant. Read on at your own discretion! Comments for this article have automatically been locked, refer to the FAQ for more details.
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

Skip down to comments...
Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
 
comments powered by Disqus
Other posts you may like...