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