For the example 1 is required definition of data
in table, therefore we assume definition of table:
create table test_to_process (
x number,
y varchar2(100),
node_id
number
)
tablespace &&small_data;
comment on table test_to_process is
'test table to transaction on tpp processing
example'
;
comment on column test_to_process.x is 'an value known as x number type';
comment on column test_to_process.y is 'an value known as y varchar2 type';
comment on column test_to_process.node_id
is 'identification of node';
With initial
data generate by following code:
begin
for i in 1..1000
loop
insert into test_to_process (x) values (i);
end loop;
commit;
end;
/
Let’s assume
following code following code for optimization by transactional parallel
processing:
create or replace procedure some_processing
(
p_x number,
p_node_id number
)
is
begin
update test_to_process
set y = to_char(x)||' some text',
node_id
= p_node_id
where x = p_x;
end;
/
declare
cursor c1 is
select x
from test_to_process;
begin
for r in c1 loop
--begin of processing
some_processing(r.x, 1);
--end of processing
end loop;
-- This part is based on woman s experience: Never trust a man after 10:00
PM.
-- Note: Actually woman s are trusting anyway.
if to_number(to_char(sysdate, 'hh24')) > 22 then
-- every processing after 22 hour should be rolled back
rollback;
else
-- otherwise is valid and can be committed
commit;
end if;
end;
/
The
result of current code is following data in table TEST_TO_PROCESSING:
|
X |
Y |
NODE_ID |
|
1 |
1 some text |
1 |
|
2 |
2 some text |
1 |
|
3 |
3 some text |
1 |
|
4 |
4 some text |
1 |
|
5 |
5 some text |
1 |
|
6 |
6 some text |
1 |
|
7 |
7 some text |
1 |
|
8 |
8 some text |
1 |
|
9 |
9 some text |
1 |
|
10 |
10 some text |
1 |
|
11 |
11 some text |
1 |
|
12 |
12 some text |
1 |
|
13 |
13 some text |
1 |
|
… |
… |
… |
For using of the Transactional Parallel Processing should be the process
rewritten following way:
1) The processing part in the loop should be separated into procedure.
2) Grant the new procedure to Transactional Parallel Processing package.
3) Rewrite the processing.
Note:
We recommend to keep Transactional Parallel Processing in separate schema under
separate user.
The
variable &tpp_owner
represents the schema/user where is installed TPP.
The
variable &tpp_user
represents the schema/user where is installed TPP.
Add 1)
Result will be following procedure to_execute:
create or replace procedure to_execute (
p_parallel_level
in number,
p_node_id
in number)
as
cursor c1 is
select x
from test_to_process
where rownum < 1000
and mod(x, p_parallel_level) = (p_node_id - 1)
;
-- The select will separate list of 1000 item
-- for processing into X groups where X is number of group defined by p_parallel_level.
-- Values of node_id is in range <1 .. p_parallel_level>
-- In this case we need to separate group according remainder of mod(x, p_parallel_level)
-- divided by p_parallel_level.
-- Instead of node_id we should use (node_id - 1). It will separate groups according
-- remainder values in range <0..(p_parallel_level-1)>
--
-- For example in our case for parallel level 4 it means following
separation:
-- for 1st thread:
-- select x
-- from test_to_process
-- where rownum
< 1000
-- and mod(x, 4) = 0
--
-- for 2nd thread:
-- select x
-- from test_to_process
-- where rownum
< 1000
-- and mod(x, 4) = 1
--
-- for 3nd thread:
-- select x
-- from test_to_process
-- where rownum
< 1000
-- and mod(x, 4) = 2
--
-- for 4th thread:
-- select x
-- from test_to_process
-- where rownum
< 1000
-- and mod(x, 4) = 3
begin
for r in c1 loop
--begin of processing
some_processing(r.x, p_node_id);
--end of processing
end loop;
end to_execute;
/
Add 2)
Grant of the procedure.
grant execute on to_execute to &&tpp_owner;
Add 3) Update current processing.
Note:
In the statement should be specify the procedure for execution including the
schema.
declare
l_task varchar2(30) := 'TEST_TASK
example';
l_sql_execute_stmt
varchar2(32767);
-- there is required to use 4 parallel instances
l_parallel_level number := 4;
begin
-- procedure to be execute in parallel
l_sql_execute_stmt
:= 'begin &&tpp_user..to_execute(:parallel_level, :node_id); end;';
&&tpp_owner..tpp.initiate(
p_task_name => l_task
,p_comment => 'example of processing by the
transactional parallel processing'
,p_sql_stmt => l_sql_execute_stmt
,p_parallel_level => l_parallel_level -- there is
required to use 4 parallel instances
);
&&tpp_owner..tpp.wait_for_end_of_processing(l_task);
-- This part is based on woman s experience: Never trust a man after 10:00
PM.
-- Note: Actually woman s are trusting anyway.
if to_number(to_char(sysdate, 'hh24')) > 22 then
-- every processing after 22 hour should be rolled back
rollback;
else
-- otherwise is valid and can be committed
commit;
end if;
&&tpp_owner..tpp.finalization(l_task);
end;
/
The
result of current code is following data in table TEST_TO_PROCESSING:
|
X |
Y |
NODE_ID |
|
1 |
1 some text |
2 |
|
2 |
2 some text |
3 |
|
3 |
3 some text |
4 |
|
4 |
4 some text |
1 |
|
5 |
5 some text |
2 |
|
6 |
6 some text |
3 |
|
7 |
7 some text |
4 |
|
8 |
8 some text |
1 |
|
9 |
9 some text |
2 |
|
10 |
10 some text |
3 |
|
11 |
11 some text |
4 |
|
12 |
12 some text |
1 |
|
13 |
13 some text |
2 |
|
… |
… |
… |
For more details see http://1stsw.com/tpp_documentation/
The processing part in the loop should be separated
into procedure.
Let's assume we have current PLSQL code.
declare
x number:= 4;
y varchar2(10) := 'TEST';
begin
for i in (1..node_id)
loop
-- begin processing
insert into test_to_process (x, y, node_id) values (to_number(x), y, node_id);
dbms_lock.sleep(20);
-- end processing
end loop;
if to_number(to_char(sysdate, 'hh24')) > 22 then
-- every processing after 22
hour should be rolled back
rollback;
else
-- otherwise is valid and can
be commited
commit;
end if;
end;
/
It can
be using the Transactional Parallel Processing package rewrite following way.
1) The
processing part in the loop should be separated into procedure.
2)
Grant the new procedure to Transactional Parallel Processing package. (We
recommend to keep Transactional Parallel Processing in separate schema under
separate user.)
3)
Rewrite the processing.
Add 1)
Result will be following:
create or replace procedure to_execute (x in number, y in varchar2, node_id in number default 0)
as
p varchar2(100) := 'to_execute';
begin
-- begin processing
insert into test_to_process (x, y, node_id) values (to_number(x), y, node_id);
dbms_lock.sleep(20);
-- end processing
exception
when others then
&&tpp_owner..tpp.add_log(p, 'err:
'||to_char(sqlcode)||' '||dbms_utility.format_error_backtrace);
end to_execute;
/
Add 2)
Grant of the procedure.
grant execute on to_execute to &&tpp_owner;
Add 2) Update current processing.
declare
l_task varchar2(30) :=
'TEST_TASK';
l_sql_execute_stmt varchar2(32767);
begin
-- procedure to be execute in
parallel
l_sql_execute_stmt := 'begin
&&tpp_user..to_execute (1, ''TEST'', :node_id); end;';
&&tpp_owner..tpp.initiate(
p_task_name => l_task
,p_comment =>
'example of processing by the transactional parallel processing'
,p_sql_stmt => l_sql_execute_stmt
,p_language_flag => dbms_sql.native
,p_parallel_level => 4 --
there is required to use 4 prallel instanes
);
&&tpp_owner..tpp.wait_for_end_of_processing(l_task);
if to_number(to_char(sysdate, 'hh24')) > 22 then
-- every processing after 22
hour should be rolled back
rollback;
else
-- otherwise is valid and can
be commited
commit;
end if;
&&tpp_owner..tpp.finalization(l_task);
end;
/
Note: For the example to demonstrate the transactional
behavior we define following table:
create table test_to_process (
x number,
y varchar2(100),
node_id number
)
tablespace
&&small_data;
comment on table test_to_process is
'test table to transaction on tpp processing example'
;
comment on column test_to_process.x is 'an
value known as x number type';
comment on column test_to_process.y is 'an
value known as y varchar2 type';
comment on column test_to_process.node_id is
'identification of node';
Result of processing before 22 PM are records in table
test_to_process.
See result of following command:
select * from test_to_process;
Result of processing before 22 PM is no record in
table test_to_process.
See result of following command:
select * from test_to_process;
For more details see http://1stsw.com/tpp_documentation/