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.
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.
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.
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:
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.
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.
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!