Help on Private Rollback Segment
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
