RE: Direct I/O, better performance?

From: "Roger Xu" <roger_xu@xxxxxxxxxxx>
To: <oracle-dba@xxxxxxxxxxx>, "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 25 Oct 2004 17:35:44 -0500
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
Follow-Ups:
- Re: Direct I/O, better performance?
  - From: Nuno Souto
RE: Direct I/O, better performance?

Other related posts:
