Re: determining rollback segment activity

From: Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk>
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 Lewis
Received on Tue Apr 18 1995 - 00:00:00 CEST

Original text of this message