Help on Private Rollback Segment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help on Private Rollback Segment

  1. #1
    Join Date
    Mar 2002
    Posts
    17

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    When the PL/SQL block is running, you can watch/query from v$rollstat to know about size of rollback seg usage;

    Tamil

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    committing inside the loop is performance killer
    I'm stmontgo and I approve of this message

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width