| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Direct I/O, better performance?
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 25 2004 - 17:31:05 CDT
 |
 |