-
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
-
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
-
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
-
No there is nothing will tell you about amout of rollback *before* running it.
Trace it. See what's happening underneath.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|