What size Records per block should I use?

[maday15]

I am trying to read and figure out what size records per block I should use and I want to verify what I came up with. This is for a storage area that has one table in it. The record mean size from tabanalys is 3240, size of storage area is 74.9 gb. The database blocksize is 8k, there are currently 24,821,989 records in the table. Using the calculation of 8192/3240, I get 2.52 which means I should use either 2 or 4 records per block. I'm not sure which one is better. Currently it is 64 records per block and 64 blocks per cluster (Type 2 storage areas), I plan on doing a dump and load and think it would be a good time to move this to a new storage area that is set up properly. This table is pretty static after a row is inserted. From what I am reading I think it should be 4 records per block and 512 blocks per cluster, am I missing something or does this sound about right?

 

[Casper]

it depends on the data distribution also. (still waiting for statistical data like standard deviation in the output from dbanalys ).

What is the max size and what is the min size?
What is the fragmentation you have right now for this table?

Casper.

 

[maday15]

The min size is 76 the max is 4075. The fragmentation: Count = 24821989, Factor = 1. Scatter factor is 1.1, just in case you needed that too.

 

[TomBascom]

With a max record size around 4k and an average around 3k you probably have a fairly uniform distribution of record sizes with a low standard deviation so I'd go with 4 and 512.

 

[maday15]

Thanks Tom!