Database Reference
In-Depth Information
Starting with Oracle Database 11 g Release 2 and above, we have a new way to implement parallelism via the
DBMS_PARALLEL_EXECUTE built-in package. Using it, you can execute a SQL or PL/SQL statement in parallel by taking
the data to be processed and breaking it up into multiple, smaller streams. You can implement the same approach
yourself, manually, and I show how in the section on “Old School Do-It-Yourself Parallelism” that follows. The beauty
of the new package, though, is that it eliminates much of the tedious work that you otherwise need to perform.
Let's start with the premise that we have a SERIAL routine that we'd like to execute in parallel against some
large table. We'd like to do it with as little work as possible; in other words, modify as little code as possible and be
responsible for generating very little new code. Enter DBMS_PARALLEL_EXECUTE . We will not cover every possible use of
this package (it is fully documented in the Oracle Database PL/SQL Packages and Types Reference manual but we will
use just enough of it to implement the process I've just described.
Assuming we start with the empty table T2 , we'll modify our serial process now to look like this (additions to the
original, simple serial process are in bold ):
EODA@ORA12CR1> create or replace
2 procedure serial( p_lo_rid in rowid, p_hi_rid in rowid )
3 is
4 begin
5 for x in ( select object_id id, object_name text
6 from big_table
7 where rowid between p_lo_rid
8 and p_hi_rid )
9 loop
10 -- complex process here
11 insert into t2 (id, text, session_id )
12 values ( x.id, x.text, sys_context( 'userenv', 'sessionid' ) );
13 end loop;
14 end;
15 /
Procedure created.
That's it: just add the ROWID inputs and the predicate. The modified code has not changed much at all. I am using
SYS_CONTEXT to get the SESSIONID so we can monitor how much work was done by each thread, each parallel session.
Now, to start the process, we first need to break up the table into small pieces. We can do this by some numeric
range—useful for tables that use a SEQUENCE to populate their primary key, by any arbitrary SQL you want to code
or by ROWID ranges. We'll use the ROWID range. I find it to be the most efficient because it creates non-overlapping
ranges of the table (contention free) and doesn't require querying the table to decide the ranges; it just uses the data
dictionary. So, we'll make the following API calls:
EODA@ORA12CR1> begin
2 dbms_parallel_execute.create_task('PROCESS BIG TABLE');
3 dbms_parallel_execute.create_chunks_by_rowid
4 ( task_name => 'PROCESS BIG TABLE',
5 table_owner => user,
6 table_name => 'BIG_TABLE',
7 by_row => false,
8 chunk_size => 10000 );
9 end;
10 /
PL/SQL procedure successfully completed.
 
Search WWH ::




Custom Search