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;
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';
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!
Now I can really floop the pig!
-i
