|
Transaction Entry
|
In this issue, I start a new topic, transaction entry.
First, I talk about initrans and maxtrans which are specified
to create a table and an index.
initrans and maxtrans
When you create a table or an index, you may have hard time in allocating datafile
and determining the extent size to distribute I/O processes.
How many of you have modified initrans and maxtrans at creating the table
and the index?
What are initrans and maxtrans?
When you update the table and the index by issuing INSERT, UPDATE,
or DELETE statements, space called transaction entry (ITL) is temporarily allocated in a
block to be actually updated. Transaction entry contains information regarding the
transaction updating the block and is 23 bytes in size.
If multiple transactions attempt to write to the same block at a time, transaction entries
are allocated for the number of transactions.
Space reserved for the transaction entry is allocated in a block beforehand
for the number specified in initrans. When the reserved space is consumed to the fullest,
space keeps being allocated dynamically until it reaches the maxtrans or until available
space in a block is consumed entirely.
By default, initrans is set to 1 and maxtrans is set to 255 at CREATE TABLE.
maxtrans can be set up to 255 but it is actually determined by the block size.
block size max value of transaction entry
2K 41
4K 84
8K 169
16K 255
|
maxtrans -Block storing-
Now, I actually let the concurrent accesses occur more than the number of times specified
by maxtrans to see what happens.
3887 bytes of transaction entry (23 bytes*169 blocksize=8) are actually allocated.
Half of 8K block size is temporarily reserved as space for transaction.
Is this reserved space dumped after all?
I execute the transaction issuing INSERT statement only to
check the block usage rate. I use POPSQL to increase the dedicated-connections
to 200 and issue INSERT statements at the same time.
This program lets 200 sessions repeat INSERT and COMMIT 1,000 times.
system environment for this test:
OS:AIX5L
Oracle:8.1.7
I create a table with maxtrans=255 (which is actually 169 because block size is 8K this time.)
CREATE TABLE TBL_SHU1 (ID DATE DEFAULT SYSDATE,
TEXT CHAR(10))
INITRANS 1 MAXTRANS 255 PCTFREE 10
STORAGE ( INITIAL 20M
NEXT 20M
PCTINCREASE 0
MAXEXTENTS UNLIMITED);
|
200 sessions
After I execute the program above, I issue ANALYZE statement to check the block
usage.
analyze table tbl_shu1 compute statistics;
select TABLE_NAME,NUM_ROWS,BLOCKS from dba_tables
where table_name='TBL_SHU1';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU1 200000 850
|
850*8K=6.6MB is currently used.
1 session
After I truncate the table above, I reduce the number of sessions to 1 and execute
200000 of inserts.
I execute the program above, and then issue ANALYZE statement to check the block usage.
analyze table tbl_shu1 compute statistics;
select TABLE_NAME,NUM_ROWS,BLOCKS from dba_tables
where table_name='TBL_SHU1';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU1 200000 665
|
665*8K=5.19MB is currently used.
Block usage is reduced by 22% when compared to 200 sessions. I expect that
the block usage can be reduced by 50% though. I will talk about this next week.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle