[@more@]

SGA與共享記憶體

參考文獻：

<<深入淺出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無法建立資料庫.

SGA與共享記憶體

相關文章