Oracle's cache vs UX buffer cache (Bill Hassell)

[Tim Killinger]

Hello,

Understanding this issue is very important to us:

In a recent message I posted

(http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x5d93afe90f1cd71190050090279cd0f9,00.html)

Bill Hassell made the following statements:

"If your DBA is really running 64bit Oracle, then SGA can be several Gb in size and take advantage of in-memory sort areas, fast row insertions and bypass the HP-UX buffer cache with Oracle's built-in cache. Note that in order to bypass HP-UX's buffer cache, there are two steps:

- Make sure that all Oracle data and index lvols are separate mountpoints, and that rollback, redo and archive logs as well as Oracle executables and config files are not mixed into these data area mountpoints"

Bill or anyone else who understands this issue - My questions are:

1 - Since we are using a VA7100 SAN, does the multiple mount point rule still apply? We were planning on having only a single mount point for Oracle, since we have no control over the placement of Oracle files on devices or lvols on devices.

2 - Why do I want to bypass UX's buffer cache in the first place? Is it less effective than oracle's?

Thank you in advance!!!!

[Christopher Caldwell · Accepted solution]

1) We use different mount points so we can use different file system mount options (the options that include bypassing the buffer cache).

2) HP's buffer cache is a generic buffer cache; Oracle's buffer cache is optimized for things you might want to do with Oracle. You've got to figure that the guys at Oracle have spent a lot of time optimizing Oracle for memory management (as well as making the database unbreakable ;-)). The guys at HP have spent a lot of time optimizing the buffer cache for whatever runs on HP (lots of different things).

If you use both caches, you're probably double buffering, which encroaches on the memory available to the Oracle SGA and your going

disk -> hp buffer cache -> oracle SGA -> hp buffer cache -> disk

instead of
disk -> oracle SGA -> disk

[U.SivaKumar_2]

Hi,

In my experience I have seen Reducing HP-UX buffer cache size negatively affects the I/O response of the disk activities and thereby slows Oracle.

regards,

U.SivaKumar

[Jon Finley]

Yes, but if you use "RAW" disk, then Oracle will perform faster.

The other thought is that by using the UX buffer cache you're increasing the chance that Oracle has committed, but the transaction could be lost in the UX cache.

Jon

[Chris Wong]

If you have online JFS, you can use the "direct" option:

mount -F vxfs -o delaylog,mincache=direct,
convosync=direct,nodatainlog

Uses the ???direct??? feature
SLOW if use other methods for accessing Oracle data other than Oracle itself. (Example: tar).
Direct Writes simulates raw, in that writes bypass the file system buffer cache

BUT, you don't have the admin danger of using raw.

You can't control where the data is on the array, but you want to be able to control how the filesystem is mounted.

- Chris

[Steven E. Protter]

As to basic Oracle performance, you keep data on different mount points.

Its better if they are on a different virtual disk, but not essential.

Our typical fs layout is

/oracle oracle software

/oracle1 logs, redo control files

/oracle2 data

/oracle3 archive logs redo control files

Each is a mountpoint and the /oracle2 guy is on a disk array, RAID 10 Fastest/Best

SEP

[John Poff]

Hi,

Are you running HP-UX 11.0 or 11.11? On 11.0 you would want to set your data and index
filesystems to do 'mincache=direct' so that you bypass the buffer cache, and set your
other filesystems to use the buffer cache. On 11.11, bypass the buffer cache on
everything. I've tested several filesystem mount options on 11.11 and using the
buffer cache is much faster for operations that write to disk.

I would suggest making separate mountpoints as others have suggested here, so that you
can better manage the performance with different mount options.


Here are some threads where the issue is discussed:


http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x402436e69499d611abdb0090277a778c,00.html

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x335eef70e827d711abdc0090277a778c,00.html



JP

[Tim Killinger]

Thanks for the feedback - it is somewhat clearer to me, but:

If on our VA7100 we have 1 and only one mount point for Oracle tablespaces - i.e. data,redo,indexes - we will have mount option control for those specific oracle files.

Given the VA7100, is there STILL a practical need for separate mount points for redos, data, and indexes?? (all non-tablespace file types already have different mount points)

Perhaps this is a question for the SAN forum?

Thanks again!!

[John Poff]

Hi,

Even though you have everything on the VA7100, I would suggest making a separate filesystem for your redo logs, so that you can mount that filesystem with options to use the buffer cache. The mount options will make a big difference depending on the type of writes being done, and the redo logs are written differently [a bunch of data all at once] than the data and indexes [a little bit here and there].

On the other hand, it probably won't kill anything to leave it like it is. The answer as always is, it depends.

JP