|
Transaction Entry
|
In this issue, I continue to talk about initrans and maxtarns
which are specified when a table and an index are created.
Before I start, I have to apologize for using another machine for
testing because the machine I previously used does not operate properly.
This may cause a slight difference in testing result.
(Previous system environment)
OS:AIX5L
Oracle:8.1.7
DB_BLOCK_SIZE:8K
(Current system environment)
OS:Windows2000 Server
Oracle:8.1.7
DB_BLOCK_SIZE:8K
maxtrans -block usage ratio-
Last time, I set maxtrans to 255 (which is actually 169) and execute the
transaction issuing INSERT processes in 200 sessions and 1 session to examine
if there is any difference in block usage ratio.
---200 session---
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU1 200000 850
850*8K=6.64MB is currently used.
|
---1 session---
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU1 200000 665
665*8K=5.19MB is currently used.
|
The result above proves that 200 sessions require larger number of blocks.
It the difference in results caused by difference in the transaction layer?
Before I go on, I set maxtrans to 1 and examine how the result changes
when compared with 1 session.
---200 sessionAmaxtrans 1---
CREATE TABLE TBL_SHU1 (ID DATE DEFAULT SYSDATE,
TEXT CHAR(10))
INITRANS 1 MAXTRANS 1 PCTFREE 10
STORAGE ( INITIAL 20M
NEXT 20M
PCTINCREASE 0
MAXEXTENTS UNLIMITED);
|
I set maxtrans to 1 and create a table. I then execute the same test
program I used last time to let 200 sessions repeat insert and commit
1,000 times. After that, I execute ANALYZE to check the result.
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 148989
|
BLOCK column indicates 148989. As block size is set to 8k, this process requires
1164MB! Each block does not even have space for two records.
I set maxtrans to 1 again and create a table to analyze further.
CREATE TABLE TBL_SHU2 (ID DATE DEFAULT SYSDATE,
TEXT CHAR(10))
INITRANS 1 MAXTRANS 1 PCTFREE 10
STORAGE ( INITIAL 20M
NEXT 20M
PCTINCREASE 0
MAXEXTENTS UNLIMITED)
TABLESPACE TS_SHU2;
|
I then let one session execute INSERT and stop the process before the session
executes COMMIT. Transaction entry of the first block is entirely used.
insert into tbl_shu2 (TEXT) values ('PPPPPPPPPP');
|
Next, I let the other session execute INSERT. I let the first session
execute COMMIT and let the second session execute COMMIT.
I then execute ANALYZE.
analyze table tbl_shu2 compute statistics;
select TABLE_NAME,NUM_ROWS,BLOCKS from dba_tables
where table_name='TBL_SHU2';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU2 2 2
|
BLOCK column indicates 2. The first block may be occupied as a segment
header. I truncate the table and let the three sessions execute INSERT
and stop the operation before three sessions execute COMMIT.
I then execute ANALYZE and view dba_tables.
SQL> select TABLE_NAME,NUM_ROWS,BLOCKS from dba_tables
2 where table_name='TBL_SHU2';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU2 3 3
|
BLOCK column indicates 3.
When the transaction entry in the block reaches maxtrans,
Oracle raises high water mark and allocates new blocks to let next
transaction proceed without being in queue.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle