This output shows that we were able to lock 37 rows but ran out of transaction slots for the 38th row. For this given block, a maximum of 37 transactions can concurrently access it. If we redo the example with a slightly smaller string, we’ll see that it finishes with no problems:
SQL> truncate table t;Table truncated.
SQL> insert into t (x,y)select rownum, rpad(‘‘,147,’‘)from dualconnect by level <= 46;46 rows created.
SQL> select length(y),dbms_rowid.rowid_block_number(rowid) blk,count(), min(x), max(x)from tgroup by length(y), dbms_rowid.rowid_block_number(rowid); LENGTH(Y) BLK COUNT() MIN(X) MAX(X)
SQL> exec do_update(1);we finished – no problems PL/SQL procedure successfully completed.
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 1 byte smaller) allowed us to have at least nine 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 two 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 two. However, if you examine the data dictionary after creating a table, you’ll notice that INITRANS displays a value of one:
SQL> create table t ( x int );
SQL> select ini_trans from user_tables where table_name = ‘T’;INI_TRANS
So is the default number of transaction slots one or two? Even though the data dictionary is showing a value of one, we can demonstrate that it really is two. Consider this experiment. First, generate one transaction for table T by inserting a single record:
SQL> insert into t values ( 1 );
Now verify that one block is consumed by table T:
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from t; DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
Next, place into the variables—B and F—the block number and the datafile number of the block used by table T:
SQL> column b new_val B
SQL> column f new_val F
SQL> select dbms_rowid.ROWID_BLOCK_NUMBER(rowid) B, dbms_rowid.ROWID_TO_ABSOLUTE_FNO( rowid, user, ‘T’ ) F from t;
Now dump the block being used by table T:
SQL> alter system dump datafile &F block &B;
Next, place into a variable named TRACE the location and name of the trace file containing the dump information for the block:
SQL> column trace new_val TRACE
SQL> select c.value || ‘/’ || d.instance_name || ‘ora‘ || a.spid ||’.trc’ trace from v$process a, v$session b, v$diag_info c, v$instance dwhere a.addr = b.paddr and b.audsid = userenv(‘sessionid’)and c.name = ‘Diag Trace’;
You should see some output similar to this:
TRACE/u01/app/oracle/diag/rdbms/cdb/CDB/trace/CDB_ora_18604.trc
Now terminate the session and edit the trace file:
SQL> disconnect
SQL> edit &TRACE
Searching the trace file for the value of Itl, we see there are two transaction slots that have been initialized (even though there has only been one transaction issued for this table):
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0013.00e.000024be 0x00c000bf.039e.2d –U- 1 fsc 0x0000.01cfa56a
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
The INITRANS value of one reported in the data dictionary is most likely a legacy value, and it really should display a value of two for more current versions of Oracle.
Leave a Reply