SGA与共享内存

[@more@]

SGA与共享内存

参考文献：

http://www.eygle.com/archives/2005/11/whats_mean_linux_shmmax.html

http://hi.chinaunix.net/?uid-11771621-action-viewspace-itemid-12230

http://www.diybl.com/course/7_databases/oracle/oraclejs/20090318/163173.html

<<深入浅出Oracle DBA入门进阶与诊断案例>>第四章内存管理

SGA在Linux和Unix上的设置和一个操作系统的内核参数有关。这个参数是shmmax.

　SHMMAX Available physical memory Defines the maximum allowable size of one shared memory segment。The SHMMAX setting should be large enough to hold the entire SGA in one shared memory segment. A low setting can cause creation of multiple shared memory segments which may lead to performance degradation.

SHMMAX参数定义的是系统允许的单个共享内存段的最大值，如果该参数小于sga的设置，sga虽然可以创建成功但是会被分配到多个共享段中。按照oracle的解释,SHMMAX应该比SGA大,否则会引发性能下降。所以通常推荐通过调整shmmax的值把sga限制在一个共享内存段中

Linux上该参数的缺省值通常为32M。

[root@neirong root]# more /proc/sys/kernel/shmmax 33554432

可以通过ipcs命令查看此设置下共享内存的分配，我们可以看到Oracle分配了多个共享内存段以满足SGA设置的需要:

[root@neirong root]# ipcs -sa ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 884736 oracle 640 4194304 14 0x00000000 917505 oracle 640 33554432 14 0x00000000 950274 oracle 640 33554432 14 0x00000000 983043 oracle 640 33554432 14 0x00000000 1015812 oracle 640 33554432 14 0x00000000 1048581 oracle 640 33554432 14 0x00000000 1081350 oracle 640 33554432 14 0x00000000 1114119 oracle 640 33554432 14 0x00000000 1146888 oracle 640 33554432 14 0x00000000 1179657 oracle 640 33554432 14 0x00000000 1212426 oracle 640 33554432 14 0x00000000 1245195 oracle 640 33554432 14 0x00000000 1277964 oracle 640 33554432 14 0x00000000 1310733 oracle 640 33554432 14 0x00000000 1343502 oracle 640 33554432 14 0x00000000 1376271 oracle 640 33554432 14 0x00000000 1409040 oracle 640 33554432 14 0x00000000 1441809 oracle 640 33554432 14 0x00000000 1474578 oracle 640 33554432 14 0x00000000 1507347 oracle 640 33554432 14 0x00000000 1540116 oracle 640 33554432 14 0x00000000 1572885 oracle 640 33554432 14 0x00000000 1605654 oracle 640 33554432 14 0x00000000 1638423 oracle 640 33554432 14 0x00000000 1671192 oracle 640 33554432 14 0x00000000 1703961 oracle 640 33554432 14 0x7a9c9900 1736730 oracle 640 4194304 56 ------ Semaphore Arrays -------- key semid owner perms nsems 0xfcf02e10 229376 oracle 640 154 ------ Message Queues -------- key msqid owner perms used-bytes messages

使用pmap我们可以看到每个共享内存段的地址空间

