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@xxxxxxxxxxx [ mailto:oracle-dba@xxxxxxxxxxx] Sent: Tuesday, October 19, 2004 3:43 PM To: Roger Xu; Bobak, Mark; Oracle-L@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@xxxxxxxxxxxxxxx] > Sent: Tuesday, October 19, 2004 2:46 PM > To: Roger Xu; Oracle-L@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@xxxxxxxxxxxxx > [ mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Roger Xu > Sent: Tuesday, October 19, 2004 3:16 PM > To: Oracle-L@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@xxxxxxxxxxxx -- //www.freelists.org/webpage/oracle-l