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

On 6 Jul 2003 04:56:53 -0700, trestusler@hotmail.com (Max) wrote:

>WHy is an index creation taking more time with directio ?

You are posting insufficient information.
forcedirectio applies to ufs filesystems only. If you have vxfs
filesystems with only the *basic* edition of Veritas installed,
forcedirectio will not work.
Also you should truss your process to check whether it really doesn't
use forcedirectio, and check your init.ora for disk_async_io = true.
If you have the parameter set to true and your disks don't support
directio a truss will show you your lwp.kaio requests fail and Oracle
is reverting to normal buffered io. That is a perfect reason for a
system with forcedirectio erroneously set to true actually performing
slower, because each io request will be processed 2 times.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

Sybrand Bakker <gooiditweg@sybrandb.demon.nl> wrote in message news:<ikfggvofqif50ci4p5kggkbrqfo887dbom@4ax.com>. ..
> On 6 Jul 2003 04:56:53 -0700, trestusler@hotmail.com (Max) wrote:
>
> >WHy is an index creation taking more time with directio ?
>
> You are posting insufficient information.
> forcedirectio applies to ufs filesystems only.
Thanks for the reply.
Sorry, it is UFS. didnt mention this because ufs is the default.

> disk_async_io = true.
isnt this the default ? Anyway a show parameters shows this to be
true.

> directio a truss will show you your lwp.kaio requests fail and Oracle
> is reverting to normal buffered io.

This seems to be what is happening. truss reports

kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL


Reading some more newsgroup postings and articles from Sun it seems
kaio is currently only available on raw and QIO with Veritas
filesystems...

Guess I'll need to do a lot more reading but hopefully someone here
can give a quick answer.

Is there any performance benefit to having a plain ufs filesystem
mounted forcedirectio on solaris for oracle?

Max
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

trestusler@hotmail.com (Max) wrote in message news:<5a4548ca.0307061454.8ea7291@posting.google.c om>...
> Sybrand Bakker <gooiditweg@sybrandb.demon.nl> wrote in message news:<ikfggvofqif50ci4p5kggkbrqfo887dbom@4ax.com>. ..
> > On 6 Jul 2003 04:56:53 -0700, trestusler@hotmail.com (Max) wrote:
> >
> > >WHy is an index creation taking more time with directio ?
> >
> > You are posting insufficient information.
> > forcedirectio applies to ufs filesystems only.
> Thanks for the reply.
> Sorry, it is UFS. didnt mention this because ufs is the default.
>
> > disk_async_io = true.
> isnt this the default ? Anyway a show parameters shows this to be
> true.
>
> > directio a truss will show you your lwp.kaio requests fail and Oracle
> > is reverting to normal buffered io.
>
> This seems to be what is happening. truss reports
>
> kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF) Err#22 EINVAL
>
>
> Reading some more newsgroup postings and articles from Sun it seems
> kaio is currently only available on raw and QIO with Veritas
> filesystems...
>
> Guess I'll need to do a lot more reading but hopefully someone here
> can give a quick answer.
>
> Is there any performance benefit to having a plain ufs filesystem
> mounted forcedirectio on solaris for oracle?
>
As you have already seen there isn't. Set disk_async_io to false and
you should see a performance benefit.

Sybrand Bakker
Senior Oracle DBA

a quick answer.
> >
> > Is there any performance benefit to having a plain ufs filesystem
> > mounted forcedirectio on solaris for oracle?
> >
> As you have already seen there isn't. Set disk_async_io to false and
> you should see a performance benefit.
>
Not to drag this on but searching metalink for solaris forcedirectio
etc I see that Oracle and a lot of dba's recommend the forcedirectio
option for plain UFS on solaris for performance...

I'll stop now and go back to sysadmining which I understand a little
better than dbaing ...

Thanks.

> Sybrand Bakker
> Senior Oracle DBA

Hi, I think your test is not a good test for this purpose.
You are doing a DDL. I am not sure what gets written to redo
in this case. I think it is probably just the DDL SQL without
the physical data ( or logical changes only ).

Although writes do affect db writers, however because they
are not real time, using timing in sqlplus won't tell you
anything, unless you happen to bump into a checkpoint.

So the best way to test this is to test your log writer, not
db writer.

If you test DML you would notice big difference. I have
benchmarked inserts before. Your main interest would be
log writer CPU usage and 'log file sync' wait time.
DIO performs fairly close to QIO.

Solaris has forcedirectio Veritas has convosync option
that you can set to "direct".

Perform a simple test without DIO of inserting a large number of rows
and get the difference of 'log file sync' before and after
and then do the same with DIO. The difference is several fold.

It is harder to collect exact redo CPU % usage without proper tools,
but you can get a rough idea using ps.

Of course your redo needs to be on the DIO volume.

On our dev environment, I noticed significant improvement (around 25%)
in disk based sorts (specifically improvements in "direct path write"
event) after mounting the filesystem in "forcedirectio" mode.
Apart from bypassing the file system buffers, this option also
eliminates the single writer lock (at the unix level) resulting in
better performance.

-Madhu S



trestusler@hotmail.com (Max) wrote in message news:<5a4548ca.0307060356.2c4c2c8a@posting.google. com>...
> 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