Re: Simple Oracle Query Taking Too Long

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.
>>

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 www.dizwell.com.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group