Rollback Size

[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

[Axr2]

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

[phamou]

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

[Axr2]

No there is nothing will tell you about amout of rollback *before* running it.

Trace it. See what's happening underneath.

[stmontgo]

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?