Oracle indexes and redo log vxfs options

[Bryan Hunter_1]

I have been reviewing Oracle and Hp forums regarding caching and synchronizing of I/O for various Oracle file types. Can some one shed some light on some apparent conflicts for index files and redo logs?

On index files, some suggest that the direct option only be applied to regular data areas while others would seem to group all data areas together. Does Oracle itself protect indexes as well as it does the data thus making it safe to go direct?

On redo files, what are the risks of going direct?

Also, I have seen many references as to how Oracle says these should be set, but does anyone have actual references to Oracle documents?

I would appreciate any discussion as to why these different views exist.

Thanks,
Bryan hunter

[A. Clay Stephenson]

Hi Bryan,

If you peruse Oracle's MetaLink, you find somewhat conflicting statements on the use of the vxfs mount options. I can only summerize what I have found and actually measured as being the optimal for Oracle 7 & 8.

For datafiles and indices:
delaylog,nodatainlog,convosync=direct,mincache=direct
(This bypasses the UNIX buffers and essentially becomes raw IO) You should then greatly reduce unix buffers and increase SGA buffering

For redo & archive logs:
delaylog,nodatainlog

Hope this helps, Clay

[Volker Borowski]

Hi,

Oracle uses a no-cache option when it opens the filehandles to any of its files. It has to, to guarantee data integrety with it's own logging mechanism.
So if the files are in the filesystem, you have the overhead of the filesystem-cache-management without a real profit of it. Reads are cached through the oracle_data_buffer, but eat space in the filesystembuffer in addition.
Writes are not delayed at all. This is why there are general rumors about speeding up oracle between 10%-30% when going to raw devices.

Now for easy of management, some people prefer to have the files in the filesystem and do not play around with raw devices. vxfs has some good mount options to optimize IO for databases (as Clay put in). So on vxfs I think you will not loose 10% compared to raw devices at all.

Concerning the question which types of segments are protected diffrently by oracle, there is to say "They are all protected as you configure".
There are options to reduce logging. Some people configure some indexes (or even tables) with the "NOLOGGING" option. This means changes to this indexes are not protected the same way as usual.
You should be very aware of this, if you have such objects in your database, because these objects may need special recovery after a crash.
In general, you should only use this for temporary objects, that will be reset at any restart of your database or your application.
If you have doubts, set to LOGGING and go for additional archivelog-space.

The online-redolog-files and controlfiles are the files that need most synchronus writing to disk. So direct access is best.

Just my 0.02 ?
Volker

[Bryan Hunter_1]

Hi Clay:

In your response on March 15, 2001 to the subject "forums > hp-ux > databases > migration of large oracle database from 7.3 to 8i" you said the following.

"Redo logs and archive logs should be mounted delaylog,datainlog."

Would you please explain why the change in the datainlog option.

[A. Clay Stephenson]

Hi Bryan,

Since that posting, I reviewed and tested the performance of the mount options for redo and archive logs. In theory, datainlog should have slightly better performance at the expense of slightly less integrity. I could not reliably measure any significant performance differences
so I have since changed my mount options for archive and redo logs to nodatainlog.

The best answer is to do these measurements yourself, the good news is that changing the mount options will not harm Oracle (other than
performance/integrity tradeoffs) in any way - indeed Oracle will not know it.

The only i/o option that potentially will harm you is asynch i/o. There you have to ask yourself are the speed gains worth the risk?

In my case, the vxfs mount options provide sufficient performance with low risk and I have chosen not to implement asynch i/o.

Regards, Clay