[dknapp]

This is interesting. We have the same config for STATISTICS_LEVEL, but we are not having the high system utilization. Just the same, I have changed the parameters per Oracle's (and this post's) recommendations.

Another source for high system utilization is the *paging* rate on RHEL4. I am planning a move to utilize HUGE_PAGES this evening. It's seems to be the best way to pin the SGA in RH.

The symptom is heavy pagging in response to db requests. Here's a vmstat that shows what I mean:

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
25 2 3068524 14288 616 4221600 406 0 2333 494 2024 3107 11 72 9 8
39 1 3069136 14136 332 4221196 2 24 114 55 1067 646 1 99 0 0
1 17 3156908 15228 592 4155300 198 4926 1325 5036 2014 902 2 84 1 13
0 25 3161944 18308 800 4152288 69 236 169 286 1576 497 1 1 2 96
0 21 3185408 17772 1780 4144732 1091 2034 2320 2489 1698 1524 3 5 15 78
0 18 3186784 20228 2196 4150308 278 2034 1367 2405 1814 917 6 3 6 85
0 16 3186908 19876 2468 4162684 460 790 2270 1151 3581 3309 23 3 8 65
0 19 3186960 20924 2464 4170324 204 2171 2203 2354 1842 898 9 2 9 81
4 7 3174388 25804 2064 4178432 2247 267 11999 784 2138 1876 25 8 17 50
0 3 3160332 18980 2048 4176600 3894 0 7509 547 4632 5829 38 6 23 32
5 4 3140384 18356 1340 4172824 5746 0 9578 2553 3804 4232 28 7 35 31
0 4 3115104 20428 1636 4191620 6768 0 12222 429 3390 4052 36 8 29 28

high # of blocked processes, a lot of blocks swapped in, and a lot of waitung. Ugh.

The /proc/sys/vm/swappiness parameter provided brief relief of the pagging issues, but over time, the SGA was still paged out. It's very frustrating to see our system start paging horribly, even though we have ample memory. It seems that RHEL4 is very aggressive about pushing infrequently used pages out in order to make room for file system buffers (which are of little use on a dedicated DB server anyway).

On our test systems I have moved to HUGE pages and set the filesystemio_options parameter to SetALL, that tells Oracle to bypass the file system buffers and write directly to disk. I believe that this will be more efficient than double buffering (SGA+Filesystem) and will reduce memory pressure at the OS level as well. So far I have seen no ill effects from the changes and I'm planning the changes in PROD this evening.

Here is an excellent link for RHEL optimization.

Tuning and Optimizing RHEL for Oracle 9i and 10g Databases (Red Hat Enterprise Linux, 4, 3, 2.1 - redhat, x86-64)