RE: Direct I/O, better performance?

From: "Roger Xu" <roger_xu@xxxxxxxxxxx>
To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 19 Oct 2004 15:18:44 -0500
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

For technical support please email tech_support@xxxxxxxxxxx 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@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
RE: Direct I/O, better performance?

Other related posts:
