JL Computer Consultancy

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.

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;

/