RE: Transactions waiting for Lock on Rollback Segments

Vivek,

Responses embedded. As usual, the answers are not easy 1-liners, but they deal with the issues that you are grappling with. Hope it helps.

Cheers,

Gaja.

• VIVEK_SHARMA <vivek_sharma_at_inf.com> wrote:
  >
  >-----Original Message-----
  >From: VIVEK_SHARMA
  >Sent: Tuesday, May 23, 2000 11:19 AM
  

>Steve Wrote > my guess is that it was actually an
> >ITL entry shortage (see page 47 in my book).
> > If this is 8i, you may want to consider using the
> > 'alter table minimize records_per_block'
>
> What does ITL stand for ?
> The name of the Book please ( pg 47) ?

I guess you already have the answers to both these questions.

Vivek wrote:
> On Oracle 7.3.4 with 8Kb DB_BLOCK_SIZE ,
> for the respective TableS , should INITRANS & MAXTRANS
> be Altered to Control the No. of rows per block ? How ?
>

Altering INITRANS and MAXTRANS usually does not control the number of rows in a block. I guess you can argue that if you set a absurdly high INITRANS that would take up a ton of space in the block header and hence control the number of rows in the blocks. But let's look at a reasonable configuration. INITRANS & MAXTRANS control the number of transaction(txn.) slots that are assigned in the block header.

Said another way, it controls the block level transactional concurrency. These txn. slots are utilized by txns. when they make modifications to the block. Conceptually you can think of each txn. signing in, using one of these slots before making modifications to any rows. Then at the row level there is a lock-byte which indicates the slot# which is locking the row. The slot# in turn will contain the txn.-id of the txn. locking the row.

Vivek wrote:  

> Gaja ,
> Thanks so much for the Explanation . Was the Greatest
> Gaja Wrote >"The "ball park" optimum number of rollback
> segments that is required for most environments can be derived
> by the formula : "no. of concurrent txns/4".
>
> Qs. 1 Should Number of Rollback Segments be INCREASED to
> 12,000 approx from 160 or Some intermediate value ?

I have not worked in any environment that required 12000 rollback segments, but then again I need to see more of the world...;-). In your original posting you said that your system had to process 50000 OLTP txns. in 1 hour. We need to also verify how long a given txn. is active and how much work it performs.

If we assume even distribution of 50000 txn. in a given hour, it translates to 833.33 txns./minute, which in turn is 13.88 txns./sec. Given that you do not have 50000 txns. hitting the database at the same time, I am pretty confident that you do not need 12000 rollback segments, unless they all hit the database at the same exact second. Please bring me back to "center field" if I have missed something.

If I were you, I would work off the 13.88 txns./second number. Theoritically, using the formula, you do not need more than 4 rollback segments, but then again you will have to size the rollback segments right and then monitor the database to see whether the contention goes away. One can speculate that even 160 rollback segments that you have now is too much, but one cannot categorically say that until one studies all the related metrics on your database.

The formula (no. of concurrent txns./4) works for most environments, you will have to be the judge as to whether it works for yours. If it does not, then you will have to create more rollback segments as needed, until the contention is at a minimal.

Vivek wrote:

> NOTE That The Transactions OLTP in nature
>
> QUERY :-
> select table_name,avg_row_len
> from sys.dba_tables
> where ...
> /
>
> OUTPUT :-
> TABLE_NAME AVG_ROW_LEN NUMBER of ROWS
> INSERTed per Tran
> ------------------------- ------------
> ---------------------------------
> DAILY_TRAN_DETAIL_TABLE 186 2
> DAILY_TRAN_HEADER_TABLE 91 1
> TEMP_DAILY_TRAN_TABLE 73 2
>
> Thus 1 Transaction = 550 BYTES - Correct ?
>

1 txn. here affects 550 bytes of data, however that does not mean that you are generating 550 bytes of rollback. For inserts, the rowid information of the inserted rows is adequate, to perform a rollback operation later on if needed.

> Gaja Wrote >The size of the extents in the rollback segments
>> can be derived by performing an avg(used_ublk)
>> in v$transaction during peak periods.
>

Vivek wrote:
>OK -will revert back to you after next Transaction Runs
> Will use the Query :-
> select avg(used_ublk) from v$transaction;
>

Will wait for your feedback.

> Gaja Wrote > "Proactive configuration of an optimal number of
> freelists and the effective use of the INITRANS parameter "
>

Vivek wrote:

> Qs. How is the Value of INITRANS , MAXTRANS to be determined
> ? Any Thumb rule ?

There are no hard and fast rules for INTRANS & MAXTRANS configuration. Basically it boils down to how many txns. will attempt to modify rows within the same block at the same time. Every environment is different.

I have used INITRANS of 4 in most OLTP environments with a DB_BLOCK_SIZE of 8K, for those tables that encounter a lot of concurrent changes. The rationale there was that in our "infinite wisdom", we knew that on an average, no more than 4 txns. will touch a block at the same time. Most txn. slot contention/problems, arise during inserts anyway and that problem can be alleviated by configuring (2 * # of CPUs) worth of freelists. Deletes and Updates are lesser of a problem.

As the block size for a given database increases (basically doubles on each increase..2K, 4K, 8K, 16K etc.), the contention for data also doubles. You have to keep that factor in mind while configuring INITRANS. So using my previous example, if and when I configured a 16K blocksize database, everything else remaining constant, the value of INITRANS will be set to 8, for those tables that support a lot of concurrent changes.

There are 2 schools of thought with regards to MAXTRANS. Some set it the same value as INITRANS, but be aware that any txn. that visits a block and is unable to procure a slot (because none is available), then it will wait until a slot becomes available. If you have done your groundwork right, this usually should not pose a problem.

Others leave MAXTRANS at its default value (255?), but also be aware that when dynamic txn. slot allocation occurs, the 23 bytes required for the slot is procured from PCTFREE. Dynamic transaction slot allocation also slows down the speed of a transaction.

>> stuff deleted...

Gaja Krishna Vaidyanatha   | 3460 West Bayshore Road,
Manager - Integration      | Palo Alto, CA 94303
& Consulting Services      | gaja_at_brio.com
Global Alliances           | (650)-565-4442
Brio Technology            | www.brio.com 

"Opinions and views expressed are my own and not of Brio Technology" Received on Wed May 24 2000 - 12:08:17 CDT