Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
Date: Sat, 5 Sep 2009 17:00:53 +0000 (UTC)
Message-ID: <h7u5g4$9c0$2_at_solani.org>
On Fri, 04 Sep 2009 18:04:37 -0700, Charles Hooper wrote:
> Obviously from the above, 11.2.0.1 is significantly faster at the index
> access than is 11.1.0.6 on the same platform. But wait, I forgot
> something. On 11.1.0.6 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 11.2.0.1, so it defaulted to
> NONE. What happens when the FILESYSTEMIO_OPTIONS parameter is set to
> SETALL?
> * Oracle 11.2.0.1 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.
-- http://mgogala.freehostia.comReceived on Sat Sep 05 2009 - 12:00:53 CDT