Re: directio performance question

On Sun, 06 Jul 2003 04:56:53 -0700, Max wrote:

> Hi,
>
> I've got what is probably for most of you a very basic question...
> From what I've read, in solaris, mounting an oracle filesystem with
> the
> "forcedirectio" option gives it upto 80% of the performance of having
> oracle on raw devices by eliminating the double buffer.
>
> I ran a small test and seem to get the opposite result. Can someone
> tell me if I am doing something wrong ?
>
> without forcedirectio
> ---------------------
> SQL> set timing on
> SQL> create index idx_dou_buff on INDEX_TEST(TIMESTAMP);
>
> Index created.
>
> Elapsed: 03:01:32.08
>
> Then dropped the index, stopped orcle, unmounted the filesystems and
> remounted it with the directio option
>
> With directio
> ---------------
>
> SQL> create index idx_dou_buff on INDEX_TEST(TIMESTAMP);
>
> Index created.
>
> Elapsed: 03:58:27.01
>
>
> WHy is an index creation taking more time with directio ?
>
> Solaris 8 latest patches and Oracle 8i
>
> Thanks for any comments.
>
> M

1. Direct IO flag is file system specific. File system must know how to bypass the buffer cache. UFS cannot do direct I/O. VxFS can, if you don't have only the free version. I'm not acquainted with forcedirectio option but it will most likely be ignored if the FS doesn't do direct IO. The same happens when somebody tries to specify O_DIRECT flag on a file system that doesn't support it. To get direct IO, you still need help of the Mighty Bucks.
2. If the table and the index are small, then unix and the buffer cache will actually help index creation, because of the prefetch and in-memory sorting, as opposed to disk I/O. The influence of the direct IO is frequently overrated. Tune your database well and it will work well. -- Mladen Gogala The most costly of all follies is to believe passionately in the palpably not true. It is the chief occupation of mankind. H.L. Mencken