Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Direct I/O, better performance?

RE: Direct I/O, better performance?

From: Roger Xu <roger_xu_at_dp7uptx.com>
Date: Mon, 25 Oct 2004 17:35:44 -0500
Message-ID: <A6801E8A03316A4DA597866F77A013F702D4F241@irv2kexch01>


First of all, we do not have performance issue. Secondarily, I did not gain anything after I switching to Direct I/O.

All I have is longer statistics gathering, longer datafile

backup and some longer jobs.

Anybody in the list see benefits after switching to Direct I/O?

Thanks.

-----Original Message-----
From: oracle-dba_at_comcast.net [ mailto:oracle-dba_at_comcast.net] Sent: Tuesday, October 19, 2004 3:43 PM
To: Roger Xu; Bobak, Mark; Oracle-L_at_Freelists. Org (E-mail) Cc: Roger Xu
Subject: RE: Direct I/O, better performance?

Roger,

Why turn off "forcedirectio" totally ? I think Mark was not trying to discourage you from using it . Solaris's Concurrent Direct IO is a wonderful feature that should improve the database performance overall , excepting those cases which Mark mentioned,which can be taken care of. You will need to readjust Oracle's SGA size to make use of the released Solaris Page cache.

Direct I/O not only avoids the double buffering ,but also provides the following benefits :

-shorter code path for I/O as the filesystem cache is bypassed.
- less pressure on Solaris's VM system
- elimination of Solaris's single writer lock on files
- batching large writes

An excellent choice especially for redologfiles.

-Thiru

> Thank you all for replying my email. You guys are awesome.
> Lots of good ideas and deep thoughts.
>
> My expectation was to improve overall performance, not just
>
> statistics gathering.
>
> I think I am going to turn off "direct I/O", because I also
>
> found out the datafiles backup ran slower than before.
>
> Thanks again.
>
>
>
> -----Original Message-----
> From: Bobak, Mark [ mailto:Mark.Bobak_at_il.proquest.com]
> Sent: Tuesday, October 19, 2004 2:46 PM
> To: Roger Xu; Oracle-L_at_Freelists. Org (E-mail)
> Subject: RE: Direct I/O, better performance?
>
>
> Roger,
>
> Why would you expect the statistics gathering process to improve
> performance? Did you identify some inefficiency in the process
> which you determined would be addressed by switching to direct I/O?
>
> In general, I think it's safe to say that direct I/O is better
> than buffered. However, I would not expect an instant
> performance increase with something like stats gathering.
> The idea with direct I/O is that the O/S does not attempt
> to buffer the datafiles in memory. This frees that memory
> and allows it to be allocated to the Oracle kernel (SGA),
> where it may (perhaps) be used to allocate a KEEP and/or
> RECYCLE buffer pool, allowing Oracle to manage the buffering
> of datafiles directly, rather than allowing the O/S
> to attempt to do so. The idea is that the Oracle
> kernel knows more about how data in the datafiles
> is used than the O/S, and therefore should be better
> at managing memory dedicated to buffering datafile
> contents.
>
> As to the slowness with statistics collection, well, I think
> you have to start at the beginning. Treat it like any other
> poorly performing business process. Set a SQL trace at level
> 8, and rn the stats. Analyze where time is being spent.
>
>
> Finally, one more point regarding direct I/O. While it's
> safe to say that direct I/O is better than buffered I/O,
> there is at least one case where that's not true.
> (Thanks to Jonathan for this example.)
> It's possible, if you have a process that does a full table
> scan on a moderately large table. (Say, on the order of
> 1GB or 2 GB.) Consider that the server you're on has
> lots and lots of memory, resulting in the aforementioned
> table being cached in the filesystem buffer cache. The
> result is that all those 'db file scattered read' events
> are really, really fast, cause they are all (almost all?)
> being satisfied from buffer cache. Remember, buffers are
> being aged out of the Oracle buffer cache quickly, cause
> it's a sufficiently large table, and the operation is a full
> table scan. So, now you move to direct I/O. Well, the Oracle
> buffer cache is behaving the same way, aggressively aging
> the full scanned blocks out of the cache. But now, there
> is no filesystem buffer cache. So, all those 'db file
> scattered read' events are resulting in a real physical I/O.
> So, the performance of the job suffers. Conclusion?
> Direct I/O sucks! Of course, a better solution would be to
> grow the buffer cache by the amount of memory saved by not
> having the filesystem buffer cache, and perhaps use that
> memory to allocate or grow the KEEP buffer pool, and put that
> table there. Now, Oracle can satisfy the full scan without
> attempting a physical read.
>
> Come to think of it, the stats process is probably doing
> FTS behind the scenes. The situation outlined above could
> be what's happening to you. (Could be....not enough info
> to draw any conclusions.)
>
> Hope that helps,
>
> -Mark
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [ mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Roger Xu
> Sent: Tuesday, October 19, 2004 3:16 PM
> To: Oracle-L_at_Freelists. Org (E-mail)
> Subject: Direct I/O, better performance?
>
>
> Hi,
>
> We are running Solaris 9 with UFS on Oracle 9.2.0.4.0.
> We switched to direct I/O and did not see a better performance
> as far as updating statistics concerned. Why?
>
> It used to take us 22 hours to update statistics for all tables,
> but now 31 hours.
>
> Thanks,
> Roger Xu
> Database Administrator
> Dr Pepper Bottling Company of Texas
> (972)721-8337
>

This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the material.



This email has been scanned for all viruses by the MessageLabs Email Security System. Any questions please call 972-721-8257 or email your request to tech_support_at_dp7uptx.com.
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Oct 25 2004 - 17:31:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US