Ideal Extent Size

[shrikant_datex]

HI

What should be Ideal size for Extents in LMT?
For example I have Inventory_Data, Inventory_IDX tablespace where i am storing data , indexes created for Inventory Tables. both tablespaces are LMT
All the tables in the Invetory_Data are IOT and their secondary indexes are stored in the Inventory_IDX. The avg Row length for each table in the Indentory_Data is 400 bytes and around 12-50 bytes for tables in the Inventory_IDX.
All the tables in the Inventory tablespace will roughly contain 10 lack+ records and will have heavy updates and inserts on these tables and around 50-100 concurrent users will be playing with the system
Now, how should i set BLOCK_SIZE and EXTENT_SIZE for both.
IF BLOCK_SIZE = 8K and EXTENT_SIZE = 64 K
Avg Row Length : 400 bytes
No of Rows in the Block: 20
IF PCTINCREASE = 20 No of Free Rows per Block : 4
No of Rows per Block = 20-4 =16
No of Rows per Extent = 16 x 8 = 128
with same calculation i could have 162 rows per block for Index
tablespace tables


IF BLOCK_SIZE = 8K and EXTENT_SIZE = 1M
Avg Row Length : 400 bytes
No of Rows in the Block: 20
IF PCTINCREASE = 20 No of Free Rows per Block : 4
No of Rows per Block = 20-4 =16
No of Rows per Extent = 16 x 128 = 2048
with same calculation i could have 162 rows per block and 20864
rows per extent for Index tablespace tables

With this setting Can i able to avoid Internal fragmentation ?
How should i maintain INITTRANS , MAXTRANS, PCTFREE, PCTUSED
and other Segment space parameters ?
Will larger or reduced block size will help in this situation ?


Regards
Shrikant Kulkarni
Pune, India

[slimdave]

Have you considered just using automatic extent sizing and automatic segment space management?

[bazza]

You have way too much time on your hands.

Realistically, if you use LMT, you won't suffer from fragmentation. Have a read up on it.

Use ASSM, you're obviously unsure of what extent sizes you want, let oracle handle it for you.

Bazza

[gandolf989]

Have you considered just using automatic extent sizing and automatic segment space management?

I thought the answer was 42.

[shrikant_datex]

Hi All

I am using Oracle 8.1.6 Enterprise Manager Edition which does not support Segement space management. So by setting PCTFREE parameters I need to avoid ROW chaining..
I am really sorry..i have not mentioned it in Q.

Waiting for reply

[slimdave]

The principle behind PCTFREE is that it allows you to tell Oracle that a block must not be used for inserting new records if if has less than that Percent of free space available, thus reserving that space for the growth ofindividual rows due to updates. So, your setting for pctfree must reflect the liklihood that inserted rows will grow in size post-insert. If they will never grow then you can set PCTFREE=0 (a table of invoice details might be a candidate for this, as it is static once it has been generated).