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; /