|
Transaction Entry
|
In this issue, I continue to talk about initrans and maxtrans which
are specified when a table and an index are created.
This time, I pick up one of questions from subscribers.
Operating Environment
OS: Windows2000 Server
Oracle:8.1.7
DB_BLOCK_SIZE:8K
Question
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?
Answer
View is not provided per block. Thus, we need to get the block dump
to check how many transaction entries are in a block.
CREATE TABLE TBL_SHU3 (ID DATE DEFAULT SYSDATE,
TEXT CHAR(10))
INITRANS 1 MAXTRANS 255 PCTFREE 10
STORAGE ( INITIAL 20M
NEXT 20M
PCTINCREASE 0
MAXEXTENTS UNLIMITED);
|
---Insert a row---
First of all, I insert a row.
insert into tbl_shu3 (TEXT) values ('PPPPPPPPPP');
|
Following is an excerpt from the block dump.
(Refer to the previous issue for how to get the block dump)
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.054.0000004d uba: 0x00800373.0005.2c ---- 1 fsc 0x0000.00000000
|
One transaction entry is created.
I now execute a commit process to get the block dump.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0002.054.0000004d uba: 0x00800373.0005.2c --U- 1 fsc 0x0000.0004462a
|
Flag is switched from (----) to (--U-), but the transaction entry still remains.
---Insert a row from another session---
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0004.00d.0000004d uba: 0x008006eb.0003.1b ---- 1 fsc 0x0000.00000000
commit
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0004.00d.0000004d uba: 0x008006eb.0003.1b --U- 1 fsc 0x0000.00044630
|
I don' see any increase in transaction entry.
--Insert two rows from a session---
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0005.05e.0000004c uba: 0x008008b1.0002.28 ---- 2 fsc 0x0000.00000000
commit
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0005.05e.0000004c uba: 0x008008b1.0002.28 --U- 2 fsc 0x0000.00044634
|
Even though I insert two rows, only a single transaction entry is used
per transaction. Lck above indicates the number of records.
---Insert a row each from three sessions---
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0006.00f.0000004d uba: 0x00800b66.0005.57 ---- 1 fsc 0x0000.00000000
0x02 xid: 0x0007.00f.0000004d uba: 0x00800d07.0004.1e ---- 1 fsc 0x0000.00000000
0x03 xid: 0x0008.037.0000004d uba: 0x00800ed7.0003.56 ---- 1 fsc 0x0000.00000000
commit
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0006.00f.0000004d uba: 0x00800b66.0005.57 --U- 1 fsc 0x0000.0004463b
0x02 xid: 0x0007.00f.0000004d uba: 0x00800d07.0004.1e --U- 1 fsc 0x0000.0004463d
0x03 xid: 0x0008.037.0000004d uba: 0x00800ed7.0003.56 --U- 1 fsc 0x0000.00044639
|
As I insert a row each from three sessions (i.e. three transactions are performed),
three transaction entries (0x01 to 0x03) are created.
---Drop transaction entries---
Transaction entry still remains after commit process is performed.
Is this kept as a history or will be dropped?
I increase the number of transaction entries to four, execute truncate process,
reboot Oracle, and get the block dump. Details of the block dump are as follows:
After truncate is performed
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0011.050.0000004d uba: 0x008020b8.0002.4e C--- 0 scn 0x0000.00044658
0x02 xid: 0x0013.03d.0000004d uba: 0x008024ff.0003.4f --U- 1 fsc 0x0014.0004465f
0x03 xid: 0x0010.031.0000004d uba: 0x00801f1e.0004.3b C--- 0 scn 0x0000.00044655
0x04 xid: 0x000d.011.0000004d uba: 0x008018c0.0002.55 C--- 0 scn 0x0000.0004464c
After Oracle is rebooted
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0011.050.0000004d uba: 0x008020b8.0002.4e C--- 0 scn 0x0000.00044658
0x02 xid: 0x0013.03d.0000004d uba: 0x008024ff.0003.4f --U- 1 fsc 0x0014.0004465f
0x03 xid: 0x0010.031.0000004d uba: 0x00801f1e.0004.3b C--- 0 scn 0x0000.00044655
0x04 xid: 0x000d.011.0000004d uba: 0x008018c0.0002.55 C--- 0 scn 0x0000.0004464c
|
Transaction entry still remains even after Oracle is rebooted.
Next, I cerate a new table and insert rows from 169 sessions.
(I set an interval between insert and commit to 100 seconds
so that the transaction entry can be used.
CREATE TABLE TBL_SHU4 (TEXT VARCHAR2(1000))
INITRANS 1 MAXTRANS 255 PCTFREE 10
STORAGE ( INITIAL 1M
NEXT 1M
PCTINCREASE 0
MAXEXTENTS UNLIMITED);
(This is a logic using POPSQL developed by our engineers. I execute following SQL statement
from 169 sessions.)
SQL insert into tbl_shu4 (TEXT) values ('P');
SLEEP 200
COMMIT
|
I get the block dump and check 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 are created.
How many records (bytes) can be inserted to this block from a single session?
Will transaction entries be dropped when any operation requires space?
I will leave these issues to next week.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle