[20190104]sga_target 的设置和ORA-04031错误.txt

[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错误概率自然降低了.