[root@neirong root]# ps -ef|grep 3102 oracle 3102 1 0 09:27 ? 00:00:26 ora_dbw0_hsmkt root 3447 3397 0 10:39 pts/4 00:00:00 grep 3102 [root@neirong root]# pmap 3102 ora_dbw0_hsmkt[3102] 08048000 (37308 KB) r-xp (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle 0a4b7000 (8804 KB) rw-p (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle 0ad50000 (380 KB) rw-p (00:00 0) 50000000 (4096 KB) rw-s (00:04 884736) /SYSV00000000 51000000 (32768 KB) rw-s (00:04 917505) /SYSV00000000 53000000 (32768 KB) rw-s (00:04 950274) /SYSV00000000 55000000 (32768 KB) rw-s (00:04 983043) /SYSV00000000 57000000 (32768 KB) rw-s (00:04 1015812) /SYSV00000000 59000000 (32768 KB) rw-s (00:04 1048581) /SYSV00000000 5b000000 (32768 KB) rw-s (00:04 1081350) /SYSV00000000 5d000000 (32768 KB) rw-s (00:04 1114119) /SYSV00000000 5f000000 (32768 KB) rw-s (00:04 1146888) /SYSV00000000 61000000 (32768 KB) rw-s (00:04 1179657) /SYSV00000000 63000000 (32768 KB) rw-s (00:04 1212426) /SYSV00000000 65000000 (32768 KB) rw-s (00:04 1245195) /SYSV00000000 67000000 (32768 KB) rw-s (00:04 1277964) /SYSV00000000 69000000 (32768 KB) rw-s (00:04 1310733) /SYSV00000000 6b000000 (32768 KB) rw-s (00:04 1343502) /SYSV00000000 6d000000 (32768 KB) rw-s (00:04 1376271) /SYSV00000000 6f000000 (32768 KB) rw-s (00:04 1409040) /SYSV00000000 71000000 (32768 KB) rw-s (00:04 1441809) /SYSV00000000 73000000 (32768 KB) rw-s (00:04 1474578) /SYSV00000000 75000000 (32768 KB) rw-s (00:04 1507347) /SYSV00000000 77000000 (32768 KB) rw-s (00:04 1540116) /SYSV00000000 79000000 (32768 KB) rw-s (00:04 1572885) /SYSV00000000 7b000000 (32768 KB) rw-s (00:04 1605654) /SYSV00000000 7d000000 (32768 KB) rw-s (00:04 1638423) /SYSV00000000 7f000000 (32768 KB) rw-s (00:04 1671192) /SYSV00000000 81000000 (32768 KB) rw-s (00:04 1703961) /SYSV00000000 83000000 (4 KB) r--s (00:04 1736730) /SYSV7a9c9900 83001000 (644 KB) rw-s (00:04 1736730) /SYSV7a9c9900 830a2000 (4 KB) r--s (00:04 1736730) /SYSV7a9c9900 830a3000 (3444 KB) rw-s (00:04 1736730) /SYSV7a9c9900 b6ec2000 (44 KB) r-xp (68:03 32811) /lib/libnss_files-2.3.2.so b6ecd000 (4 KB) rw-p (68:03 32811) /lib/libnss_files-2.3.2.so b6ece000 (512 KB) rw-p (68:03 40360) /dev/zero b6f4e000 (1140 KB) rw-p (00:00 0) b706b000 (1224 KB) r-xp (68:03 114692) /lib/tls/libc-2.3.2.so b719d000 (12 KB) rw-p (68:03 114692) /lib/tls/libc-2.3.2.so b71a0000 (12 KB) rw-p (00:00 0) b71a3000 (72 KB) r-xp (68:03 32795) /lib/libnsl-2.3.2.so b71b5000 (4 KB) rw-p (68:03 32795) /lib/libnsl-2.3.2.so b71b6000 (8 KB) rw-p (00:00 0) b71b8000 (52 KB) r-xp (68:03 114696) /lib/tls/libpthread-0.60.so b71c5000 (4 KB) rw-p (68:03 114696) /lib/tls/libpthread-0.60.so b71c6000 (8 KB) rw-p (00:00 0) b71c8000 (132 KB) r-xp (68:03 114694) /lib/tls/libm-2.3.2.so b71e9000 (4 KB) rw-p (68:03 114694) /lib/tls/libm-2.3.2.so b71ea000 (8 KB) r-xp (68:03 32791) /lib/libdl-2.3.2.so b71ec000 (4 KB) rw-p (68:03 32791) /lib/libdl-2.3.2.so b71ed000 (2940 KB) r-xp (68:06 491677) /opt/oracle/product/9.2.0/lib/libjox9.so b74cc000 (1088 KB) rw-p (68:06 491677) /opt/oracle/product/9.2.0/lib/libjox9.so b75dc000 (12 KB) rw-p (00:00 0) b75df000 (4 KB) r-xp (68:06 491685) /opt/oracle/product/9.2.0/lib/libskgxn9.so b75e0000 (8 KB) rw-p (68:06 491685) /opt/oracle/product/9.2.0/lib/libskgxn9.so b75e2000 (4 KB) r-xp (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.so b75e3000 (4 KB) ---p (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.so b75e4000 (4 KB) rw-p (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.so b75e5000 (4 KB) r-xp (68:06 491688) /opt/oracle/product/9.2.0/lib/libodmd9.so b75e6000 (4 KB) rw-p (68:06 491688) /opt/oracle/product/9.2.0/lib/libodmd9.so b75e7000 (4 KB) r-xp (68:03 101245) /etc/libcwait.so b75e8000 (4 KB) rw-p (68:03 101245) /etc/libcwait.so b75ea000 (4 KB) rw-p (00:00 0) b75eb000 (84 KB) r-xp (68:03 32778) /lib/ld-2.3.2.so b7600000 (4 KB) rw-p (68:03 32778) /lib/ld-2.3.2.so bfff8000 (32 KB) rwxp (00:00 0) mapped: 881332 KB writable/private: 12056 KB shared: 827392 KB

