Re: determining rollback segment activity
Date: 1995/04/18
Message-ID: <798240755snz_at_jlcomp.demon.co.uk>#1/1
In article <1995Apr14.221652.4542_at_inet.d48.lilly.com>
swisshelm_at_lilly.com "Bob Swisshelm" writes:
: 1. Which active transaction is holding a lock in the oldest extent,
: forcing the rollback segment to extend?
: 2. How much actual rollback segment space is each active transaction
: using?
Qn 1: Here is my best guess at present: It assumes that you know which rollback segment you are interested in.
Look in v$transaction:
column SCNBASE holds (I think) the System Commit Number from the moment the transaction started. As such I would not expect two rows in v$transaction to have the same SCNBASE; however, this may not be quite accurate (what is SCNWRAP for, it always seems to be 0 !!). XIDUSN is the Undo Segment Number (i.e. rollback segment number) of the rollback segment for the transaction. UBADBA is the Absolute Block Address of the first block grabbed by the transaction: value is power(file_number,26) + block_number (for Unix systems). (This seems to have been replaced by UBAFIL, UBABLK in newer versions of Oracle).
Select all rows in v$transaction with the same XIDUSN as the one you are interested in, then find the one with the lowest SCNBASE: my theory is that this is the oldest transaction in the segment: since rollback is used in a round-robin fashion, this is presumably the one you want.
Qn 2: I don't think that there is a solution to this problem: I think that a transaction simply grabs the next available 'directory entry' in a legal block to use an arbitrary amount of space in the block. I think you would need to trace through block-dumps to get all entries for a given transaction to work out actual space usage.
-- Jonathan LewisReceived on Tue Apr 18 1995 - 00:00:00 CEST