Parallel Execution - Expert Oracle Database Architecture - page 685

We started by creating a named task: 'PROCESS BIG TABLE' in this case. This is just a unique name we'll use to
refer to our big process. Second, we invoked the CREATE_CHUNKS_BY_ROWID procedure. This procedure does exactly
what its name implies: it “chunks up” a table by ROWID ranges in a manner similar to what we just did. We told the
procedure to read the information about the currently logged in user's table named BIG_TABLE and to break it up into
chunks of no more than about 10,000 blocks ( CHUNK_SIZE ). The parameter BY_ROW was set to false which implies, in this
case, that the CHUNK_SIZE is not a count of rows to create ROWID ranges by but rather a count of blocks to create them.
We can see the number of chunks and information about each chunk immediately after this block of code
executes by querying DBA_PARALLEL_EXECUTE_CHUNKS :
EODA@ORA12CR1> select *
2 from (
3 select chunk_id, status, start_rowid, end_rowid
4 from dba_parallel_execute_chunks
5 where task_name = 'PROCESS BIG TABLE'
6 order by chunk_id
7 )
8 where rownum <= 5
9 /
CHUNK_ID STATUS START_ROWID END_ROWID
---------- -------------------- ------------------ ------------------
1 UNASSIGNED AAAEyWAAEAAAAQoAAA AAAEyWAAEAAAAQvCcP
2 UNASSIGNED AAAEyWAAEAAAAQwAAA AAAEyWAAEAAAAQ3CcP
3 UNASSIGNED AAAEyWAAEAAAAQ4AAA AAAEyWAAEAAAAQ/CcP
4 UNASSIGNED AAAEyWAAEAAAARAAAA AAAEyWAAEAAAARHCcP
5 UNASSIGNED AAAEyWAAEAAAARIAAA AAAEyWAAEAAAARPCcP
The query in this example shows the first five rows in the view; in my case, there were 218 total rows in there for
the table in question, each representing a non-overlapping chunk of the table to process. This does not mean we'll be
processing the table in “parallel 218,” just that we have 218 chunks in total to process. We are now ready to run our task
via this API call:
EODA@ORA12CR1> begin
2 dbms_parallel_execute.run_task
3 ( task_name => 'PROCESS BIG TABLE',
4 sql_stmt => 'begin serial( :start_id, :end_id ); end;',
5 language_flag => DBMS_SQL.NATIVE,
6 parallel_level => 4 );
7 end;
8 /
PL/SQL procedure successfully completed.
Here we asked to run our task 'PROCESS BIG TABLE'— which points to our chunks. The SQL statement we want
to execute is 'begin serial( :start_id, :end_id ); end;'— a simple call to our stored procedure with the ROWID
range to process. The PARALLEL_LEVEL I decided to use was four, meaning we'll have four parallel threads/processes
executing this. Even though there were 218 chunks, we'll only do four at a time. Internally, this package uses the
DBMS_SCHEDULER package to run these threads in parallel.
Once our task starts running, it will create four jobs; each job is told to process the chunks identified by the key
value 'PROCESS BIG TABLE' and run the stored procedure SERIAL against each chunk. So, these four sessions start
and each reads a chunk from the DBA_PARALLEL_EXECUTE_CHUNKS view, processes it, and updates the STATUS column.
If the chunk is successful, the row will be marked as PROCESSED; if it fails for any reason or if a given chunk cannot be