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: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 19 Oct 2004 15:45:33 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9B31@AABO-EXCHANGE02.bos.il.pqe>


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=20 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.) =20 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. =20

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,
=20

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?
=20

It used to take us 22 hours to update statistics for all tables, but now 31 hours.
=20

Thanks,
Roger Xu=20
Database Administrator=20
Dr Pepper Bottling Company of Texas=20
(972)721-8337=20

=20

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.=20



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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 19 2004 - 14:41:38 CDT

Original text of this message

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