Rollback Size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rollback Size

  1. #1
    Join Date
    May 2003
    Posts
    5

    Question Rollback Size

    Hi,

    I need to know approximatly how much rollback are used in a large delete transaction.
    the table implied in the delete has several "on delete cascade" constraints under it, witch causes a 300MB delete to use about 9Gigs of Rollback space.

    do you know a way of tracking the rollback that could be used in a delete transaction or can you give me a way of knowing the actual size of all the records implied in the delete statement (with the "on delete cascade" sub-tables data too).

    thanks & have a good day!

    Patrick
    Patrick Hamou
    Principal Consultant
    Renaps Technology Canada inc.
    http://www.renaps.com

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    There are lots of scripts available on metalink. Here's one.

    SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
    t.cr_get, t.phy_io, t.used_ublk, t.noundo,
    substr(s.program, 1, 78) "COMMAND"
    FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
    WHERE t.addr = s.taddr
    and t.xidusn = r.usn
    ORDER BY t.cr_get, t.phy_io
    /


    Look at used_ublk

  3. #3
    Join Date
    May 2003
    Posts
    5
    is there any way of calculating the record sizes of all of the impacted tables before running it ?
    I need to understand more deeply why this transaction takes 9Gigs of Rollbacks...

    Do you know any existing script that could generate me the sql to get the vsize of all of the impacted tables (the tables that are part of the "on delete cascade" rule) ?

    thanks again,

    Patrick
    Patrick Hamou
    Principal Consultant
    Renaps Technology Canada inc.
    http://www.renaps.com

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    No there is nothing will tell you about amout of rollback *before* running it.

    Trace it. See what's happening underneath.

  5. #5
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Rollback Size

    Originally posted by phamou
    Hi,

    I need to know approximatly how much rollback are used in a large delete transaction.
    the table implied in the delete has several "on delete cascade" constraints under it, witch causes a 300MB delete to use about 9Gigs of Rollback space.

    do you know a way of tracking the rollback that could be used in a delete transaction or can you give me a way of knowing the actual size of all the records implied in the delete statement (with the "on delete cascade" sub-tables data too).

    thanks & have a good day!

    Patrick
    How do you know this? Do you mean that a delete is removing 300mg of data and if so how are you determining this. How do you know this delete is using 9gb worth of rollback.

    Are you using automatic undo?
    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