|
Transaction Entry
|
In this issue, I review and summarize the analysis of initrans and maxtrans.
System Environment
OS: Windows2000 Server
Oracle:8.1.7
DB_BLOCK_SIZE:8K
I used a table/index with maxtrans=255 (actually with maxtrans=169), a table with
maxtrans=1, and an index with maxtrans=2 and inserted records from a single
session and 200 sessions to examine the behavior of transaction entry
and data in a block.
Here is a summary.
(1)maxtrans (table maxtrans 1)
I inserted 1000 records each from 200 sessions to a single table with maxtrans=1.
(I inserted 200,000 records in total. The length of a record was 17 bytes excluding the overhead.)
The result indicated that 150,000 blocks were used for this process which was hundred times
more than the blocks used in the process inserting data from 1 session.
If number of records that can be allocated to a block reaches maxtrans, Oracle allocates
new block to prevent other sessions from being in queue. Otherwise, performance slows down.
Furthermore, block that reach maxtrans is unlinked from freelist
(2)maxtrans (index maxtrans 2)
Likewise (1), I inserted 1000 records each from 200 sessions to an index with maxtrans=2,
The process did not consume blocks in large quantity but made other sessions be in queue
until transaction entry had free space. This caused performance slowdown.
Index puts much emphasis on performance of index scan. Besides, indexes are stored in ascending
order and always access the same block.
(3)maxtrans (index maxtrans 169)
Likewise (2), I inserted 1000 records each from 200 sessions to an index with maxtrans=255
(actually with maxtrans=169). The process required blocks 2.8 times more than which the process
inserting records from a single session required.
Space to be used by transaction entry consumed space for data in large quantity and there
is no space sufficient to store data.
Conclusion
Most of you are not likely to set maxtrans to 1 as I have done to analyze transaction entry.
However, suppose concurrent heavy transactions attempt to access a site (e.g. transactions
from 1000 sessions at a time), what should we do to have better performance?
As maxtrans can be set up to 255, set maxtrans to appropriate value to prevent
this site from facing performance slowdown.
For example, if a table encounters concurrent heavy accesses, it is recommended that you have
multiple freelists to avoid heavy access to a single block.
By doing this, appropriate number of blocks can be used, and transaction entry does
not need to consume space unnecessarily.
Regarding an index, if indexes are stored in ascending order, indexes do not consume blocks
unnecessarily. However, performance slowdown may occur because the process waits for transaction
entry to have free space.
Even though you have multiple freelists, it does not improve index performance at all.
If range scan is not performed, reverse key index may operate effectively.
Reverse key index stores column information in reverse. Thus, it is effective
if indexes are stored in ascending order.
(data)
22:02:15
22:02:16
22:02:17
(reverse key index)
51:20:22
61:20:22
71:20:22
By doing this, you can distribute accesses among leaf blocks.
If you need the range scan, you may add some information to the head of index
to distribute accesses. For example, in addition to a column where SYSDATE is stored,
you may have another column to improve performance.
Following is an example of adding APPS column. This column stores information
of which application server is used to access. Put this column to the top of the index
so that concurrent accesses can be distributed among leaf blocks.
(Current table)
ID DATE DEFAULT SYSDATE <- index
TEXT CHAR(10)
|
(Table where a new column is added)
ID DATE DEFAULT SYSDATE <- 2
TEXT CHAR(10)
APPS NUMBER(2) <- 1
CREATE INDEX ... ON TBL(APPS,ID)...
|
It is not a good idea to put a column with high cardinality to the top of
the index because it does not operate sufficiently for range scan.
There may be other ways to avoid performance slowdown. If I ever have a chance,
I will cover this topic again in the future.
New topic will start next week.
That's it for today.
Motohiro Ooba
|
|
Ora! Ora! Oracle