Oracle / Buffer cache

[Joseph Bague]

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

[Con O'Kelly · Accepted solution]

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

[Joseph Bague]

Hi Con,

On OnlineJFS what are those options?
Do you have any link/documentation about this?

Regards

Joseph

[Con O'Kelly]

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

[Joseph Bague]

Hi Con,

Do you have a document/web link regarding oracle performance tuning on hp-ux 11.11?(db_max_pct related) I need this doc as supporting document.

Thanks

Joseph

[Con O'Kelly]

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/UXPerfCookBook.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

[Alexander M. Ermes]

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

[Jean-Luc Oudart]

We fixed our OS buffer cache to ~300Mb
the rest is available for SGA & user processes, you do not want to really use your swap area ?

You should also tune the SGA components

JL