|
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
Last time, I inserted 1,000 data each from 200 sessions to an index
and found out that transaction entry consumed space in large quantity.
This time, I set maxtrans to 2 and insert data from 200 sessions
at a time to analyze an index.
Set maxtrans to 2 (index)
I use a table tbl_shu1.
DDL to create a table.
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 truncate tbl_shu1 and create an index with maxtrans=2 again.
CREATE INDEX IDX_TBL_SHU1 ON TBL_SHU1(ID)
INITRANS 2 MAXTRANS 2 PCTFREE 10
STORAGE ( INITIAL 20M
NEXT 20M
PCTINCREASE 0
MAXEXTENTS UNLIMITED);
|
As always, I use a program written by POPSQL and insert 1,000 data each from
200 sessions.
Processing...
Processing...
Why does it take so long?
It took about 8 minutes to complete the process last time, but this time,
it takes more than 20 minutes.
Process is completed anyway, so I execute ANALYZE.
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 795
|
When I analyzed the table in previous issues, not even 2 records were stored in a single
block, but this time, the result showed that it was less than Case 2 (see Vol.133)
where maxtrans was set to 255.
Now, I check the block dump by every 100 blocks.
block ID # of transaction entries # of records
3 2 216
100 2 231
200 2 249
300 2 234
|
---Excerpts of block dump (block ID 100)---
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0013.04d.000000e8 uba: 0x0080258f.000e.01 CB-- 0 scn 0x0000.000f10bb
0x02 xid: 0x000c.020.000000e9 uba: 0x0080161a.0010.12 --U- 1 fsc 0x0000.000f1114
Leaf block dump
===============
header address 114984028=0x6da845c
kdxcolev 0
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 231 <- number of records
|
As maxtrans is set to 2, number of transaction entries should
be 2 or less. Take a look at the number of records above. Records seem to be stored
evenly in every block. The result shows obvious difference from the case where I analyzed the table.
If it is unable to allocate the transaction entry, is next block supposed
to be used?
It takes time to insert 200,000 data. Does this mean if it is unable to allocate
the transaction entry, a process waits for free space being created and stores data?
Why?
If a single block contains only 1 or 2 records, index may not function as it is supposed
to operate.
For example, when I execute a range scan to select 1,000 rows, a process simply
reads a few blocks. However, the process actually accesses to a few hundred blocks
and that is why it takes time to complete the insert process.
Regarding a table, on the other hand, table basically stores data and does not put much
emphasis on improving performance.
There is another reason why it takes more than 20 minutes to insert the data. Usually, indexes
are arranged in descending or ascending order. This time, I create an index in ID column
(DEFAULT SYSDATE) of tbl_shu1.
This means that data are inserted in order of indexes in a block. In other words, all sessions
attempt to insert the data to the same block.
I will continue covering this topic next week.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle