Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Testing Direct I/O

Re: Testing Direct I/O

From: Nuno Souto <>
Date: Tue, 14 Feb 2006 19:46:17 +1100
Message-ID: <> apparently said,on my timestamp of 14/02/2006 11:33 AM:
> To further clarify, the saved SQL statements I have are from business
> transactions -- they're all select statements, no DML...

and here I am for 30 years laboring under the misunderstanding that SELECTs are DML statements... As opposed to DDL: Data Definition Language! :)

Seriously: isolate a series of statements that are relevant to your environment and application, get a test bed together and establish a baseline with cooked io. Then switch on direct io and test, tune, test, tune, etcetc. Stop when happy or satisfied that it's good or bad for your app. Measure overall number of IO requests per second and overall MB/sec throughput. Simple, really.

One word of warning: when you turn on dio, you are implicitly forfeiting a lot of little optimisations the file system software might actually be doing for you, contingent on what type of file system and maker you were using before. And you are then replacing those by the direct, simple IO requests to/from disk into Oracle buffers. As such expect some slight degradation of raw IO speed, paid for by gaining some CPU back from having to do a lot less buffer shuffling.

I do recall some option back in older versions that would let you use direct mapping of Oracle buffers to OS buffers. Ie, no buffer-to-buffer copy. Dunno if still valid in 9i or 10g. That was usually a better way to go if one wasn't sure of what could be done at dio level to help the db sw or if one wasn't authorised to mangle hardware and OS configs to spread IO better.

If in your HPUX environment you are using the extensions to the Veritas file system, I strongly suggest that you don't bother with dio and concentrate instead on optimising that environment: much less pain, much more gain.

Nuno Souto
in sunny Sydney, Australia
Received on Tue Feb 14 2006 - 02:46:17 CST

Original text of this message