[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錯誤機率自然降低了.