Oracle High Performance Tuning for 9i and 10g

In general larger block sizes are appropriate to read-only data warehouse-type databases. Smaller block sizes are appropriate to highly concurrent, highly active OLTP databases with small transactions. Block size can be 2K, 4K, 8K, 16K, and 32K. An OLTP database could have serious problems reading a few bytes of data from 32K blocks. A data warehouse would suffer immense performance problems reading huge volumes of data from block size of 2K because of the huge number of blocks it would have to read.

A block size of 8K is the Oracle Database default. I have never seen an OLTP Oracle database with a block size of less than 8K other than older versions of Oracle Database. Definitely beware of an OLTP Oracle database with 16K or 32K block size.

One other important factor is the DB_FILE_MULTIBLOCK_ READ_COUNT parameter.

The DB_FILE_MULTIBLOCK_READ_COUNT parameter allows the reading of multiple blocks at once. The default value for Oracle9i Database is 8 for OLTP databases. I generally set this value to 8 but it can be set to 4 or even 2 for OLTP databases depending on reporting and the nastiness of application SQL code. Any reporting or poorly written SQL code tends to warrant a higher setting for the DB_FILE_MULTIBLOCK_READ_COUNT parameter. Bad SQL code tends to execute more full scanning anyway. Setting the DB_FILE_MULTIBLOCK_READ_COUNT parameter higher will encourage more full physical scanning.

So beware of OLTP databases with block size larger than 8K and a high DB_FILE_MULTIBLOCK_READ_COUNT. Large blocks and large block read counts do not fit well with small transactions and high DML activity. The result could be wasted I/O and probably extensive locking and latch wait problems. Do not set high values for anything to do with block size for OLTP databases. Do the opposite for data warehouse databases. Let's look further at different types of databases briefly:

• OLTP/DSS Database.   A smaller block size of perhaps 8K is preferable. If some reporting is required then multiple block reads can be taken advantage of. Also poorly written SQL code is common, which may execute a lot of full scans. Locking and latch wait problems could result but it is best to keep to commonly used standards. A block size of 8K is a commonly used standard. A balance between low block contention and the ability to read large amounts of data is important. With larger block sizes lock levels can become incremental in that locks on rows can degenerate into block locks and sometimes even exclusive table locks.

• High DML Concurrency OLTP Database with Small Transactions.   Perhaps use a small block size, perhaps even 4K or 2K. These block sizes are generally not used but very good performance would be gained for small transactions. Additionally table row lengths must be short. Getting less block contention is important. A small block size is better for index searches. As a side issue very high concurrency databases can have concurrency parameters adjusted to help with multiple session access and updates. However, high concurrency settings reserve more block header space and reduce block space available for data.

• Data Warehouse or Read-Only.   Use the largest block size possible (32K) assuming there is low concurrency. Higher concurrency should probably have the block size changed to 16K. Reading large amounts of data at once is important with serial access. Larger block size is better for full table scans and tends to encourage them.

  Tip

  Oracle9i Database allows multiple block sizes so there is no reason why different tables in any type of database could not be placed into different block-sized tablespaces. Many issues can be resolved by using multiple block sizes effectively providing multiple, nonconflicting pools of data at both the physical I/O and memory buffer levels. Multiple block-sized tablespaces can replace the KEEP and RECYCLE database buffer pools of Oracle8i Database.