Help on Private Rollback Segment
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
lv_date1 := '01.jan.00';
lv_date2 := lv_date1 + 15;
update ordermaster set status ='N'
where orderdte between lv_date1 and lv_date2;
if lv_date1 >= '1-jun-03' then
lv_date1 := lv_date2;
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
When the PL/SQL block is running, you can watch/query from v$rollstat to know about size of rollback seg usage;
committing inside the loop is performance killer
I'm stmontgo and I approve of this message
Click Here to Expand Forum to Full Width