process stop reason CACHE

[Charles McCary]

Hi - I have some oracle processes that are spending a lot of time in stop reason CACHE according to glance.

Glance's description of this stop reason is:


CACHE Waiting at the buffer cache
level trying to lock down a
buffer cache structure, or
waiting for an IO operation
to or from a buffer cache to
complete. File system access
will block on IO more often
than CACHE on HP-UX 11.x.
Processes using the file
system to access block
devices will block on CACHE on HP-UX 10.20.



My questions are:

1) does this have anything to do with the size of the buffer cache (I've got dbc_max_pct=3 of 16gb). We have plenty of free memory on the machine if this needs to be up'd.

2) If it's not the buffer cache, what can I check? I've increased the max queue depth for the luns the db is using (it's an eva3000 on the backend by the way).

thanks,

c

[Peter Godron]

Charles,
I assume you have seen:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1030127

Wouldn't it be nice if people completed their threads to help future problem solving.

[Charles McCary]

yep...saw that one.

[spex]

Hi,

The Oracle processes are obviously blocking on OS buffer cache. This could be due to excessive reads from or writes to the cache. You should be able to determine which by examining the actual SQL driving the processes, or by 'sar -b 5 5' (compare %rcache to %wcache).

A larger OS buffer cache should improve performance. Subjectively speaking, 492MB (3% of 16GB) of memory seems small to me for a database running on this type of hardware. Note that this will require a reboot.

However, you are "double buffering" your database, meaning that IO passes through both Oracle's buffer cache and the OS buffer cache. A better approach may to use raw (instead of cooked) volumes, thereby bypassing the OS buffer cache altogether.

PCS

[Charles McCary]

spex,

thanks for the info. I'll look at increasing the buffer cache, just wish I knew that to be the culprit. Got to thinking about it and I have another machine that's running oracle as well and it's processes are spending time in the "cache" stop reason too. That box has dbc_max_pct=7 of 16gb...so I'm not sure that upping the dbc_max_pct is going to get the procs out of this state.

I've looked into using the mincache=direct,convosync=direct options (i.e. skipping the buffer at the fs level), but I remember that you have to be in sync with the oracle config as well or you can actually decrease performance. Not sure I remember all the details about that now though.

[spex]

Charles,

If you haven't seen it yet, the HP-UX Performance Cookbook makes for a great read. It covers tuning the OS buffer cache and LVs in detail, among other topics. For the sake of convenience, I've attached it. Good luck!

PCS