JL Computer Consultancy

A demonstration of controlling concurrent processing.

June  1999


In previous articles I demonstrated how to generate a covering set of rowid ranges for a table, and a package for allocating tasks from a list to multiple concurrent processes with minimal contention. In this article I put the two packages together to show how you can update a very large table in discrete sections to get the maximum possible performance from your system.

There are 4 sections to the page.

Section 1

Building a sample data set, get the rowid ranges, populate the control table for the parallel process

Section 2

A procedure to acquire and lock a rowid range, then update that range's set of data

Section 3

A simple shell script that can be executed multiple times to select the parallelism

Section 4

A simple SQL script to reset the flags on the control table to repeat the experiment

Before you can run this demonstration, you will need to create a suitably privileged account, and the three packages that allow it to work - jpl_utils (to convert decimal to hex), y2k_tools to generate the rowid ranges, and parallel_allocation to control the concurrent processes.

The account you use will have to have the privilege to select from dba_extents, and to execute pacakge dbms_lock granted directly to it, as these objects are used in the support packages.


Preparing the demonstration

rem
rem     Script:        cc_make.sql
rem     Author:        Jonathan Lewis
rem     Dated:         1-June-1999
rem     Purpose:       Prepare demo data for concurrent processing
rem
rem
rem     Create a sample table
rem
create table upd_demo
unrecoverable
tablespace tools
storage (initial 480K next 480K)
as
select * from sys.source$
;
rem
rem     Generate the rowid ranges
rem
begin
        y2k_tools.generate_rowid_ranges(
               'JPL','UPD_DEMO',480
        );
end;
/
rem
rem     Copy the rowid ranges into the
rem     generic 'parallel allocation' table
rem
declare
        v_ret_code     number;
        v_err_msg      varchar2(128);
begin
        -- 'Create' the table
        parallel_allocation.create_driver(
               i_driving_task   => 'Update demo',
               io_return_code   => v_ret_code,
               io_error_message => v_err_msg
        );
        if (v_ret_code != parallel_allocation.c_success) then
               dbms_output.put_line('Error: ' || v_ret_code);
               dbms_output.put_line(v_err_msg);
               raise_application_error(-20001, 'Broken');
        end if;
        -- Populate the table
        -- Note that we are selecting from the y2k_tools table, one row at a time
        -- We could build a special version of the parallel_allocation package to 
        -- use the y2k_tools table directly, but one reason for having packages at
        -- all is so that we can re-use generic code.
        for r1 in (select * from rowid_control) loop
               parallel_allocation.populate_driver(
                       i_driving_task          => 'Update demo',
                       i_payload               => r1.rowid_start || '-' || 
                                                 r1.rowid_end,
                       io_return_code          => v_ret_code,
                       io_error_message        => v_err_msg
               );
               if (v_ret_code != parallel_allocation.c_success) then
                       dbms_output.put_line('Error: ' || v_ret_code);
                       dbms_output.put_line(v_err_msg);
                       raise_application_error(-20001, 'Broken');
               end if;
        end loop;
end;
/
commit;

Procedure to get one control row and update the associated data

rem
rem     cc_update.sql
rem
rem     Loop requesting a row from the allocation table
rem     if there are any control rows left
rem            update (and count) the rows in the range
rem            mark the control row as done
rem     else if a problem arises
rem            bomb out
rem     else
rem            end
rem
rem
set serveroutput on size 10000
set timing on
create or replace procedure do_update as
        v_continue     boolean := true;
        v_count        number(8) := 0;
        v_total        number(8) := 0;
        v_ret_code     number(2);
        v_err_msg      varchar2(80);
        v_payload      varchar2(40);
        v_rowid_start  rowid;
        v_rowid_end    rowid;
begin
        while v_continue loop
               parallel_allocation.allocate_target_item(
                       i_driving_task   => 'Update demo',
                       o_payload        => v_payload,
                       io_return_code   => v_ret_code,
                       io_error_message => v_err_msg
               );
               if (v_ret_code = parallel_allocation.c_no_rows_left) then
                       v_continue := false;
               elsif (v_ret_code = parallel_allocation.c_success) then
                       v_rowid_start := chartorowid(substr(v_payload,1,18));
                       v_rowid_end   := chartorowid(substr(v_payload,20));
                       update /*+ rowid(t) */ upd_demo t
                       set line = line + 1
                       where rowid between v_rowid_start 
                       and v_rowid_end;
                       v_count := sql%rowcount;
                       v_total := v_total + v_count;
                       dbms_output.put_line(
                               'Rows: ' || v_count || ' - ' || v_total
                       );
                       parallel_allocation.complete_target_item(
                               i_driving_task   => 'Update demo',
                               i_payload        => v_payload,
                               io_return_code   => v_ret_code,
                               io_error_message => v_err_msg
                       );
                       if (v_ret_code != parallel_allocation.c_success) then
                               dbms_output.put_line('Error: ' || v_ret_code);
                               dbms_output.put_line(v_err_msg);
                               raise_application_error(-20001, 'Broken');
                       end if;
               else
                       v_continue := false;
                       dbms_output.put_line('Error: ' || v_ret_code);
                       dbms_output.put_line(v_err_msg);
                       raise_application_error(-20001, 'Broken');
               end if;
        end loop;
end;
.
/

A shell script to run one copy of the update

#!/bin/ksh
#
#       cc_demo.ksh
#
sqlplus -s jpl/jpl  <<-!!!
set serveroutput on size 100000
set timing on
execute do_update;
exit
!!!

An SQL script to reset all the control rows

rem
rem     cc_reset.sql
rem     Quick fix to clear all the flags
rem
declare
        v_return_code number(2);
        v_error_message varchar2(80);
begin
parallel_allocation.reset_all_targets(
               i_driving_task         => 'Update demo',
               io_return_code         => v_return_code,
               io_error_message       => v_error_message
);
end;
/

Back to Main Index of Topics