oracle 选择数据块大小

选择数据块大小

A block size of 8 KB is optimal for most systems.However, OLTP systems occasionally use smaller block sizes and DSSsystems occasionally use larger block sizes. This section discussesconsiderations when choosing database block size for optimalperformance and contains the following topics:

大部分oracle系统数据块大小设为8KB。然而，OLTP有时候使用比8KB小的块，DDS有时候使用比8KB大的块。这部分主要在下面三个方面讨论选择数据块的注意事项。

• Reads

• Writes

• Block Size Advantages andDisadvantages

Note:

The use of multiple block sizes in a single database instance isnot encouraged because of manageability issues.
因为运维管理方面的问题，一般不建议使用multiple block size。

Reads

Regardless of the size of the data, the goal is to minimize thenumber of reads required to retrieve the desired data.

不管数据的大小,目标是最小化检索所需的数据读的次数。

• If the rows are small and access is predominantly random, thenchoose a smaller block size.

• If the rows are small and access is predominantly sequential,then choose a larger block size.

• If the rows are small and access is both random and sequential,then it might be effective to choose a larger block size.

• If the rows are large, such as rows containing large object(LOB) data, then choose a larger block size.

Writes

For high-concurrency OLTP systems, consider appropriate valuesfor INITRANS,MAXTRANS, andFREELISTS when using a larger block size. Theseparameters affect the degree of update concurrency allowed within ablock. However, you do not need to specify the value forFREELISTS when using automatic segment-space management.

对于高并发OLTP系统，考虑INITRANS, MAXTRANS, andFREELISTS的值。这些参数影响update的并发度。

If you are uncertain about which block size to choose, then trya database block size of 8 KB for most systems that process a largenumber of transactions. This represents a good compromise and isusually effective. Only systems processing LOB data need more than8 KB.

如果你不能确定选择多大的数据库，可以尝试8KB的数据块。

See Also:

The Oracle Database installation documentation specific to youroperating system for information about the minimum and maximumblock size on your platform

Block Size Advantages andDisadvantages

lists the advantages and disadvantages of different blocksizes.

不同的块大小的优点和缺点

Block Size Advantages andDisadvantages

Block SizeAdvantagesDisadvantages

Smaller

Good for small rows with lots of random access.

Reduces block contention.

Has relatively large space overhead due to metadata (that is,block header).

Not recommended for large rows. There might only be a few rowsstored for each block, or worse, row chaining if a single row doesnot fit into a block,

Larger

Has lower overhead, so there is more room to store data.

Permits reading several rows into the buffer cache with a singleI/O (depending on row size and block size).

Good for sequential access or very large rows (such as LOBdata).

Wastes space in the buffer cache, if you are doing random accessto small rows and have a large block size. For example, with an 8KB block size and 50 byte row size, you waste 7,950 bytes in thebuffer cache when doing random access.

Not good for index blocks used in an OLTP environment, becausethey increase block contention on the index leaf blocks.