Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle settings & buffer cache.

SOLVED
Go to solution

Oracle settings & buffer cache.

Hi All

I know theres been a few discussions on buffer cache settings related to oracle but I need a bit of clarification as Im still trying to get my head around it!.

Our system is running Oracle 8i on HP-UX 11.00 with 6GB Physical RAM, pseudo-swap enabled & 6GB of swap. I have set a fixed buffer cahe of 200 MB.

I understand that the internal structure of Oracle means any read/writes to its datafiles are read/written to its own buffers(SGA). Three questions:
1. Do these read/writes also go through the OS buffer cache??
2. Do the mount options mincache=direct, convosync=direct mean that the read/writes bypass the OS buffer cahe?? If so does this mean it is only possible with On-line JFS to prevent Oracle using the OS buffer cache ie if these options are not set in /etc/fstab then buffer cahe is always used??
3. Finally am I correct to assume that if I create a filesystem with largefiles enabled - I also have mount this fs with the largefiles option in /etc/fstab as follows:
" vxfs delaylog,largefiles 0 2"

I'd be grateful for any thoughts/clarifications as Im not sure Im on he right track here.

Thanks.

Cheers
Con
7 REPLIES
Roger Baptiste
Honored Contributor
Solution

Re: Oracle settings & buffer cache.


<>

Looks fine. You also have the option of setting dynamic buffer cache, ranging from a max of 10% to min 5%.
Check how the cache hit rates are currently by looking at sar -b output. 95% for reads should be a good value and around 85% for writes ; Based on this, you can increase the cache value. If you set it to dynamic, the system uses it as on need basis.


<<1. Do these read/writes also go through the OS buffer cache?? >>

Yes. An FS operation passes
through FS buffer cache. So, you are using double buffering.

<<2. Do the mount options mincache=direct, convosync=direct mean that the read/writes bypass the OS buffer cahe??>>

Yes.

<< If so does this mean it is only possible with On-line JFS to prevent Oracle using the OS buffer cache ie if these options are not set in /etc/fstab then buffer cahe is always used?? >>

Yes. You would need Advanced OnlineJFS to use these options and the setting needs to be there in the fstab file for mounting options.
Also, note that if you are planning to use it, use it on the oracle data disks only
and not on archive or rollback logs.

<<3. Finally am I correct to assume that if I create a filesystem with largefiles enabled - I also have mount this fs with the largefiles option in /etc/fstab as follows:
" vxfs delaylog,largefiles 0 2" >>

Yes.


HTH
raj
Take it easy.
Klaus Claassen
Occasional Contributor

Re: Oracle settings & buffer cache.

Hi,

have you already seen/read the "HP-UX Performance Cookbook"
By Stephen Ciullo and Doug Grumann,
revision 22OCT01 ?

It's available as pdf under
http://h21007.www2.hp.com/dspp/files/unprotected/devresource/Docs/TechPapers/UXPerfCookBook.pdf

It might answer some (all?) of your questions.
(I have similar Oracle problems as you have,
and I discovered the book today and find it very interesting)


Best regards,
Klaus Claassen

Re: Oracle settings & buffer cache.

Rajman
Thanks very much for your detailed response - that was fantastic. One minor point on Q3. I read the man page properly (!) & it appears that by default the fs is mounted with largefile capability if the filesystem was created with largefiles enabled. Am I reading this correctly?

Klaus
Great document - hadn't seen it before. Answers loads of questions that I haven't even asked yet! From a brief glance it also answers question about pseudo-swap that was the subject of a long discussion recently.

Thanks again.

Cheers
Con
Bill Hassell
Honored Contributor

Re: Oracle settings & buffer cache.

As far as defaults for mounting with largefile capability, I have not seen that to be the case. You need the filesystem modified for largefile support (fsadm can do this), and then mount with the largefile option, either in the mount command or in fstab.

You can check on the state of the filesystem with fsadm. For HFS, you must use the raw logical volume while VXFS needs just the mountpoint:

fsadm -F hfs /dev/vg03/rlvol1

fsadm -F vxfs /mount_point


Bill Hassell, sysadmin
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle settings & buffer cache.

Hi Con:

You are correct. If largefiles was enabled when the filesystem was created or if set using fsadm then the default state changes to -o largefiles and thus no -o largefiles mount option is needed. It doesn't hurt but you don't need it.

Your other option if you don't want to purchase OnlineJFS is to use raw volumes. You can even do this without changing anything within Oracle. For example, let's suppose that one of your Oracle datafiles is /u01/oradata/data01.dbf and you wish to actually move that to the raw lvol /dev/vg04/rlvol1.

1) Shutdown the database
2) dd if=/u01/oradata/data01.dbf of=/dev/vg04/rlvol01
3) rm (or rename) /u01/oradata.data01.dbf
4) ln -s /dev/vg04/rlvol01 /u01/oradata/data01.dbf
5) Startup the database

Note that no init.ora changes are even required. Having said all this, if I were you I would purchase OnlineJFS. It's one of those tools that no HP-UX box should be without. It makes system management much easier. If you do choose to use raw or OnlineJFS unbuffered i/o, you should use these options on datafiles and indices but leave the archive log and redologs buffered. I do suggest that you measure the performance because on 11.11 I typically see better performance with buffered i/o. I also find that defeating dynamic buffer cache by setting bufpages to a non-zero value gives better performance. Typically bufpages=80000 (320 MB is about as much as you need and that is very generous).

Regards, Clay
If it ain't broke, I can fix that.

Re: Oracle settings & buffer cache.

Thanks for all the replies.

Hi Clay
Thanks for the great info. From your response am I correct to assume that there is no inherent advantage in using unbuffered io for FS's with oracle datafiles ( and indeed it can cause performance degradation in some cases)?? I assumed that bypassing the OS buffer cache with fs's containing only oracle datafiles would either have no perf impact or could provide a perf gain but not cause a fall in performance.
One other point, on some systems we have our archive logs in separate fs's but indices are in the same fs as datafiles. Does this mean that these Fs's should never use unbuffered io?

Initially I was thinking along the lines that we should mount all FS's that contain our Oracle datafiles with the options to bypass OS buffer cache. (Assuming we have Online JFS installed).
However as we currently have no performance issues, probably wise to leave it alone.

Thanks for any help.

Cheers
Con
Roger Baptiste
Honored Contributor

Re: Oracle settings & buffer cache.

Hi,

<>

Yes. If the filesystem has been created or modified for largefile capability, then it will mount it by default with largefiles option . Infact, the fstab file of many of my systems don't have the l-largefile options, even then the FS mounts with largefiles support. You can always check up largefiles support by running fsadm command on the filesystem.


<>


I know, there is varied opinion on the gains of disabling/enabling the FS buffering . In all the database systems i support, i have the FS buffering enabled. I tend to concentrate more on the backend (i.e disk and controller, striping of disks, lv's etc), to
tune the Filesystems and also get the DBA involved to see he doesn't have data and index on the same FS.

If you do not have any performance issue, my suggestion is to let it remain as it is.
If a FS performance is sluggish, check whether
the DBA can move out any dbfile or index out of it to a lesser used FS.

HTH
raj
Take it easy.