Oracle Study之案例--通过IPCS查看共享内存之“怪现象”
在Oracle 11gR2环境下，通过ipcs命令查看共享内存，竟然发现分配给Oracle的内存只有4096Bytes，而在Oracle 10g环境下从未发现这种问题！
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[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
|
数据库版本：
|
1
2
3
4
5
6
7
8
|
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的通过以下两个参数实现内存的自动个管理：
|
1
2
3
4
5
6
7
|
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
|
1、会不会是参数memory_max_target有关系呢？把它设为0，然后重启数据库。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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.
|
再看共享内存：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[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参数，将其恢复到系统默认值：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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
|
查看系统共享内存:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
[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！
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1599787，如需转载请自行联系原作者