为了避免多个共享内存段，我们可以修改shmmax内核参数，使SGA存在于一个共享内存段中。

通过修改/proc/sys/kernel/shmmax参数可以达到此目的。

[root@neirong root]# echo 1073741824 > /proc/sys/kernel/shmmax [root@neirong root]# more /proc/sys/kernel/shmmax 1073741824

这里设为1G。

对于shmmax文件的修改，系统重新启动后会复位。可以通过修改 /etc/sysctl.conf 使更改永久化。

在该文件内添加以下一行,这个更改在系统重新启动后生效

kernel.shmmax = 1073741824 重起数据库使更改生效:

SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> ! [oracle@neirong oracle]$ ipcs -sa ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status ------ Semaphore Arrays -------- key semid owner perms nsems ------ Message Queues -------- key msqid owner perms used-bytes messages [oracle@neirong oracle]$ exit exit SQL> startup ORACLE instance started. Total System Global Area 839980852 bytes Fixed Size 452404 bytes Variable Size 201326592 bytes Database Buffers 637534208 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> ! ipcs -sa ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x7a9c9900 1769472 oracle 640 859832320 35 ------ Semaphore Arrays -------- key semid owner perms nsems 0xfcf02e10 360448 oracle 640 154 ------ Message Queues -------- key msqid owner perms used-bytes messages

此时进程的pmap映射显示为：

