Oracle / Buffer cache

[quote]8.7 Tuning the Operating System Buffer Cache
To take full advantage of raw devices, adjust the size of Oracle Database buffer cache. If memory is limited, then adjust the operating system buffer cache.
The operating system buffer cache holds blocks of data in memory while they are being transferred from memory to disk, or from disk to memory.

Oracle Database buffer cache is the area in memory that stores Oracle Database buffers. Because Oracle Database can use raw devices, it does not use the operating system buffer cache.

If you use raw devices, then increase the size of Oracle Database buffer cache. If the amount of memory on the system is limited, then make a corresponding decrease in the operating system buffer cache size.

Use the sar command to determine which buffer caches you must increase or decrease.[/quote]


Hi all,

Does oracle benifits from hp-ux buffer cache?
We have oracle 8.1.7 running on HP-UX 11.11 (Nclass 4 cpu) 3G of memory. As of now the memory utilization is 99% and 1.3 G of that is into buffer cache. Our DBA told me that they having a slow down on query. Do I need to modify my kernel parameter to increase the performance of oracle?

Thanks in advance

Joseph


Hi Joseph

Oracle has its own buffer cahce, although all DB read/writes pass through the OS buffer, unless you set specific filesystem mount options to bypass the OS buffer (these are only available with OnlineJFS).
If you have 3GB of memory & you're using 1.3GB for buffer, then your buffer cache is really too high.
It be should be around 400MB. Resize your OS buffer cache so it consumes a maximum of 400MB-500MB of memory (ie set dbc_max_pct=15 & dbc_min_pct=5).

Its very possible that with only 3GB of memory & buffer using 1.3GB then memory is causing Oracle performance issues.

However your DBA also needs to look at the Oracle DB Buffer size, this is completely independant from the OS buffer. Indeed a number of the SGA parameters set in the init.ora file can have a significant impact on Oracle performance if they are not sized correctly.

Cheers
Con


Hi Joseph

The 2 options are:
mincache=direct,convosync=direct,nodatainlog

You should only use these options for filesystems that contain the Oracle datafiles.

In some cases these options can lead to small performance gains but it there's no gaurantee.
If you're having Oracle performance issues, I'd first look at reducing your OS buffer cache size & then get the DBA's to examine their SGA parameters to ensure they are sized correctly.

Cheers
Con

Hi Joseph

Sorry I don't have a document on Oracle Tuning on HP-UX relating to buffer cache (dbc_max_pct).
I can point you to 2 HP-UX performance tuning guides that discuss buffer cache sizing.

http://h21007.www2.hp.com/dspp/files/unprotected/devresource/Docs/TechPapers/UXP
erfCookBook.pdf

http://docs.hp.com/hpux/onlinedocs/os/11.0/tuningwp.html#top

Both are excellent docs & are written by one of HP's top performance experts.

If you search the forums, you'll also find many threads discussing buffer cache sizing. I missed that you were running 11i which handles buffer cache slightly differently than 11.00 and can benefit from larger cache sizes than 11.00. However with only 3GB of memory, I'd say your buffer cache is definitely much too large at 1.3GB.

Cheers
Con

Hi there.
Here a URL for Oracle doc :

http://otn.oracle.com/documentation/index.html

you should find everything you need there.
Rgds
Alexander M. Ermes