Locking and Latching - Expert Oracle Database Architecture - page 230

This time we completed successfully—the difference a single byte makes! In this case, having the extra
46 bytes of space free on the block (each of the 46 strings was just one byte smaller) allowed us to have at least 9
more transactions active on the block.
This example demonstrates what happens when many transactions attempt to access the same block
simultaneously—a wait on the transaction table may occur if there is an extremely high number of concurrent
transactions. Blocking may occur if the INITRANS is set low and there is not enough space on a block to dynamically
expand the transaction. In most cases, the default of 2 for INITRANS is sufficient, as the transaction table will
dynamically grow (space permitting), but in some environments you may need to increase this setting (to reserve
more room for slots) to increase concurrency and decrease waits.
An example of when you might need to increase the setting would be on a table or, even more frequently, on an
index (since index blocks can get many more rows on them than a table can typically hold) that is frequently modified
and has a lot of rows per block on average. You may need to increase either PCTFREE (discussed in Chapter 10) or
INITRANS to set aside ahead of time sufficient space on the block for the number of expected concurrent transactions.
This is especially true if you anticipate the blocks will be nearly full to begin with, meaning there is no room for the
dynamic expansion of the transaction structure on the block.
One last note on INITRANS . A couple of times I've stated that the default value for this attribute is 2. However,
if you examine the data dictionary after creating a table, you'll notice that INITRANS displays a value of 1:
EODA@ORA12CR1> create table t ( x int );
EODA@ORA12CR1> select ini_trans from user_tables where table_name = 'T';
INI_TRANS
----------
1
So is the default number of transaction slots 1 or 2? Even though the data dictionary is showing a value of 1, we
can demonstrate that it really is 2. Consider this experiment. First generate one transaction for table T by inserting a
single record:
EODA@ORA12CR1> insert into t values ( 1 );
Now verify that one block is consumed by table T :
EODA@ORA12CR1> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
57715
Next, place into the variables— B and F —the block number and the data file number of the block used by table T :
EODA@ORA12CR1> column b new_val B
EODA@ORA12CR1> column f new_val F
EODA@ORA12CR1> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) B,
2 dbms_rowid.ROWID_TO_ABSOLUTE_FNO( rowid, user, 'T' ) F
3 from t;
Now dump the block being used by table T :
EODA@ORA12CR1> alter system dump datafile &F block &B;