Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!q75g2000hsh.googlegroups.com!not-for-mail From: staind Newsgroups: comp.databases.oracle.server Subject: solaris ufs and filesystemio_options with setall Date: Thu, 12 Jul 2007 22:25:12 -0000 Organization: http://groups.google.com Lines: 170 Message-ID: <1184279112.650914.158350@q75g2000hsh.googlegroups.com> NNTP-Posting-Host: 199.171.212.107 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1184279112 23391 127.0.0.1 (12 Jul 2007 22:25:12 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 12 Jul 2007 22:25:12 +0000 (UTC) User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: q75g2000hsh.googlegroups.com; posting-host=199.171.212.107; posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0 Xref: usenetserver.com comp.databases.oracle.server:431066 X-Received-Date: Thu, 12 Jul 2007 18:25:12 EDT (text.usenetserver.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@BIA.WORLD> alter system flush buffer_cache; System altered. [ Then issue (as user oracle) ] oracle@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@staind:/tmp/dio/kstat$ cat diomon.c #include #include #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@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!