solaris ufs and filesystemio_options with setall

From: staind <ulherr_at_yahoo.com>
Date: Thu, 12 Jul 2007 22:25:12 -0000
Message-ID: <1184279112.650914.158350@q75g2000hsh.googlegroups.com>

I have a quick question about direct i/o and the filesystemio_options parameter, in particular on ufs and Solaris. My understanding is that if we set this to setall (or directio), Oracle will, at its discretion, use direct i/o for file access. However, in the couple of machines I've looked at where I have this parameter set, Oracle is not doing any direct i/o whatsoever (I will explain in a minute how exactly I verify this). I am wondering if anyone has been able to get 10g to do direct i/o on a Solaris box using ufs, with filesystemio_options (as opposed to using the forcedirectio mount option)? I am trying to determine if there is something in my configuration prohibiting this, or if in fact Oracle is explicitly choosing not to use direct i/o for some reason, or if it's a bug.

There are two ways I can verify no direct i/o is happening in my case.

1. Via truss:

[ first flush the buffer cache ]
sys_at_BIA.WORLD> alter system flush buffer_cache;

System altered.

[ Then issue (as user oracle) ]
oracle_at_ironman:~$ truss -f -t open,ioctl -u ':directio' sqlplus user/ pass

... (lots of output here)

[ Then type ]
create table a as select * from big_table;

You will see that for every data file opened, directio is explicitly turned off:

24399:  open("/u04/oradata/BIA/APM_DATA13.dbf", O_RDWR|O_DSYNC) = 11
24399:  -> libc:directio(0x10f, 0x0, 0x1, 0x0)
24399:  ioctl(271, 0x2000664C, 0x00000000)              = 0

The 3rd parameter to the ioctl() call is 0 for directio_off, and 1 for directio_on.

2) Via the kernel stats. Here is a short snippet of code that will function similar to iostat, but for direct i/o activity:

alive_at_staind:/tmp/dio/kstat$ cat diomon.c
#include <stdio.h>
#include <kstat.h>

#define DELAY 3
#define LOOP 1

int main (int argc, char **argv) {

        kstat_ctl_t *kc;
        kstat_t *ksp;
        u_int delay, i = 0;
        int loop;

        struct ufs_directio_kstats {
                u_int   logical_reads;
                u_int   phys_reads;
                u_int   hole_reads;
                u_int   nread;
                u_int   logical_writes;
                u_int   phys_writes;
                u_int   nwritten;
                u_int   nflushes;
        } ks1, ks2;

        if (!(kc = kstat_open())) {
                perror("Error on kstat_open()");
                exit(1);
        }

        if (!(ksp = kstat_lookup(kc,
                                 "ufs directio",
                                 -1,
                                 "UFS DirectIO Stats"))) {
                printf("Cannot find \
                       [ufs directio:*:UFS DirectIO Stats]\n");
                exit(2);
        }

        if (argc > 1 && (atoi(argv[1]) > 0)) {
                delay = atoi(argv[1]);
        } else
                delay = DELAY;

        if (argc > 2 && (atoi(argv[2]) > 0))
                loop = atoi(argv[2]);
        else if (argc > 1)
                loop = -1;
        else
                loop = LOOP;

        if (kstat_read(kc, ksp, &ks1) == -1) {
                perror("Error in kstat_read()");
                exit(3);
        }
        while (i != loop) {
                if (!(i % 25))
                        printf("%10s%10s%10s%10s%10s%10s%10s%10s\n",
                               "lreads", "preads", "lwrites",
"pwrites",
                               "Kread", "Kwrite", "holeread",
"nflush");
                sleep(delay);
                if (kstat_read(kc, ksp, &ks2) == -1) {
                        perror("Error in kstat_read()");
                        exit(3);
                }
                printf("%10u%10u%10u%10u%10u%10u%10u%10u\n",
                       ks2.logical_reads - ks1.logical_reads,
                       ks2.phys_reads - ks1.phys_reads,
                       ks2.logical_writes - ks1.logical_writes,
                       ks2.phys_writes - ks1.phys_writes,
                       (ks2.nread - ks1.nread) / 1024,
                       (ks2.nwritten - ks1.nwritten) / 1024,
                       ks2.hole_reads - ks1.hole_reads,
                       ks2.nflushes - ks1.nflushes);
                i++;
                ks1 = ks2;
        }

}

Compile with:

gcc -o diomon diomon.c -lkstat

Run with:

./diomon 3

or similar.

bia_at_ironman:/tmp$ ./diomon 3
lreads preads lwrites pwrites Kread Kwrite holeread nflush

     0 0 0 0 0 0 0 0

     0 0 0 0 0 0 0 0

If there are any non-zero numbers, direct i/o is in fact happening. However in my case, there are none. Keep in mind the kernel level stats are per system, so it is easier to test this if no other directio is happening on the machine (i.e. no partitions mounted with forcedirectio and no programs explicitly using it (none that I know of do by default on Solaris)).

I have tried doing parallel queries, direct path loads, etc. but did not find any evidence of directio actually happening. Maybe Oracle is really doing "the right thing" and not using it, but I figured some activity may benefit from direct access.

Any comments are appreciated! Received on Thu Jul 12 2007 - 17:25:12 CDT