|
Transaction Entry
|
Hello everyone. In this issue, I continue to analyze initrans and maxtrans which
are specified when tables and indexes are created.
System environment:
OS: Windows2000 server
Oracle: 8.1.7
DB_BLOCK_SIZE=8K
Analysis of maxtrans -block usage ratio-
tbl_shu1 and tbl_shu2 are defined as follows:
Last time, I set maxtrans to 1 and let 200 sessions execute insert processes
at a time to see what happens.
select TABLE_NAME,NUM_ROWS,BLOCKS from dba_tables
where table_name='TBL_SHU1';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU1 200000 148989
|
The result above indicates not even two records could not be stored in a single block.
I also let three sessions execute insert processes at a time and found out three blocks
were used for this operation.
select TABLE_NAME,NUM_ROWS,BLOCKS from dba_tables
where table_name='TBL_SHU2';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
TBL_SHU2 3 3
|
The result above proves that if the transaction entry in the block reaches maxtrans,
Oracle raises high water mark and allocates new block to prevent next transaction from
being in queue.
Next, I analyze space and freelist left in the block.
Dump file
freelist and high water mark data reside in the segment header.
I get block dump of the segment header to check the details.
SQL> select file_id,block_id from dba_extents
where segment_name = 'TBL_SHU2' and extent_id=0;
FILE_ID BLOCK_ID
---------- ----------
7 2
SQL> alter system dump datafile 7 block 2;
system altered
Note:This command is an internal command of Oracle.
Oracle Corporation does not take any responsibility of you using
this command.
|
Excerpt of block dump
(1)buffer tsn: 6 rdba: 0x01c00002 (7/2)
(2)Highwater:: 0x01c00006 ext#: 0 blk#: 3 ext size: 2559
(3)SEG LST:: flg: USED lhd: 0x01c00005 ltl: 0x01c00005
|
(1) rdba represents data block address (DBA)
(2) Highwater represents DBA of high water mark
(High water mark indicates a block that comes after the last block
with data.)
(3) lhd is DBA of the first free block of freelist
Status of current block is as follows:
0x01c00002 segment header
0x01c00003 datablock 1
0x01c00004 datablock 2
0x01c00005 datablock 3
0x01c00006 block indicated by highwater
|
lhd of (3) indicates datablock 3. If data is inserted next time,
it will be stored in datablock 3.
I check the block dump of datablock.
0x01c00003 seg/obj: 0xc67 csc: 0x00.3f226 itc: 1 flg: - typ: 1 - DATA
0x01c00004 seg/obj: 0xc67 csc: 0x00.3f227 itc: 1 flg: - typ: 1 - DATA
0x01c00005 seg/obj: 0xc67 csc: 0x00.3f228 itc: 1 flg: O typ: 1 - DATA
|
flg of datablock 1 and 2 indicates hypen (-), but the flg of
datablock 3 indicates 0. datablock 1 and 2 are thus not linked to
the freelist.
This means that three sessions execute insert process at a time and when the second and the third
sessions attempt to insert data to datablock 1 and 2, transaction entry
is not longer available. Therefore, the insert process does not complete.
Based on the result, Oracle concludes that the block is already occupied and the datablock 1
and 2 are not linked to the freelist.
Now, I actually execute the insert process.
insert into tbl_shu2 (TEXT) values ('PPPPPPPPPP');
|
I get the block dump again to check the details.
Before insert
0x01c00005 nrow=1
After insert
0x01c00005 nrow=2
|
The result is the same as the previous one. Data is stored in datablock 3.
Why datablock 1 and 2 are not used?
Once the datablock is unlinked from the freelist, it will be linked again
if records in that datablock are deleted or updated and the block is below pctused.
SQL> delete from tbl_shu2
2 where to_char(id,'MM/DD HH24:MI:SS')='03/19 16:01:55';
One row deleted
SQL> commit;
SQL> alter system dump datafile 7 block 3;
0x01c00003 seg/obj: 0xc67 csc: 0x00.443a3 itc: 1 flg: O typ: 1 - DATA
|
The result above indicates that flg indicates 0.
If only insert processes are executed to a table, this table
will not be used again and unnecessary space will remain.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle