oracle 选择数据块大小

翻译 2013年12月05日 11:10:19


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:



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


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.


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.


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 Size Advantages Disadvantages


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,


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.





  • 2013年05月24日 01:52
  • 48KB
  • 下载

Oracle 海量数据处理- 索引的选择

在Oracle数据库中,索引按照索引机制的不同,可以分为三种。 1. B-Tree索引 B-Tree index 应用场景: OLTP 使用比较多,处理键值重复率比较低的字段比较适合使用B-Tre...
  • qptufly
  • qptufly
  • 2013年06月29日 17:56
  • 2088


  • 2016年07月18日 13:47
  • 2KB
  • 下载

Oracle 中常用数据字典大总结

  • 2013年09月09日 14:06
  • 7KB
  • 下载


Oracle数据库容灾方案的选择 - 容灾首先是一个概念,要认识到为什么做容灾,才能做好容灾。世界上没有卖后悔药的,当灾难降临了,如果没有行之有效的数据保护、数据恢复的容灾措施,带来不可预估的损失将...
  • bjqwite
  • bjqwite
  • 2012年07月13日 11:39
  • 632

Oracle 数据坏块处理

  • 2011年10月27日 17:02
  • 289KB
  • 下载


  • 2013年03月30日 21:36
  • 167KB
  • 下载


手动迁移原创博客,原文发表在 一.dump数据块 oracle的rowid中包含着...
您举报文章:oracle 选择数据块大小