On Working in Parallel
Finish faster with DBMS_PARALLEL_EXECUTE.
ORACLE DATABASE 11g RELEASE 2
Ihave to perform DML [data manipula- tion language] operations on millions of
rows of data. Writing the SQL statement to
perform the changes isn’t hard, but managing rollback segments and getting the
process to finish in an acceptable amount of
time are. Does Oracle Database 11 g Release
2 offer any new features that can help?
Each new version of Oracle Database
brings an ever-widening array of built-in or supplied packages that extend the
capabilities of Oracle Database. Oracle
Database 11 g Release 2 is no exception: it
contains more than 50 new packages (all
documented in Oracle Database PL/SQL
Packages and Types Reference), including
DBMS_PARALLEL_EXECUTE.
DBMS_PARALLEL_EXECUTE now provides the ability to break up a large table
according to a variety of criteria, from
ROWID ranges to key values and user-defined methods. You can then run a SQL
statement or a PL/SQL block against these
different “chunks” of the table in parallel,
using the database scheduler to manage
the processes running in the background.
Error logging, automatic retries, and
commits are integrated into the processing
of these chunks.
To use DBMS_PARALLEL_EXECUTE to run
tasks in parallel, your schema will need the
CREATE JOB system privilege. You can then
use the following subprograms of the built-in
package (these are the most commonly used
of the package’s routines) to achieve your goal:
CREATE_TASK creates a named task to be •
managed by DBMS_PARALLEL_EXECUTE.
CREATE_CHUNKS_BY_ROWID defines by •
ROWID the various chunks of the total set of
rows to be modified by the SQL statement.
CREATE_CHUNKS_BY_SQL defines, by •
a user-specified SQL statement, the
chunking of data.
CREATE_CHUNKS_BY_NUMBER_COL•
defines, by a numeric column, the
chunking of data.
RUN_ TASK runs the named task after •
chunking has been defined.
TASK_STATUS obtains the status of the task. •
S TOP_ TASK stops the task. •
RESUME_TASK resumes the task. •
DROP_ TASK removes the task when it has •
been completed.
All these subprograms and any others in
DBMS_PARALLEL_EXECUTE—except for
TASK_STATUS—perform a commit.
Let’s look at a few examples of how to
use this package. We will start with the
simplest approach: chunking by ROWID.
Suppose I need to apply a raise in salary
(specified by a percentage) to all the
employees in our company. In case my parallelized task fails for some reason, I want to
be able to retry a specified number of times
to complete it.
Listing 1 displays the code for implementing a parallelized apply_raise procedure.
Table 1 explains the use of DBMS_
PARALLEL_EXECUTE subprograms in the
apply_raise procedure in Listing 1. The steps
in lines 6 through 28 create the task, specify
chunking, and run the task. If you are sure
Code Listing 1: Chunking by ROWID in the apply_raise procedure
SQL> PROCEDURE apply_raise (
2 pct_in INNUMBER
3 , retries_in IN PLS_INTEGER DEFAULT 2
4 )
5 IS
6 c_update_statement CONSTANT VARCHAR2 (1000)
7 := ‘UPDATE /*+ ROWID (dda) */ EMPLOYEES emp
8 SET emp.salary = emp.salary ( 1.0 + pct_in/100)
9 WHERE ROWID BETWEEN :starting_rowid AND :ending_rowid’;
10 c_task_name CONSTANT VARCHAR2 ( 20) := ‘Give Raise’;
11 l_attempts PLS_INTEGER := 1;
12 BEGIN
13 DBMS_PARALLEL_EXECUTE.CREATE_TASK (c_task_name);
14
15 DBMS_PARALLEL_EXECUTE.
16 CREATE_CHUNKS_BY_ROWID (task_name => c_task_name
17 , table_owner => USER
18 , table_name => ‘EMPLOYEES’
19 , by_row => TRUE
20 , chunk_size => 1000
21 );
22
23 DBMS_PARALLEL_EXECUTE.
24 RUN_TASK (task_name => c_task_name
25 , sql_stmt => c_update_statement
26 , language_flag => DBMS_SQL.native
27 , parallel_level => 10
28 );
29
30 LOOP
31 EXIT WHEN DBMS_PARALLEL_EXECUTE.TASK_STATUS (c_task_name) <>
32 DBMS_PARALLEL_EXECUTE.FINISHED
33 OR l_attempts > retries_in;
34 l_attempts := l_attempts + 1;
35 DBMS_PARALLEL_EXECUTE.RESUME_TASK (c_task_name);
36 END LOOP;
37
38 DBMS_PARALLEL_EXECUTE.DROP_TASK (c_task_name);
39* END apply_raise;
