Ask The Real Tom

April 30, 2009



High Perfomance DML with Parallel Piplined Function and Bulk DML (II)

Filed under: oracle — Tags: , , — admin @ 7:34 am

Refering to my previous blog.
I could speed up the update of my large 120GB table from 02:25:43.86 to 01:28:46.61.

I’ll just explain the changes to get the additional 55 minutes.

v_array_size PLS_INTEGER := 10000;
I still not sure what the optimal size is bit 10000 works well.

FORALL i IN INDICES OF v_row1
This is the main change. The collection gets no copied, it just references the collected array.

select count(*) from table(pgk_xxxx_mig_parallel_ptf_api.ptf_dml( cursor(select /*+ PARALLEL(t,64) PARALLEL_INDEX(t, I_PK_TRANSAKT, 64) */ TRANSACTION ID from T_TRANSACTION t ) ) );
parallel 64 does more than 16.

Still improvements should be possible. I’m working on it. 🙂

April 24, 2009



High Perfomance DML with Parallel Piplined Function and Bulk DML

Parallel Update and Delete has some restriction on partitioned tables. The statement will just run in a parallel degree equal to the partition count.

I searched a solution to optimze a update on a 120G Table with about 300’000’000 rows which touches every row.

My current solution is a do a buld update within a pipelined function which is parallel executet.
With this solution I can run the update with bulk dml and in parallel.

Important is that the dml within the pipelined function is an anoymous transaction since any implicit or explicit commits are allowed in the session where the pipelined function is executet.

Now I have the possibility to tune any bulk batch size and any parallel degree to optimized the dml to my need.


CREATE OR REPLACE PACKAGE pgk_tom_mig_parallel_ptf_api AS

TYPE t_parallel_test_row IS RECORD ( TRANSACTIONID NUMBER(16));
TYPE t_parallel_test_ref_cursor IS REF CURSOR RETURN t_parallel_test_row;
TYPE t_dml_counter IS TABLE OF NUMBER;

TYPE t_TRANSACTIONID_tab IS TABLE OF t_transaction.TRANSACTIONID%TYPE INDEX BY BINARY_INTEGER;
v_row1 t_TRANSACTIONID_tab;
v_array_size PLS_INTEGER := 500000;

function ptf_dml(p_cursor IN t_parallel_test_ref_cursor)
return t_dml_counter
pipelined
parallel_enable (PARTITION p_cursor BY ANY );
-- parallel_enable (PARTITION p_cursor BY RANGE (TRANSACTIONID));

END pgk_tom_mig_parallel_ptf_api;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY pgk_tom_mig_parallel_ptf_api AS

-- create or replace function ptf_dml(p_cursor IN SYS_REFCURSOR)
function ptf_dml(p_cursor IN t_parallel_test_ref_cursor)
return t_dml_counter
pipelined
-- parallel_enable (PARTITION p_cursor BY RANGE (TRANSACTIONID))
parallel_enable (PARTITION p_cursor BY ANY )
is
PRAGMA AUTONOMOUS_TRANSACTION;
r_rowid t_transaction.TRANSACTIONID%type;
BEGIN
LOOP
FETCH p_cursor BULK COLLECT INTO v_row1 LIMIT v_array_size;
FORALL i IN 1..v_row1.COUNT

UPDATE t_transaction t
SET t.yx =
.......
WHERE TRANSACTIONID=v_row1(i);

-- rollback;
commit;
pipe row (1);
EXIT WHEN p_cursor%NOTFOUND;
END LOOP;
end ptf_dml;

end pgk_tom_mig_parallel_ptf_api;
/
SHOW ERRORS

alter session force parallel query parallel 16;
select count(*) from table(pgk_tom_mig_parallel_ptf_api.ptf_dml( cursor(select /*+ PARALLEL(t,16) PARALLEL_INDEX(t, I_PK_TRANSID, 16) */ TRANSACTION
ID from T_TRANSACTION t ) ) );
alter session enable parallel query;

COMMIT;

Powered by WordPress