[oracle@neirong bdump]$ pmap 4178 ora_lgwr_hsmkt[4178] 08048000 (37308 KB) r-xp (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle 0a4b7000 (8804 KB) rw-p (68:06 1525072) /opt/oracle/product/9.2.0/bin/oracle 0ad50000 (3320 KB) rw-p (00:00 0) 50000000 (835584 KB) rw-s (00:04 1835008) /SYSV7a9c9900 83000000 (4 KB) r--s (00:04 1835008) /SYSV7a9c9900 83001000 (644 KB) rw-s (00:04 1835008) /SYSV7a9c9900 830a2000 (4 KB) r--s (00:04 1835008) /SYSV7a9c9900 830a3000 (3444 KB) rw-s (00:04 1835008) /SYSV7a9c9900 b6bb7000 (4112 KB) rw-p (00:00 0) b6fbb000 (44 KB) r-xp (68:03 32811) /lib/libnss_files-2.3.2.so b6fc6000 (4 KB) rw-p (68:03 32811) /lib/libnss_files-2.3.2.so b6fc7000 (512 KB) rw-p (68:03 40360) /dev/zero b7047000 (144 KB) rw-p (00:00 0) b706b000 (1224 KB) r-xp (68:03 114692) /lib/tls/libc-2.3.2.so b719d000 (12 KB) rw-p (68:03 114692) /lib/tls/libc-2.3.2.so b71a0000 (12 KB) rw-p (00:00 0) b71a3000 (72 KB) r-xp (68:03 32795) /lib/libnsl-2.3.2.so b71b5000 (4 KB) rw-p (68:03 32795) /lib/libnsl-2.3.2.so b71b6000 (8 KB) rw-p (00:00 0) b71b8000 (52 KB) r-xp (68:03 114696) /lib/tls/libpthread-0.60.so b71c5000 (4 KB) rw-p (68:03 114696) /lib/tls/libpthread-0.60.so b71c6000 (8 KB) rw-p (00:00 0) b71c8000 (132 KB) r-xp (68:03 114694) /lib/tls/libm-2.3.2.so b71e9000 (4 KB) rw-p (68:03 114694) /lib/tls/libm-2.3.2.so b71ea000 (8 KB) r-xp (68:03 32791) /lib/libdl-2.3.2.so b71ec000 (4 KB) rw-p (68:03 32791) /lib/libdl-2.3.2.so b71ed000 (2940 KB) r-xp (68:06 491677) /opt/oracle/product/9.2.0/lib/libjox9.so b74cc000 (1088 KB) rw-p (68:06 491677) /opt/oracle/product/9.2.0/lib/libjox9.so b75dc000 (12 KB) rw-p (00:00 0) b75df000 (4 KB) r-xp (68:06 491685) /opt/oracle/product/9.2.0/lib/libskgxn9.so b75e0000 (8 KB) rw-p (68:06 491685) /opt/oracle/product/9.2.0/lib/libskgxn9.so b75e2000 (4 KB) r-xp (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.so b75e3000 (4 KB) ---p (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.so b75e4000 (4 KB) rw-p (68:06 491567) /opt/oracle/product/9.2.0/lib/libskgxp9.so b75e5000 (4 KB) r-xp (68:06 491688) /opt/oracle/product/9.2.0/lib/libodmd9.so b75e6000 (4 KB) rw-p (68:06 491688) /opt/oracle/product/9.2.0/lib/libodmd9.so b75e7000 (4 KB) r-xp (68:03 101245) /etc/libcwait.so b75e8000 (4 KB) rw-p (68:03 101245) /etc/libcwait.so b75ea000 (4 KB) rw-p (00:00 0) b75eb000 (84 KB) r-xp (68:03 32778) /lib/ld-2.3.2.so b7600000 (4 KB) rw-p (68:03 32778) /lib/ld-2.3.2.so bfffc000 (16 KB) rwxp (00:00 0) mapped: 899660 KB writable/private: 18096 KB shared: 839680 KB

实际上，如果没有修改shmmax参数，Oracle在启动过程中就会报出以下错误:

Starting ORACLE instance (normal) Thu Nov 17 09:27:29 2005 WARNING: EINVAL creating segment of size 0x0000000033400000 fix shm parameters in /etc/system or equivalent

这是一个warning的提示，说明是建议修改，但并非强制内容。

如果物理内存是 2 G, 假设这台DB Server上还有Apache 在运行，那么shmmax 中设置的内存也会被Apache 来使用，那么分配的 2 块共享内存段给Oracle 是否就是 2 * 1G , 还是仅仅满足 SGA需求的 1.2 G就停止分配 ，其他的内存的一部分分配给Oracle PGA 和软件 Apache 来使用 ？

　　想到这个问题,是因为在安装oracle时,常常需要修改内核文件/etc/sysctl.conf,需要设置这个参数.

　　设置oracle共享内存段：

　　以root身份进行修改 /etc/sysctl.conf 这个文件，加入以下的语句：

　　kernel.shmmax=2147483648

　　kernel.shmmni =4096

　　kernel.shmall =2097152

　　kernel.sem =25032000100128

　　fs.file-max =65536

　　net.ipv4.ip_local_port_range =102465000

　　注：

SEMMNI 100 Defines the maximum number of semaphore sets in the entire system.

SEMMNS 256 Defines the maximum semaphores on the system. This setting is a minimum recommended value, for initial installation only. The SEMMNS parameter should be set to the sum of the PROCESSES parameter for each Oracle database, adding the largest one twice, and then adding an additional 10 for each database.

SEMOPM 100 Defines the maximum number of operations for each semop call.

SEMMSL 100 Defines the minimum recommended value, for initial installation only.

SHMMAX 2147483648 Defines the maximum allowable size of one shared memory segment. 2 GB for SMP kernel. The recommended size is half the RAM size.

SHMMIN 1 Defines the minimum allowable size of a single shared memory segment.

SHMMNI 100 Defines the maximum number of shared memory segments in the entire system.

SHMSEG 4096 Defines the maximum number of shared memory segments one process can attach.

SHMVMX 32767 Defines the maximum value of a semaphore.

　　一般情况下可以设置最大共享内存为物理内存的一半，如果物理内存是2G，则可以设置最大共享内存为1073741824，如上；如物理内存是1G，则可以设置最大共享内存为512 *1024 *1024 =536870912；以此类推。

　　在redhat上最大共享内存不建议超过　　4*1024*1024*1024-1=4294967295

　　设置完成后用命令more /etc/sysctl.conf |grepkernel.s 检查。

建议重启系统再继续下面的操作。

以下是个案例：shmmax设置不当导致dcba无法创建数据库.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16405897/viewspace-1031761/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16405897/viewspace-1031761/