Re: Surprising Performance Changes with Oracle (Long Post)

From: Mladen Gogala <>
Date: Sat, 5 Sep 2009 17:00:53 +0000 (UTC)
Message-ID: <h7u5g4$9c0$>

On Fri, 04 Sep 2009 18:04:37 -0700, Charles Hooper wrote:

> Obviously from the above, is significantly faster at the index
> access than is on the same platform. But wait, I forgot
> something. On on Linux I had enabled direct I/O and
> asynchronous I/O by setting the FILESYSTEMIO_OPTIONS parameter to
> SETALL. I did not change that parameter on, so it defaulted to
> NONE. What happens when the FILESYSTEMIO_OPTIONS parameter is set to
> * Oracle now required 42.45 seconds for the full tablescan
> while the index access path required one hour, 16 minutes and 46
> seconds.

Charles, that is to be expected, although this is a bit drastic difference. Most file systems do read prefetch and even asynchronous reads of the blocks when using buffer cache. Also, just as Oracle RDBMS, Unix derivatives also have smart strategies for keeping the hot blocks in the cache, which results in rather drastic performance gains. If you start doing direct I/O, lose all the help from the system buffer cache and OS. You can help things by using readahead* RH EL services and "blockdev" command to increase read-ahead.

Received on Sat Sep 05 2009 - 12:00:53 CDT

Original text of this message