Oracle-L: RE: _tru64_directio_disabled param Value on Digital Tru64 Unix

From: Browett, Darren <dbrowett_at_city.coquitlam.bc.ca>
Date: Thu, 30 May 2002 09:38:31 -0800
Message-ID: <F001.0046FDC7.20020530093831@fatcity.com>

I dont't know the full details, but Compaq/HP has just released patch kit 2 for
5.1a, and there may be a patch with regards to Direct I/O and inconsitent data
in there.

-----Original Message-----
Sent: May 29, 2002 12:48 PM
To: Multiple recipients of list ORACLE-L

I joined in on this thread a little bit late. I just did a little experimentation with this parameter, and all that I can say is WOW! This is the equivalent of the mythical _make_sql_run_faster!

My quick tests on 8.1.7.2 on both Tru64 5.1 pk3 and 5.1a pk1:

Query1 : Avg. of 1.13 sec improved to .11 sec.
Query2 : Avg. of 11.78 sec improved to 2.12 sec.
Query3 : Avg. of .75 sec improved to .08 sec.

This included multiple runs of each query, with a database bounce in between of course.

So, what is the catch?

>>> Srini.Chavali_at_Cummins.com 05/29/02 02:26PM >>>

Quote from a person who has had researched this thoroughly at our site -

"Oracle 8.1.6 and later releases check to see if they are running on Tru64 5.0a or later operating system revision. If so, the RDBMS automatically uses the directio mode to open the database files.

Directio bypasses the operating system (ADVFS file system) caching and is more efficient; however, ADVFS does not cache any data or pre-fetch read data. For single block random reads directio is a performance improvement--there is less O/S overhead and Oracle does a good job of managing the buffer cache. However, Oracle does not hold multiple block reads in its cache, so if your workload involves a large number of multi-block reads directio is a performance detriment. The blocks are not cached, so re-reads require physical I/O for each read, and Oracle does not pre-fetch data as ADVFS does, so the application incurs more I/O wait.

Also, any subsequent access after a file is opened in directio mode inherits the directio mode. This may impact other applications reading the files outside of the database activity--for example backup.

In our experience using the Oracle Applications (ERP) suite, overall performance was better with directio disabled.

By default directio is enabled if running 8.1.6 or later and Tru64 5.0a or later. The flag to disable was introduced in 8.1.7.2, I believe. We were told not to run 8.1.7.2 on Tru64 (buggy), so we have implemented 8.1.7.3.

The default operating mode is:

_tru64_directio_disabled = FALSE

This enables directio. If you set it TRUE, then the RDBMS I/O will function as it did before--using normal I/O. There is not a lot of risk in changing this option, and directio may prove to be advantageous for a heavy OLTP environment. I would recommend testing outside of Production if at all possible.

Bill"

HTH Srini Chavali
Oracle DBA
Cummins Inc

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Srini.Chavali_at_Cummins.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jay Hostetter
INET: jhostetter_at_decommunications.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------