HP buffer cache with Oracle 9.2

[Steve Illencik]

Anyone have experience tuning buffer cache with Oracle 9.2. We migrated from 8.1.7 to 9.2.0.4. At the same time (bad idea I know), we changed from a dynamic cache (dba_max_pct at the default 50% generated a cache of 1GB) to a static cache (set bufpages=12500 with a page size of 4 to generate a 500MB cache). Various Oracle notes recommend cache <128. HP recommends 500MB cache. Some say dynamic, some say static.

any real words of wisdom are greatly appreciated.

Steve

[James A. Donovan]

You shouldn't allow Oracle data to be cached by the Unix buffer cache. You're decreasing the performance of your system. Oracle already has it's own buffer cache in the SGA.

Filesystems containing Oracle datafiles shoud be mounted using the mincache=direct mount option at a minimum.

[A. Clay Stephenson]

Hi Steve,

Somebody need to learn you to do math better on account of 12500 a'int 500MB but rather 50MB. 500MB would be bufpages=125000.

Now what the value should be depends. Are you using raw/io? If so, a buffer cache of 200MB or so, maybe less is good. If Oracle is using cooked files then somewhere between 800-1200MB ought to be about right. The thing that worries me is that 50% max_dbc_pct resulted in a 1GB cache. That implies that you have 2GB of RAM and that is considered a tiny amount of RAM for even modest Oracle use. I favor static buffer caches but dynamic under 11.11 is much better than it was under 10.20 and 11.0.

How much memory do you have? How large / how many SGA's do you have? How many Oracle users are you running? Are your cooked or raw? When those answers are forthcoming then maybe a bit more meaningful data can emerge.

One thing is for sure. Your 50MB cache is much too small. Remember, even with RAW Oracle IO, other UNIX functions still need buffer cache to perform well.

[Sridhar Bhaskarla]

Hi Steve,

I would configure dynamic buffer cache on the systems that are low on memory. That's with the hope that in case there is memory pressure, the buffer cache would shrink. On systems with 4GB or more, my personal preference would be to go for static cache. 500MB doesn't seem to be a bad figure even on 11i. I would set it to 128000 pages = 512000 K = 500 MB if you are too particular having the 500 MB figure.

I tried all online JFS options (mincache and convosync) but didnt' get a thing. Rather in couple of cases, I found the performance decreased. But I believe it depends on your application. If you have online JFS, try these options and see if you can benefit.

I do have database on raw devices and we do get into performance issues on there even. It's a matter of how well the DBAs configure the database and how well the developers write the code to get the best out of it. You can do 100 things to improve the performance from the system side but one bad piece of code is enough to put it to task.

-Sri

[Geoff Wild]

You want your buffer cache to be in the 400-800 MB range...

How the Buffer Cache Grows:
As the kernel reads in files from the file system, it will try to store data in the buffer cache. If memory is available, and the buffer cache has not reached its maximum size, the kernel will grow the buffer cache to make room for the new data. As long as there is memory available, the kernel will keep growing the buffer cache until it reaches its max size (50% by default).
For performance reasons, you want the buffer cache hit ratio on reads to be higher then 90%.
sar -b and watch %rcache
Now what should you set your to? That depends on what you are running on the box. If Oracle, then you want to shrink it as Oracle has its own buffer cache.
You could also mount file systems that you don't want buffer cache by:
-o mincache=direct
That will bypass the buffer cache all together - good option for Oracle on LVM.

Rgds...Geoff