|
Transaction Entry
|
In this issue, I continue to analyze transaction entry under the following system
environment.
System Environment
OS: Windows2000 Server
Oracle:8.1.7
DB_BLOCK_SIZE:8K
Following is a question from a subscriber I covered last week.
Is there any way to know how many transaction entries does a block contain?
And is it possible to know how many transaction entries are added?
I got a block dump to check the information about transaction entry (ITL).
Once the transaction entry is created, it still remains even after
commit process is executed, a table is truncated, and Oracle is rebooted.
Is this kept as a history? Once space is occupied by the transaction entry,
Will space never be able to be used again?
Last week, I executed an insert process from 169 sessions to following table
at a time and got the block dump.
tbl_shu4
----------------------------------------- -------- ----------------------------
TEXT VARCHAR2(1000)
|
I get the block dump and checked the details.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0001.012.00000052 uba: 0x00800158.000e.23 --U- 1 fsc 0x0000.0004832d
0x02 xid: 0x0002.037.00000050 uba: 0x00800375.0005.2b --U- 1 fsc 0x0000.0004832f
...............................
0xa8 xid: 0x0012.018.00000052 uba: 0x00802277.0013.01 --U- 1 fsc 0x0000.00048458
0xa9 xid: 0x0013.027.00000050 uba: 0x0080250c.0004.01 --U- 1 fsc 0x0000.00048459
|
169 transaction entries were created.
Drop a transaction entry
This time, I analyze up to how many bytes of data can be inserted to this table.
According to the calculation, there should be 3887 bytes (23bytes*169) difference between two cases
where transaction entry can be used again and where transaction entry
is no longer available as space.
If initrans is set to 1:
CEIL((block size 8192 - block header 86)*0.9) - 4 = 7292Bytes
Note: 0.9 is for pctfree (10%).
The result proves that 7300 bytes should be used as space for data.
If transaction entry remains and data stored by using this entry remains
1 byte of data is currently inserted for the number of 169 records, and the total space usage
is 1859 bytes (including header and column information.)
If the transaction entry is dropped, 5433 bytes (7292 - 1859) will be available.
If the transaction entry is not dropped, 1546 bytes (7292 - 3887) will be available.
I set 1000 bytes of data to varchar2 column and then insert 10 rows from a session.
I then get the block dump where 169 transaction entries are created.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0019.017.00000052 uba: 0x00803160.0005.02 --U- 1 fsc 0x0000.0004a909
0x02 xid: 0x0012.049.00000053 uba: 0x0080230b.0014.02 C--- 0 scn 0x0000.0004a79a
・・・・・・・・・
0xa8 xid: 0x0005.008.00000051 uba: 0x008008d3.0003.01 C--- 0 scn 0x0000.0004a8cd
0xa9 xid: 0x0006.038.00000051 uba: 0x00800b8b.0006.01 C--- 0 scn 0x0000.0004a8cc
・・・・・・・・・
nrow=170 <- number of rows stored
|
170 - 169 = 1. There is only one row.
If transaction entry remains but data stored by using this entry is gone
Next, I truncate the table, set 1000 bytes of data to varchar2 column, and then
insert 10 rows from a session.
Following is the block dump after truncate process is performed. ITL remains.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0019.017.00000052 uba: 0x00803160.0005.02 --U- 1 fsc 0x0000.0004a909
0x02 xid: 0x0012.049.00000053 uba: 0x0080230b.0014.02 C--- 0 scn 0x0000.0004a79a
・・・・・・・・・・・・・・
0xa8 xid: 0x0005.008.00000051 uba: 0x008008d3.0003.01 C--- 0 scn 0x0000.0004a8cd
0xa9 xid: 0x0006.038.00000051 uba: 0x00800b8b.0006.01 C--- 0 scn 0x0000.0004a8cc
|
Following is a block dump after ten rows are inserted.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x001c.058.00000051 uba: 0x008037a7.0006.0a --U- 7 fsc 0x0000.0004a913
・・・・・・・・・・・・・・
nrow=7
|
Seven rows are stored, and the transaction entry decreases to 1.
Conclusion
When I execute an insert process from 169 sessions, transaction entry
remains. If space still contains data that is stored by using the transaction entry,
the transaction entry is allocated for any possibility of each record being
used by other sessions at a time. It is called a placeholder. This prevents a block
being locked when it attempts to lock the record.
If the truncate process is already performed, the transaction entry is
reset because space is deallocated. Moreover, there may be seven records of data to be
stored in the next block. Even though the block contains 169 transaction entries,
it just wastes space.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle