Help on Private Rollback Segment

[safedba]

Hi

Anyone can help me on determine the size of rollback segment?
My requirement is to update one column in table (with 1,500K records) within short period. For that I had created a private rollback segment, and using PL/SQL script i updated the table.

The PL/SQL Script

set timing on
set serveroutput on

declare
lv_date1 date;
lv_date2 date;
begin
dbms_transaction.use_rollback_segment('PR1');
lv_date1 := '01.jan.00';
loop
lv_date2 := lv_date1 + 15;
update ordermaster set status ='N'
where orderdte between lv_date1 and lv_date2;
commit;
dbms_transaction.use_rollback_segment('PR1');
if lv_date1 >= '1-jun-03' then
exit;
end if;
lv_date1 := lv_date2;
end loop;
end;

The Orderdte column is an indexed field. Before run this script I disabled the trigger associated with this table.

I had tested this script under rollback segment with different parameters, still I am not able to conclude how to determine the size of rollback segment. For testing, I had taken 5 months record (from 1/jan/03 to 31/may/03) and did for several times.

INITIAL & NEXT size of rollback segment is 1024K in all cases.

Size of Rollback - Date Range in Where Clause - Execution Time
MIN10 , MAX80 - 15days - 510 seconds
MIN5, MAX80 - 15days - 784 seconds
MIN30, MAX 160 - 15days - 461 seconds
MIN30, MAX 160 - 10 days - 316 seconds
MIN60, MAX 240 - 15days - 221 seconds
MIN100, MAX 320 - 30days - 290 seconds
MIN100, MAX 320 - 15days - 205 seconds


Expecting reply

[tamilselvan]

When the PL/SQL block is running, you can watch/query from v$rollstat to know about size of rollback seg usage;

Tamil

[stmontgo]

committing inside the loop is performance killer