High System or Kernel Memory Usage

[Michael Steele_2]

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance.

http://www.dbspecialists.com/files/presentations/buffercache.html

[RahulS]

Please the below filecache_max usage pattern of my two Oracle 11g RAC database servers.

Day Date Time "
(BRM DB Node1)" "
(BRM DB node2)"
Bytes % Bytes %
Sat 2/27/2010 23:00 1803579392 11 2958098432 18.1
Sun 2/28/2010 0:00 1938280448 11.9 16322625536 100
Sun 2/28/2010 1:00 1905328128 11.7 16016777216 98.1
Sun 2/28/2010 2:00 1918468096 11.8 15594962944 95.5
Sun 2/28/2010 3:00 1922412544 11.8 15480061952 94.8
Sun 2/28/2010 4:00 1971060736 12.1 15217262592 93.2
Sun 2/28/2010 5:00 1868517376 11.4 15106449408 92.5
Sun 2/28/2010 6:00 1879154688 11.5 15014379520 92
Sun 2/28/2010 7:00 1888792576 11.6 14519771136 88.9
Sun 2/28/2010 8:00 1893126144 11.6 14189719552 86.9
Sun 2/28/2010 9:00 1906495488 11.7 14113128448 86.5
Sun 2/28/2010 10:00 1919987712 11.8 14014951424 85.8
Sun 2/28/2010 11:00 1936580608 11.9 13673652224 83.8
Sun 2/28/2010 12:00 1934462976 11.8 13445591040 82.4
Sun 2/28/2010 13:00 1950392320 11.9 13200674816 80.9
Sun 2/28/2010 14:00 1963429888 12 12804304896 78.4
Sun 2/28/2010 15:00 1972707328 12.1 12385775616 75.9
Sun 2/28/2010 16:00 1984831488 12.2 11934097408 73.1
Sun 2/28/2010 17:00 2005561344 12.3 11391574016 69.8
Sun 2/28/2010 18:00 2022236160 12.4 11014860800 67.5
Sun 2/28/2010 19:00 2033881088 12.5 10861117440 66.5
Sun 2/28/2010 20:00 2042576896 12.5 10673901568 65.4
Sun 2/28/2010 21:00 1968144384 12.1 10589315072 64.9
Sun 2/28/2010 22:00 1981603840 12.1 10480779264 64.2

[RahulS]

Please find the attached formatted usage pattern

[Michael Steele_2]

Hi

The arbiter here is going to be buffer cache hits, which is expected to be a 90 to 100% hit ratio.

I can see from your last posting that something changed, you dropped from 100% to 60%. How did you do this? Kill processes? Wait for a big sql to complete?

Review the link I post above, I'm interested in seeing this report:

SELECT 250 * TRUNC (rownum / 250) + 1 || ' to ' ||
250 * (TRUNC (rownum / 250) + 1) "Interval",
SUM (count) "Buffer Cache Hits"
FROM v$recent_bucket
GROUP BY TRUNC (rownum / 250)

Interval Buffer Cache Hits
--------------- --------------------
1 to 250 16083
251 to 500 11422
501 to 750 683
751 to 1000 177

[Michael Steele_2]

This is interesting, contradictory, and has some good arguements.

http://raj_oracle90.tripod.com/sitebuildercontent/sitebuilderfiles/whya99percentbuffercacheratioisnotok-carymillsap.pdf

[Horia Chirculescu]

Hello,

>maxdsiz 9764864 / 2147483647
>maxdsiz_64bit 102367232 / 274877906944

274 877 906 944 = ~270G This is huge. Are you sure you want this?


>semmni 61 / 30000
>semmns 3406 / 60000

The number of semaphores are also very high (30000 !)


>shmmax 8657051648 / 4398046511104

4 398 046 511 104 = ~ 4T Also, some huge value!

How does it look like the output for:

ipcs -moba

Oracle should use only one segment - and the biggest one from your system (maybe) - the one with 8657051648 bytes.


>shmmni 7 / 4096
>shmseg 3 / 512

Why did you set a very large segment identifiers? - 4096 is a huge value!

Horia.

[RahulS]

Hi,

All the parameters below are default.

maxdsiz 9764864 / 2147483647
maxdsiz_64bit 102367232 / 274877906944
maxfiles_lim 126 / 8192
maxssiz 106496 / 134217728
maxssiz_64bit 786432 / 1073741824
maxtsiz 7249920 / 1073741824

Please see the attached document for ipcs -moba output. SEGSZ for oracle is 4GB

[Michael Steele_2]

T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME

m 65542 0xc3eb8a14 --rw-rw---- oracle dba oracle dba 1428 4311756800 18386 20019 14:26:38

NATTCH 1428
SEGSZ 4311756800

Number attached: 1428, that is processes - You are pigging out this system

SEGMENT SIZE - In G bytes, 4 GB. Which is fine.

You can figure out the rest for total shared memory consumption yourself. However, you've got to tune down the number of users or processes on this box or buy more RAM.

[Horia Chirculescu]

Can you please share with us the output of

ps -e |grep oracle |wc -l

Horia.

[RahulS]

Here it is
--------------------------
DB Node1

# ps -aef|grep oracle|wc -l
764
--------------------------
DB Node2
$ ps -aef|grep oracle |wc -l
126