How can I tell if Direct I/O is being used by Oracle using Online JFS

[Laxman Seethamraju_2]

Hi,

We are running Oracle 9.2.0.4 on HP UX11.11 64 Bit. Using Online JFS 3.3.

How can we tell if Oracle is using Direct I/O , Discovered Direct I/O.
File system are not explicitly mounted to use directio. (convosync is not set) and are not raw.

Read some where that Oracle will automatically use "Discovered Direct I/O" even if FS has missing option for convosync.

How can I validate that.


Laxman

[James A. Donovan]

Take a look at the vxtunefs man page. Particularly, the discovered_direct_iosz. By default only I/O that is larger than 256KB is handled via discovered direct I/O. So only if Oracle is writing out in 256KB+ chunks will those writes bypass the Unix buffer cache.

[Laxman Seethamraju_2]

Agreed. Is there any way to confirm if it is using direct i/o.

Any sar/oracle v$ tables or glance metrics which can point this out.

[Jeff Schussele]

Hi Laxman,

Well, I'd see how they are indeed mounted - run

mount -v | grep mnt_point

and see whether you have convosync=direct, mincach=direct. Because if you don't, it's I/O *will* go through the OS buffer cache & through the cache defined in Oracle's SGA. And that's generally not optimal for data & indices. But you usually want the redo & archive traffic going through the buffer cache. Usually, but not always.

Rgds,
Jeff

[Laxman Seethamraju_2]

Jeff,

mount output is as follows
/dev/arraypvg01/lvol1 on /database1 type vxfs log on Sat Mar 17 14:26:35 2004

The question is will HP 11.11 use Programmable Direct IO as Sun does. Or do we need to force convosync=direct on file system

If sar -b
shows
00:00:01 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
08:40:01 1598 29193 95 44 7421 99 0 0

pread prwite are consistently 0 . Does it mean no direct IO is happening.
Should I expect pread/pwrite to be no zero if direct is being used.

Oracle always attempts direct I/O by defualt and the OS translates it to direct IO. On HP does it automatically transalate to Direct I/O on JFS 3.3.?

[James A. Donovan]

You can use tusc (HP-UX's equivalent of truss) to trace a given Oracle shadow process to see how large (small) it's I/O is.

Oracle I/O size is generally determined by a combination of init.ora parameters: db_block_size, db_multiblock_read_count and db_file_direct_io_count can all influence the I/O size.

Another test you might try is a direct path export while monitoring pread/pwrite with sar -b. If db_file_direct_io_count*db_block_size > discovered_direct_iosz then I believe you should see pwrite be >0.

[Laxman Seethamraju_2]

I did observe that export with direct=y is creating pwrites.

Is it safe to assume then that during the day oralce is not using direct i/o. We have 2 options now either increase the nbuf or move to forced direct on F/S.

Any thoughts.

[James A. Donovan]

Your other option is to use vxtunefs (or, /etc/vx/tunefstab) to lower the value of discovered_direct_iosz for the filesystems/disks you're concerned with.

[Jeff Schussele]

Hi (again),

Short answer - yes.
IF you don't set convosync=direct,mincache=direct that FS *will* use buffer cache.
Good news is that you can reset the options on-the-fly - like as follows:

mount -F vxfs -o remount,convosync=direct,mincache=direct /mnt_point

You need OnLine JFS & JFS v3.3 or higher to do this, but I think you have these.

Rgds,
Jeff

[Glenn MacCrimmon]

I'm in the same situation...Oracle 9.2.0.5, JFS 3.3, with the f/s mounted convosync=direct,mincache=direct and init.ora "filesystem_options=directio". Our server has 16gb RAM and the SA left dbc_max_pct at 50 so it has an 8gb UBC. After loading a 1.5gb table, all Oracle "db file sequential read"s show 850 microseconds on average which looks suspiciously like a UBC hit, not a direct read. I would have though the single block direct reads to be around 5 milliseconds (average for our SAN).

tusc on the shadow process and the dbwr process show io sizes of 8192, our Oracle block size. I am trying to get our SA to run vxtrace on the filesystem, which might show what's really going on for an I/O. If you have access to run vxtrace, please do and let me know what you find out.

Glenn MacCrimmon