Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple Oracle Query Taking Too Long

Re: Simple Oracle Query Taking Too Long

From: DA Morgan <>
Date: Thu, 07 Sep 2006 18:38:05 -0700
Message-ID: <>

Frank van Bortel wrote:
> DA Morgan schreef:

>> That is about as horrible a definition as I can imagine. I can't think
>> of a single justification for either the 64K extents or a pctincrease
>> that is anything except 1.

> And what would be wrong with 64kB extents?
> I'm off to the Chimay :D Sayonara!

The problem with 64K extents is one Howard Rogers ranted about many years ago here at c.d.o. The fact that it doesn't match any operating system. Or at least not Windows or any variant of UNIX or Linux.

Here is what Howard wrote:

Choosing a Block Size

The answer to the question "what is a good block size?" is: "It depends entirely on your operating and file system."

This, of course, is not what Oracle itself teaches on its courses, nor what the Oracle Press books say, nor what the usual 'DBA Folklore' suggests. All these resources tend to say "It depends on the type of application you are running." They go on to say that for OLTP environments, you should go for small blocks (say, 2K or 4K). But for a data warehouse, hyou should go for big blocks (say 8K, 16K or even 32K).

This is, of course, complete rubbish since it rather overlooks one tiny, but crucial, fact: Databases have a file system to contend with, since data files don't live in a vacuum, but on a disk that has been formatted with a file system. And on Unix, the file system has a buffer of its own that needs to be filled precisely. That buffer is usually 8K in size, so a choice of any other block size will result in additional I/O operations, and hence degraded performance. There's no magic abou tthis: it's just a question of physics.

For those not familiar with Howard you can find him at

Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Thu Sep 07 2006 - 20:38:05 CDT

Original text of this message