DBA Tips Archive for Oracle

  


Choosing the Correct Database Block Size

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. An Approach to Selecting an Appropriate Block Size
  3. Example Code Repository

Overview

A common question DBAs have when creating the database is how to choose an appropriate block size. It is a known concern that choosing a block size that is improper for your file-system configuration can have significant impact on performance. Among the things to consider are of course:

An Approach to Selecting an Appropriate Block Size

Something that is often overlooked is whether the file system is going to prefer the Oracle block size selected. What makes this decision even more important, is that the DBA cannot change the block size of the database without having to drop the database and re-create it with the correct block size.

Both Oracle and the file system use its block size as the smallest unit of internal storage for both reading and writing. It is crucial that both Oracle and the file system are configured with an appropriate block size in order to provide for efficient disk IO.

On average, a write to the file system in which the file system can immediately write (the first time), it only has to wait for a half a rotation for the correct portion of the disk to come under the write head. In the case where the Oracle block size is smaller than the file system block, a write to the file system has to work in two passes and has to wait on average of 1.5 rotations.

If you would like to explorer whether a particular block size is a good (or bad) fit for your file system, I provided a C program that attempts to emulate a busy database writer (DBWR) writing to a large file. Also in the Example Code Repository is another C program that emulates a server process that can read (both random and sequential) the previously written file.

Example Code Repository

Writer Code
    writer.c
    writer.out
Test writing blocks to a file using O_DSYNC (like Oracle)

Compiling the progam:
cc writer.c -o writer

This C program will open a file for writing (matching the call made by Oracle), then write to it repeatedly either sequentially or randomly. If the file does not already exist it will be created.

Reader Code
    reader.c
    reader.out
Test "file-reading" for a particular block size

Compiling the progam:
cc reader.c -o reader

This C program will open a file for reading (matching the call made by Oracle), then read it repeatedly either sequentially or randomly.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 18-Nov-2010 18:36:04 EST
Page Count: 12341