Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sun, 06 Sep 2009 17:18:24 +0200
Message-ID: <7gi260F2podgiU2_at_mid.individual.net>
On 05.09.2009 19:00, Mladen Gogala wrote:
> On Fri, 04 Sep 2009 18:04:37 -0700, Charles Hooper wrote:
>
>
> 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.
Date: Sun, 06 Sep 2009 17:18:24 +0200
Message-ID: <7gi260F2podgiU2_at_mid.individual.net>
On 05.09.2009 19:00, Mladen Gogala wrote:
> 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.
Mladen, you make it sound as if OS level caching was smarter than Oracle's own buffer cache strategies. In theory Oracle's code would have better knowledge about access patterns so my expectation would be that taking memory from OS buffer cache and giving it to the Oracle instance would lead to better performance than using OS level caching. Otherwise we should switch off Oracle's own buffering and use only OS features.
What am I missing?
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Sun Sep 06 2009 - 10:18:24 CDT