From: A. Mahendra Rajah (Mahendra.Rajah@URegina.CA)
Date: Fri May 10 2002 - 11:42:16 EDT
Hello:
I asked the group if Oracle 8.1.6 uses AdvFS's direct I/O
feature and if so, how to disable it. I did not get any
authoritative replies from an Oracle guru, but I did hear
from two Compaq engineers who stated that direct I/O is
never implicit and must be requested in every open() call.
My sincere thanks go to the following individuals for their
replies:
Greg Freemyer
Andy Cohen
Jim Beltz
Bruce Young
Bob Harris UBPG
Raul Sossa S
Colin Bull
Pat O'Brien
I am enclosing all the replies I got just in case it could help
out the group.
-- mahendra
================ My question ====================================
System: 2 CPU ES40 with Tru64 5.1A+pk1
Ever since we moved to Tru64 5.1A from 5.1, users have been
complaining that Oracle is quite slow on our system. My Oracle
DBA says that direct I/O that is implicitly enabled in AdvFS on
Tru64 5.1A affects Oracle's performance considerably.
Is this true?
My reading up of the AdvFS Administration manual tells me that
one must request direct I/O explicitly when the file is opened.
So, it begs the question: is direct I/O implicit for all AdvFS
files or is Oracle asking for it in their code?
If so, how can one get around this problem? Moving to 8.1.7 is
not possible at the moment as SCT, the supplier of BANNER, has
not yet approved its usage.
=================================================================
From: Greg Freemyer
I don't think AdvFS ever "assumes" direct_io.
And I don't think an Oracle that old would use the
direct_io feature of Tru64 V5.
(It is only with 9i that Oracle started adding Tru64 V5
optimizations.)
If not, Oracle recommends using "raw" filesystems where
speed is a concern.
Even with the latest version of Oracle which has been
tuned specifically for Tru64 V5, there is 10-15% hit for
using AdvFS instead of RAW.
Of course the bad part about using a raw filesystem is
that you can only use Oracle utilities to administer it.
i.e. none of the standard UNIX tools understand a raw
filesystem. Oracle basically creates its own proprietary
filesystem on the raw partition.
=================================================================
From: Jim Beltz
I work in the filesystems group at Compaq, and am directly
involved in the directIO code.
Oracle uses directIO as a performance enhancer. It may be
that your Oracle DBA meant that Oracle uses directIO by
default, but it is certainly NOT the case that any file
opened in v51a uses directIO by default. It must be
requested on the open() call.
Oracle has been modified to open the database with
directIO when it is configured to use directIO. However,
I'm not sure what the configuration variables are called,
nor do I know what versions of Oracle support the directIO
calls. You may be able to get this info from Oracle.
=================================================================
From: Bruce Young
I have seen this type of behaviour also on a 5.1 -> 5.1A
upgrade. For some reason the debug flag gets enabled for
the cfs manager and as a result disk performance suffers.
You are able to set this debug flag off. To do this you
need to add this to your /etc/sysconfigtab file.
cfs: cfsdbg_flags = 0
Once the change has been made you will need to reboot.
After that you should see a marked improvement in
performance.
=================================================================
From: Bob Harris UBPG
Direct I/O must be requested. If Oracle is using Direct
I/O then Oracle would have had to open the file explicitly
asking for Direct I/O using the O_DIRECTIO open flag.
Tru64 UNIX and AdvFS will not do any Direct I/O operations
automatically.
If there is any "implicitly" operations going on, then it
is on the part of Oracle.
I have no knowlege of Oracle operations, but based on
second hand information, it is my understanding that
versions of Oracle that do know about Direct I/O also have
a option to disable it. If such an Oracle option really
exists, I do not know what it is called, nor do I know if
the option is in all versions of Oracle that know how to
use Direct I/O. Hopefully someone else that actaully plays
with Oracle will have that information.
=================================================================
From: "Raul Sossa S."
Tell your DBA's that set DISK_ASYNCH_IO=FALSE and
DB_WRITERS= # of procesors in every Oracle8i Instance
Parameter File (and test performance for a week).
Also, remember that Cluster File Systems that are being
use by an specific Oracle8i Instance, must be served by
the node that has this instance up. (this will increase
the IO performance, check with "cfsmgr -e |more", which
server is serving the corresponding cluster file systems
for what Oracle8i Instances and make them match).
=================================================================
From: Colin Bull
We have experimented with Informix RAW disks on our ES40s
using SAN and have found it to be 20-25% faster than using
cooked files. There are also performance benefits to
laying out data to remove contention from the disks if you
have enough spindles to do this. In my opinion it is a bad
move buying large capacity drives for a database, better
to have more small ones.
=================================================================
From: "O'Brien, Pat"
I am not oracle inteligent, but what I have learned from
our staff of dba's is that yes d i/o is on by default on
straight 8.1.6 and with some oracle patch can be turned
off. see below
applied an Oracle patch bringing the database to version
8.1.7.2.0 (this was necessary to get to the Oracle version
that accepts a parameter to disable Direct IO), I ran
several tests with Direct IO turned on vs Direct IO turned
off. Here are the results:
Query 1 - Created a table with 45 records but required
lots of sorting:
Direct IO turned on - 51 minutes
Direct IO turned off - 62 minutes
Query 2 - Created a table with 500,000 records no sorting
Direct IO turned on - 24 seconds
Direct IO turned off - 26 seconds
Query 3 - Created a table with 1,500,000 records no
sorting
Direct IO turned on - 1 minute 5 seconds
Direct IO turned off - 1 minute 20 seconds
Here are the results of Raw vs. Direct I/O. Same queries
as before:
Query 1 (This is the query heavy on sorting)
I used the raw device to create a temporary tablespace
where sorts would occur. With Direct I/O disabled and
using the raw temporary tablespace:
Run Time - 49 Minutes
For Queries 2 and 3, I dropped the temporary tablespace
and created a data tablespace using the raw device. With
Direct I/O disabled and using the raw data tablespace:
Query 2 (creating 500,000 record table) - 23 seconds
Query 3 (creating 1,500,000 record table) - 58 seconds
It appears that Direct I/O does approximate raw pretty
well, but raw still nudges it out.
=================================================================
This archive was generated by hypermail 2.1.7 : Sat Apr 12 2008 - 10:48:40 EDT