| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Transactions waiting for Lock on Rollback Segments
--=====================_959654200==_
Hi !
I had the same discussion in PLSQL/DBA pipe line and here is the thread..
K Gopalakrishnan
Bangalore, INDIA
At 09:44 AM 5/29/00 -0800, you wrote:
>
>> Gaja Wrote :-
>> 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.
>>
>> Qs. How to Find out what No. of Concurrent Transactions will touch 1
>> Block ?
>> Does it Depend on the SIZE of the Transaction Data inserted ? Any
>> methodology for Calculation ?
>>
>>
>> Steve Wrote > my guess is that it was actually an ITL entry shortage
>> & Gaja wrote -
>> 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.
>>
>> NOTE - Though FREELISTS was set = 50 , INITRANS & MAXTRANS were ALSO set
>> to 50
>> The DB server is an 18 CPU machine .
>> Qs Were we VERY WRONG in setting such Values for INITRANS & MAXTRANS ?
>> Qs. Could such MAXTRANS Value have caused the Transactions to wait ?
>>
>>
>> Gaja Wrote - 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.
>>
>>
>> The Assumption is Correct . The Transactions are Spread out over the Hour
>> NOTE - Almost ALL Transactions are INSERTS . NO Deletes / Updates
>>
>>
>> >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'
>> >
>>
>> 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 ?
>> >
>>
> - - -
> - - -
> - - -
>
>> 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 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.
>>
> - - -
> - - -
> - - -
>> 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.
>>
>--
>Author: VIVEK_SHARMA
> INET: vivek_sharma_at_inf.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
--=====================_959654200==_
 |
 |