|
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
In Vol.128 to 130, I analyzed the transaction entry by inserting data concurrently from multiple
sessions to a table to see what happened to maxtrans.
When the transaction entry in a block reached maxtrans, Oracle raised
high water mark even though the block still had free space, and allocated new
block to prevent next transaction from being in queue. Therefore, block was unlinked from
the freelist.
This time, I analyze an index to see what happens.
I use a table tbl_shu1 which I used in Vol.128.
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);
|
I create an index in ID column of tbl_shu1.
CREATE INDEX IDX_TBL_SHU1 ON TBL_SHU1(ID)
INITRANS 2 MAXTRANS 255 PCTFREE 10
STORAGE ( INITIAL 20M
NEXT 20M
PCTINCREASE 0
MAXEXTENTS UNLIMITED);
|
I analyze if there is any difference in index area between two cases
where I insert 200,000 data from a single session and where insert 1,000
data each from 200 sessions.
Case 1: Insert 200,000 data from a single session
First, I insert data from a single session and get the information.
Like always, I use POPSQL to create a test program.
LOOP(i=0; i<200000; i++)
SQL insert into tbl_shu1 (TEXT) values ('PPPPPPPPPP');
COMMIT
ENDLOOP
ANALYZE INDEX IDX_TBL_SHU1 COMPUTE STATISTICS;
SELECT INDEX_NAME,TABLE_NAME,NUM_ROWS,LEAF_BLOCKS FROM DBA_INDEXES
WHERE INDEX_NAME='IDX_TBL_SHU1';
INDEX_NAME TABLE_NAME NUM_ROWS LEAF_BLOCKS
------------ ----------- ---------- -----------
IDX_TBL_SHU1 TBL_SHU1 200000 476
|
476 blocks (3.17 MB) are used as leaf block.
Case 2: Insert 1,000 data each from 200 sessions
Next, I truncate tbl_shu1 and then insert 1,000 data each from 200 sessions.
ANALYZE INDEX IDX_TBL_SHU1 COMPUTE STATISTICS;
SELECT INDEX_NAME,TABLE_NAME,NUM_ROWS,LEAF_BLOCKS FROM DBA_INDEXES
WHERE INDEX_NAME='IDX_TBL_SHU1';
INDEX_NAME TABLE_NAME NUM_ROWS LEAF_BLOCKS
------------ ----------- ---------- -----------
IDX_TBL_SHU1 TBL_SHU1 200000 1345
|
1345 blocks (10.51 MB) are used, which is 2.8 times larger than Case 1.
Do we have the same result as we had in previous issues?
Get the block dump
Now, I get the block dump to check the details.
I start with the third block because the first block is occupied by segment header
and the second one is a branch.
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0010.046.00000098 uba: 0x00801e23.0008.01 CBU- 0 scn 0x0000.000e86cf
0x02 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 xid: 0x000d.021.00000098 uba: 0x008019b7.0006.06 C-U- 0 scn 0x0000.000e8651
0x04 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x05 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Leaf block dump
===============
header address 114984100=0x6da84a4
kdxcolev 0
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 417 <- number of records
|
There are 5 transaction entries and 417 records. This is quite similar
to Case 1.
Now, I check the details per 100 blocks.
block ID # of transaction entries # of records
100 166 151
200 168 110
300 169 140
|
The result proves that transaction entry consumes data space in large quantity.
Regarding the first block, it seems that 200 sessions didn't start
inserting data all at once due to a timing of transaction.
Furthermore, it is unlinked from the freelist.
(flg: -)
seg/obj: 0xdbf csc: 0x00.bf064 itc: 169 flg: - typ: 2 - INDEX
This analysis proves that concurrent insertion of data also implies
concurrent update of data. Concurrent insertion process therefore may be the right choice.
But, logically, it is not a good idea to update PKEY.
I will further talk about this topic next week.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle