Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Direct I/O, better performance?
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-l For technical support please email tech_support_at_dp7uptx.com or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. 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-lReceived on Tue Oct 19 2004 - 15:13:49 CDT