Transaction size

[farrokhp]

Can you explain to me what does it mean eaxctly " Transaction Size ", Average Transaction Size , How can I obtain them and then what possible changes I need to do?

Regards

[julian]

In the view V_$TRANSACTION, there are two columns, which include the number of undo blocks used (USED_UBLK) and number of undo records used (USED_UREC) in the transaction.

Usually, the number of undo blocks used in the transaction defines the size of the transaction.

In 9i, there is a view called V_$UNDOSTAT, whose column UNDOBLCKS show the number of undo blocks consumed for all 10 minute intervals during the last 24 hours. There is a corresponding column called TXNTOTAL. If you want to know how big an avarage transaction is in blocks, you may use AVG(UNDOBLCKS)/AVG(TXNTOTAL). This will give you the "Average Transaction Size" during the last 24 hours. This is my formula. There is no 'real' algorithm you can use to determine your average transaction size.

Clear? :-)

[farrokhp]

Julian , you mean that if I multiply number of undo blocks by my block size I will get the Trans size ? is it for each trans or for a time period? Can I get the size from Rollbvack activity ? It's not clear for me yet!!

[julian]

Originally posted by farrokhp
Julian , you mean that if I multiply number of undo blocks by my block size I will get the Trans size ?

Yes, the no. of undo blocks used by the transaction.

is it for each trans or for a time period? Can I get the size from Rollbvack activity ? It's not clear for me yet!!

V_$TRANSACTION gives you a row for a transaction, while V_$UNDOSTAT (the one I use) gives you a new row every 10' in such a way that it keeps info only for the past 24 hours. It is a very good and useful view. But in 9i, with AUM, we don't bother anymore so much about RBSs. I don't use RBSs in 9i.

[farrokhp]

Julian , you mean that if I multiply number of undo blocks by my block size I will get the Trans size ? is it for each trans or for a time period? Can I get the size from Rollbvack activity ? It's not clear for me yet!!