Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Laxman Seethamraju_2
Occasional Visitor

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

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
10 REPLIES
James A. Donovan
Honored Contributor

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

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.
Remember, wherever you go, there you are...
Laxman Seethamraju_2
Occasional Visitor

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

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
Honored Contributor

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

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
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Laxman Seethamraju_2
Occasional Visitor

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

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
Honored Contributor

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

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.
Remember, wherever you go, there you are...
Laxman Seethamraju_2
Occasional Visitor

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

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
Honored Contributor

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

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.

Remember, wherever you go, there you are...
Jeff Schussele
Honored Contributor

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

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
PERSEVERANCE -- Remember, whatever does not kill you only makes you stronger!
Glenn MacCrimmon
Occasional Visitor

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

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
Jean-Luc Oudart
Honored Contributor

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

Discovered Direct IO is available with the HP OnlineJFS product.

vxtunefs can be used to check the "Discovered Direct I/O" parameter.
According to the manual :
Any file I/O requests larger than the discovered_direct_iosz are handled as discovered direct I/O. A discovered direct I/O is unbuffered similar to direct I/O, but it does not require a synchronous commit of the inode when the file is extended or blocks are allocated. For larger I/O requests, the CPU time for copying the data into the page cache and the cost of using memory to buffer the I/O data becomes more expensive than the cost of doing the disk I/O. For these I/O requests, using discovered direct I/O is more efficient than regular I/O. The default value of this parameter is 256K.


Regards
Jean-Luc
fiat lux