[20190104]sga_target 的设置和ORA-04031错误.txt
--//链接http://www.itpub.net/thread-2104417-1-1.html的问题,我猜测跟共享池的子池有关.
--//通过测试说明问题.
--//版本11.2.0.4
1.检查参数文件:
$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=50G
sga_max_size=50G
--//设置sga_target=50G.
2.启动到nomount:
SYS@xxxx> startup nomount
ORACLE instance started.
Total System Global Area 5.3447E+10 bytes
Fixed Size 2265864 bytes
Variable Size 5100276984 bytes
Database Buffers 4.8318E+10 bytes
Redo Buffers 26480640 bytes
SYS@xxxx> show parameter cpu_count
NAME TYPE VALUE
---------- -------- -------
cpu_count integer 24
--//cpu_count=24.
SYS@xxxx> @ hide _kghdsidx_count
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------- ------------------ ------------- ------------- ------------
_kghdsidx_count max kghdsidx count TRUE 6 6
SYS@xxxx> @ hide _enable_shared_pool_durations
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------------- --------------------------------------- ------------- ------------- ------------
_enable_shared_pool_durations temporary to disable/enable kgh policy TRUE TRUE TRUE
--//_kghdsidx_count=6,这个数值与cpu数量存在密切关系.24/4 = 6,但是最大只能是7.
--//是否建立这么多还与共享池大小有关.可以查询如下确定,好像11g开始每个子池最大512M.
--//11g还会将单个子缓冲池分割为4个子分区进行管理.
SYS@xxxx> select addr,latch#,level#,child#,name,gets from v$latch_children where name='shared pool';
ADDR LATCH# LEVEL# CHILD# NAME GETS
---------------- ---------- ---------- ---------- ----------- ----
00000000601102D8 336 7 7 shared pool 13
0000000060110238 336 7 6 shared pool 297
0000000060110198 336 7 5 shared pool 187
00000000601100F8 336 7 4 shared pool 328
0000000060110058 336 7 3 shared pool 348
000000006010FFB8 336 7 2 shared pool 230
000000006010FF18 336 7 1 shared pool 225
7 rows selected.
3.而如果修改参数:
$ cat initxxxx.ora
db_name=xxxx
instance_name=xxxx
sga_target=0
sga_max_size=50G
--//设置sga_target=0.
SYS@xxxx> startup nomount
ORACLE instance started.
Total System Global Area 5.3447E+10 bytes
Fixed Size 2265864 bytes
Variable Size 5.3284E+10 bytes
Database Buffers 134217728 bytes
Redo Buffers 26480640 bytes
SYS@xxxx> @ hide _kghdsidx_count
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------- ------------------ ------------- ------------- ------------
_kghdsidx_count max kghdsidx count TRUE 1 1
SYS@xxxx> @ hide _enable_shared_pool_durations
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------------- --------------------------------------- ------------- ------------- ------------
_enable_shared_pool_durations temporary to disable/enable kgh policy TRUE FALSE FALSE
SYS@xxxx> select addr,latch#,level#,child#,name,gets from v$latch_children where name='shared pool';
ADDR LATCH# LEVEL# CHILD# NAME GETS
---------------- ---------- ---------- ---------- ----------- ----
00000000601102D8 336 7 7 shared pool 0
0000000060110238 336 7 6 shared pool 0
0000000060110198 336 7 5 shared pool 0
00000000601100F8 336 7 4 shared pool 0
0000000060110058 336 7 3 shared pool 0
000000006010FFB8 336 7 2 shared pool 0
000000006010FF18 336 7 1 shared pool 1487
7 rows selected.
--//_kghdsidx_count=1.
--//我估计可能某个子池内存消耗太多,oracle并不会从别的子池借内存使用,导致出现ora-04031错误.
--//而sga_target=0的情况下,oracle自动关闭这个特性,不能建立多个子池,这样一个大池子出现ora-04031错误概率自然降低了.