JL Computer Consultancy

Picking an Oracle database block size to suit your hardware

May 1997


There are many arguments about how to choose the best Oracle block size for your application, and whether raw devices are significantly faster than file systems, and whether Fast File Systems (such as JFS a.k.a. VxFS) would be better than simple Unix File System.

The attached pair of simple C programs allows you to get an idea of how well different devices (logical and physical) on your system might work at different sizes of Oracle block. Basically there is a writer program and a reader program. Each takes several parameters to allow you to select a file, write in arbitrary sizes, an arbitrary number of times, choosing to do random or serial access, and setting a meaningful boundary to the start of read/write so that you don't get odd effects from overlapping accesses.

You may want to add some sophistication to the programs, but the key feature is in the writer program, which opens a file with the O_DSYNC call - matching the call made by Oracle for all writes: this is the 'data sync' call which updates the file without updating the file's metadata.

There are some platforms that do not use the standard file handling calls (Pyramid RM1000, for example, seem to use a SYSMIPS() call); so do check that Oracle on your platorm is opening and handling files in the same way as these programs before relying on the results they produce.

To compile the programs, all I have ever needed to do is:

        cc reader.c -o reader
        cc writer.c -o writer

This program is referenced in a Powerpoint presentation comparing raw (logical) devices, Unix file systems, and Fast file systems.

Go to reader program

Go to writer program

Back to Main Index of Topics


reader.c

/*
        program        reader.c
        author         Jonathan Lewis
        date           29-May-1997
        purpose        Exercise file-reading
        This C program will open a file for reading, then read it repeatedly
        either sequentially or randomly.  
        The user specifies 
               The file name
               Whether reads should be sequential or random (R/S)
               The size of each read request (up to 256K)
               A boundary at which each read should start
               The number of reads to make
               A randomising seed for the random number generator
        Compiling:
               cc reader.c -o reader
        Sample of usage:
               reader /tmp/bigfile R 8192 65536 1000 17
                       read /tmp/bigfile randomly, 
                       8K reads on 64K boundary, 
                       1000 reads
               read /tmp/bigfile S 32768 32768 100
                       read /tmp/bigfile 
                       sequentially and contiguously 32K at a time, 
                       100 reads.
        Notes:
               rand() returns a random number between 0 and 32767
               Make sure that rand() * iSkipSize is less than the file size
               You could shift rand() to restrict it further, 
                       e.g. (rand() >> 1) halves the range to 0 .. 16383
                       e.g. (rand() >> 2) halves it twice to 0 .. 8191
               
*/
 
#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include <fcntl.h>
#include <unistd.h>
main (argc,argv)
        int     argc;
        char    **argv;
{
        char    szFilename[80];
        char    szRandom[20];
        size_t  mybufsize;
        int     iSkipSize;
        int     iReadCt;
        int     iRandSeed;
        int     iRandom;
        int     iFileDes;
        ssize_t nbytes;
        char    mybuf[262144];
        off_t   whereami;
        int     iLoopCt;
        strcpy(szFilename,argv[1]);
        printf("File name:          %s\n",szFilename);
        strcpy(szRandom,argv[2]);
        printf("Random or Serial:   %s\n",szRandom);
        
        mybufsize = (size_t) atoi(argv[3]);
        printf("Read Size:          %8i\n",mybufsize);
        
        iSkipSize = atoi(argv[4]);
        printf("Boundary:           %8i\n",iSkipSize);
        
        iReadCt = atoi(argv[5]);
        printf("Read Count:         %8i\n",iReadCt);
        iRandSeed = atoi(argv[6]);
        printf("Rand Seed:          %8i\n",iRandSeed);
        iFileDes = open(szFilename, O_RDONLY);
        printf("Descriptor:         %8i\n",iFileDes);
        if (szRandom[0] == 'R')
               iRandom = 1;
        else
               iRandom = 0;
        srand(iRandSeed);
        for (iLoopCt = 0 ; iLoopCt < iReadCt ; iLoopCt++ ) {
               if (iRandom)
                   whereami = lseek(iFileDes,(rand()) * iSkipSize, SEEK_SET);
               nbytes = read(iFileDes,mybuf,mybufsize);
        }
        close(iFileDes);
}

Back to Main Index of Topics

 


writer.c

/*
        program        writer.c
        author         Jonathan Lewis
        date           29-May-1997
        purpose        Write blocks to a file using O_DSYNC like Oracle
        This C program will open a file for writing, then write to it 
        repeatedly either sequentially or randomly.  If the file does not
        exist already it will be created (if possible)
        The user specifies 
               The file name
               Whether writes should be sequential or random (R/S)
               The size of each write request (up to 256K)
               A boundary at which each write should start
               The number of writes to make
               A randomising seed for the random number generator
        Compiling:
               cc writer.c -o writer
        Sample of usage:
               writer /tmp/bigfile R 8192 65536 1000 17
                       write /tmp/bigfile randomly, 
                       8K reads on 64K boundary, 
                       1000 writes
               writer /tmp/bigfile S 32768 32768 100
                       write /tmp/bigfile 
                       sequentially and contigiously 32K at a time, 
                       100 writes.
        Notes:
               rand() returns a random number between 0 and 32767
               Make sure that rand() * iSKipSize is less than the file size
               You could shift rand() to restrict it further, 
                       e.g. (rand() >> 1) halves the range to 0 .. 16383
                       e.g. (rand() >> 2) halves it twice to 0 .. 8191
               
               I had some trouble with permissions when creating new files 
               with this program.  I had to create a file, then use the chmod
               Unix command on it.
        Suggested Strategy:
               Wrap the time/timex command around the program
               Use this program in serial mode to create a file.
               Use chmod to make the file readable/writable
               Use reader/writer to test the speed of random access
*/
 
#include <stdlib.h>
#include <stdio.h>
#include <time.h>
#include <fcntl.h>
#include <unistd.h>
#include <stdlib.h>
main (argc,argv)
        int     argc;
        char    **argv;
{
        char    szFilename[80];
        char    szRandom[20];
        size_t  mybufsize;
        int     iSkipSize;
        int     iWriteCt;
        int     iRandSeed;
        int     iRandom;
        int     iLoopCt;
        int     iFileDes;
        int     whereami;
        ssize_t nbytes;
        char mybuf[262144];
        strcpy(szFilename,argv[1]);
        printf("File name:          %s\n",szFilename);
        strcpy(szRandom,argv[2]);
        printf("Random or Serial:   %s\n",szRandom);
        
        mybufsize = (size_t) atoi(argv[3]);
        printf("Write Size:        %8i\n",mybufsize);
        
        iSkipSize = (size_t) atoi(argv[4]);
        printf("Boundary:          %8i\n",iSkipSize);
        
        iWriteCt = atoi(argv[5]);
        printf("Write Count:       %8i\n",iWriteCt);
        
        iRandSeed = atoi(argv[6]);
        printf("Rand Seed:         %8i\n",iRandSeed);
        
        iFileDes = open(szFilename, O_RDWR | O_CREAT | O_DSYNC);
        printf("Descriptor:        %8i\n",iFileDes);
        if (argv[2][0] == 'R')
               iRandom = 1;
        else
               iRandom = 0;
        srand(iRandSeed);
        for (iLoopCt = 0 ; iLoopCt < iWriteCt ; iLoopCt++ ) {
               if (iRandom)
                   whereami = lseek(iFileDes,(rand()) * iSkipSize, SEEK_SET);
               
               nbytes = write (iFileDes, (void *)mybuf, mybufsize);
        }
        close(iFileDes);
}

Back to Main Index of Topics