Oracle Study之案例--通過IPCS查看共用記憶體之“怪現象”
在Oracle 11gR2環境下,通過ipcs命令查看共用記憶體,竟然發現分配給Oracle的記憶體只有4096Bytes,而在Oracle 10g環境下從未發現這種問題!
[root@rh6 ~]# ipcs -a
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 644 52 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 268 2
0x00000000 98307 gdm 600 393216 2 dest
0x00000000 131076 gdm 600 393216 2 dest
0x00000000 163845 gdm 600 393216 2 dest
0x00000000 196614 gdm 600 393216 2 dest
0x00000000 229383 gdm 600 393216 2 dest
0x4b4218ec 557064 oracle 660 4096 0
------ Semaphore Arrays --------
key semid owner perms nsems
0x00000000 0 root 600 1
0x00000000 98305 root 600 1
0x000000a7 327682 root 600 1
0xbe61d9cc 983043 oracle 660 154
------ Message Queues --------
key msqid owner perms used-bytes messages
資料庫版本:
16:27:09 SYS@ test3 >select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Oraccle 11g的通過以下兩個參數實現記憶體的自動個管理:
16:27:19 SYS@ test3 >show parameter mem
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 300M
memory_target big integer 300M
shared_memory_address integer 0
在Oracle 11g中用看Oracle的共用記憶體段---------IPCS
1、會不會是參數memory_max_target有關係呢?把它設為0,然後重啟資料庫。
16:28:11 SYS@ test3 >alter system set memory_target=0 ;
System altered.
16:36:44 SYS@ test3 >show parameter mem
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 300M
memory_target big integer 0
shared_memory_address integer 0
16:30:51 SYS@ test3 >startup force ;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 205524056 bytes
Database Buffers 100663296 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
再看共用記憶體:
[oracle@rh6 ~]$ ipcs -a
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 644 52 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 268 2
0x4b4218ec 622600 oracle 660 4096 0
------ Semaphore Arrays --------
key semid owner perms nsems
0xbe61d9cc 1114115 oracle 660 154
------ Message Queues --------
key msqid owner perms used-bytes messages
發現分配給Oracle的共用記憶體仍然很小,看來不是memory_target 參數的問題!
2、嘗試調整memory_max_target參數,將其恢複到系統預設值:
16:39:49 SYS@ test3 >alter system set sga_max_size=300m scope=spfile;
System altered.
16:40:06 SYS@ test3 >alter system reset memory_max_target scope=spfile sid='*';
System altered.
16:40:40 SYS@ test3 >startup force nomount;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 205524056 bytes
Database Buffers 100663296 bytes
Redo Buffers 6336512 bytes
16:40:52 SYS@ test3 >show parameter mem
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
16:40:59 SYS@ test3 >show parameter sga
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 180M
查看系統共用記憶體:
[oracle@rh6 ~]$ ipcs -a
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 root 644 52 2
0x00000000 32769 root 644 16384 2
0x00000000 65538 root 644 268 2
0x4b4218ec 884744 oracle 660 316669952 16
------ Semaphore Arrays --------
key semid owner perms nsems
0xbe61d9cc 1638403 oracle 660 154
------ Message Queues --------
key msqid owner perms used-bytes messages
看來是設定了memory_max_target參數的原因,導致通過ipcs查看到分配給Oracle的記憶體為4096Bytes!