Storage settings for a large insert only table

[mb]

Can anyone tell me what are good settings for a large insert only table ie pctfree, pctused, initial extent, maxextent?

This table currently has 30 million rows and 500 are added daily.

[pipo]

insert only > small pctfree (let's say 5), big pctused (~90)
but as far as initial and next size are concerned, it mainly depends of the average row length, which we cannot guess ...

[jgmagnus]

Hopefully the table already exists somewhere and you can do an analyze table command and check it out in dba_tables to get the avg row length etc.

good luck!

- Magnus

[marist89]

Originally posted by pipo
insert only > small pctfree (let's say 5), big pctused (~90)

Depends on what you are looking for. Are you looking to maximize storage? Then these settings are probably fine.

If you are looking to maximize performance, you should set the pctused to a low value to avoid blocks being linked and unlinked from the free list.

As far as the initial and next extents, use a Locally managed tablespace with automatic extent management and don't worry about it.

[mb]

Jeff,

Do the blocks in this table get effected by the freelist if the table is insert only?

Here are the stats:
PCTFREE=10
PCTUSED=80
Avg_row_length=32
Initial_extent = 1000Mb

This table does not get updated after it is inserted into, but there may be a possibility that older data maybe moved to an archive table.

[marist89]

Just inserting, no. However, if this is true:

Originally posted by mb
there may be a possibility that older data maybe moved to an archive table.

then you will have deletes. If a block gets down to 79% full, it will get put on the free list. If you are deleting a lot of data, the chances are good that you will get many blocks that are 79% full. These blocks then get put on the free list. When you go back to inserting data, Oracle will seach all these blocks only to use another 12% (91-79) of the block. This would yield good utilization of the block, but slower insert performance.

If the PCTUSED was set to a smaller number, say 10, then the block would have to get down to 9% before it was put back on the free list. When the block finally got back on the free list, you would have 82% (91-9) use of the block before it goes off the freelist again. Since this would leave unusable space in the block until you get down to 9% you would "waste" space but your inserts would go much faster because they would not have to search an artificially long freelist.