Troubleshooting-ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”SELECT SYS_CONTEXT(‘USERENV’…”,”SQLA”,”tmp”)

周末本来在家休息,突然接到值班电话说一套数据库什么业务都运行不了,直接都报:ORA-04031: unable to allocate 32 bytes of shared memory (“shared pool”,”SELECT SYS_CONTEXT(‘USERENV’…”,”SQLA”,”tmp”) 之类的错误。这类问题一般先看告警日志。确实可以看到job进程,还有mmon进程都出现大量的ORA-04031错误。而这个错误中间是一段SQL代码,一般都是递归调用的系统SQL。后面两个参数是SQLA,TMP。

此类问题我们一般继续查trace。通过trace文件,我们发现历史的等待事件都是”SGA: allocation forcing component growth”。这里表示SGA组件无法在增长了,也就是被撑爆了。

Wait State:
  fixed_waits=0 flags=0x21 boundary=(nil)/-1
Session Wait History:
    elapsed time of 0.001095 sec since last wait
 0: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38677 seq_num=38878 snap_id=101
    wait times: snap=0.000000 sec, exc=5.027528 sec, total=5.449107 sec
    wait times: max=infinite
    wait counts: calls=100 os=100
    occurred after 0.000000 sec of elapsed time
 1: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38777 seq_num=38877 snap_id=1
    wait times: snap=0.003845 sec, exc=0.003845 sec, total=0.003845 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000000 sec of elapsed time
 2: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38677 seq_num=38876 snap_id=100
    wait times: snap=0.050162 sec, exc=5.027528 sec, total=5.445262 sec
    wait times: max=infinite
    wait counts: calls=100 os=100
    occurred after 0.000000 sec of elapsed time
 3: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38776 seq_num=38875 snap_id=1
    wait times: snap=0.003839 sec, exc=0.003839 sec, total=0.003839 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000000 sec of elapsed time
 4: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38677 seq_num=38874 snap_id=99
    wait times: snap=0.050173 sec, exc=4.977366 sec, total=5.391261 sec
    wait times: max=infinite
    wait counts: calls=99 os=99
    occurred after 0.000000 sec of elapsed time
 5: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38775 seq_num=38873 snap_id=1
    wait times: snap=0.003830 sec, exc=0.003830 sec, total=0.003830 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000000 sec of elapsed time
 6: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38677 seq_num=38872 snap_id=98
    wait times: snap=0.050203 sec, exc=4.927193 sec, total=5.337258 sec
    wait times: max=infinite
    wait counts: calls=98 os=98
    occurred after 0.000000 sec of elapsed time
 7: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38774 seq_num=38871 snap_id=1
    wait times: snap=0.003799 sec, exc=0.003799 sec, total=0.003799 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000000 sec of elapsed time
 8: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38677 seq_num=38870 snap_id=97
    wait times: snap=0.050172 sec, exc=4.876990 sec, total=5.283256 sec
    wait times: max=infinite
    wait counts: calls=97 os=97
    occurred after 0.000000 sec of elapsed time
 9: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=38773 seq_num=38869 snap_id=1
    wait times: snap=0.003826 sec, exc=0.003826 sec, total=0.003826 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000000 sec of elapsed time

那么继续检查trace,确认shared pool中组件使用内存的情况。

TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"KGH: NO ACCESS            "       5758 MB 92%
"free memory               "        156 MB  2%
"KGLH0                     "        113 MB  2%
"db_block_hash_buckets     "         43 MB  1%
"kglsim object batch       "         27 MB  0%
"KQR L PO                  "         26 MB  0%
"FileOpenBlock             "         24 MB  0%
"KGLHD                     "         22 MB  0%
"kglsim heap               "         15 MB  0%
"state objects             "         13 MB  0%
     -----------------------------------------
free memory                         156 MB
memory alloc.                      6116 MB
Sub total                          6272 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"KGH: NO ACCESS            "       6014 MB 92%
"free memory               "        205 MB  3%
"KGLH0                     "        121 MB  2%
"SQLA                      "         25 MB  0%
"KGLHD                     "         20 MB  0%
"db_block_hash_buckets     "         17 MB  0%
"kglsim object batch       "         16 MB  0%
"kglsim heap               "       8757 KB  0%
"ASH buffers               "       8192 KB  0%
"enqueue                   "       8032 KB  0%
     -----------------------------------------
free memory                         205 MB
memory alloc.                      6323 MB
Sub total                          6528 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"KGH: NO ACCESS            "       6140 MB 92%
"free memory               "        222 MB  3%
"KGLH0                     "        120 MB  2%
"db_block_hash_buckets     "         43 MB  1%
"KGLHD                     "         19 MB  0%
"SQLA                      "         14 MB  0%
"ASH buffers               "       8192 KB  0%
"private strands           "       7448 KB  0%
"event statistics per sess "       6473 KB  0%
"ksunfy : SSO free list    "       6158 KB  0%
     -----------------------------------------
free memory                         222 MB
memory alloc.                      6434 MB
Sub total                          6656 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"KGH: NO ACCESS            "       6015 MB 92%
"free memory               "        191 MB  3%
"KGLH0                     "        120 MB  2%
"SQLA                      "         48 MB  1%
"kglsim object batch       "         27 MB  0%
"db_block_hash_buckets     "         17 MB  0%
"KGLHD                     "         16 MB  0%
"kglsim heap               "         14 MB  0%
"ASH buffers               "       8192 KB  0%
"private strands           "       7448 KB  0%
     -----------------------------------------
free memory                         191 MB
memory alloc.                      6337 MB
Sub total                          6528 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 5
----------------------------------------------
"KGH: NO ACCESS            "       5247 MB 89%
"free memory               "        198 MB  3%
"KGLH0                     "        117 MB  2%
"kglsim object batch       "         56 MB  1%
"db_block_hash_buckets     "         43 MB  1%
"SQLA                      "         38 MB  1%
"kglsim heap               "         30 MB  1%
"KGLHD                     "         22 MB  0%
"KQR M PO                  "         15 MB  0%
"KQR M SO                  "         14 MB  0%
     -----------------------------------------
free memory                         198 MB
memory alloc.                      5690 MB
Sub total                          5888 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 6
----------------------------------------------
"KGH: NO ACCESS            "       5373 MB 91%
"free memory               "        236 MB  4%
"KGLH0                     "        121 MB  2%
"kglsim object batch       "         25 MB  0%
"KGLHD                     "         19 MB  0%
"db_block_hash_buckets     "         17 MB  0%
"kglsim heap               "         14 MB  0%
"ASH buffers               "       8192 KB  0%
"private strands           "       7315 KB  0%
"event statistics per sess "       6473 KB  0%
     -----------------------------------------
free memory                         236 MB
memory alloc.                      5652 MB
Sub total                          5888 MB
TOTALS ---------------------------------------
Total free memory                  1209 MB
Total memory alloc.                  36 GB
Grand total                          37 GB

这里可以看到,在我们6分sub pool当中,大量的内存被分配给了KGH: NO ACCESS。基本上每个子池都有5GB左右的内存分配给了KGH: NO ACCESS。通过查询Mos上的文档《ORA-04031: Unable To Allocate 32 Bytes Of Shared Memory (“shared pool”,”select tablespace_id, rfno, …”,”SQLA”,”tmp”)” (Doc ID 1986741.1)》。可以发现是非常吻合的。而该问题解决办法是需要修改参数“_enable_shared_pool_durations”,将其设置成false。

在Jonathan Lewis的书《Oracle核心技术》中,讲解共享池的时候,介绍过Duration。不同的任务需要分配不同大小的块,他们使用块的方式也不一样。按照任务功能不同来分割共享池。可以将那些会造成大量碎片的任务与那些能重用最近释放的空闲内存的任务分离开来。可以发现,数据字典缓存所需要的内存来源于duration 1。游标位于duration 2,游标的SQL区分配位于duration 3。而MOS文档说的更加详细,The shared pool can have subpools with 4 durations. These durations are “instance”, “session”, “cursor”, and “execution”. By default these durations are separate from each other.

Oracle version >= 10.2 and < 12.1

Oracle version >= 12.1

而在12.1里面。durations就分成了2组。这个从mos文档:ORA-4031: unable to allocate 4160 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(4,0)”,”modification “) (Doc ID 1675470.1)中有说明。未发布的Bug 8857940的更改包含在12.1.0.1中,并允许将共享池持续时间分组为两组,以允许更好地共享内存(颗粒移动)并避免ORA-4031

如果我们把 _enable_shared_pool_durations设置成flase,那么duration 在Sub Pool中将会消失。所有的durations将合并到一个池中,因此不容易被耗尽。如果设置SGA_TARGET为0,enable_shared_pool_duration自动被设置为False。也可以解决这类问题。当然SGA_TARGET可以在线设置为0,但是_enable_shared_pool_durations在SGA_TARGET设置为0后不会变成false,要重启之后才会生效。

SQL> show parameter SGA_TARGET

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
SQL> SELECT   i.ksppinm name,  
  2             i.ksppdesc description,  
  3             CV.ksppstvl VALUE,  
  4             CV.ksppstdf isdefault,  
  5             DECODE (BITAND (CV.ksppstvf, 7),  
  6                     1, 'MODIFIED',  
  7                     4, 'SYSTEM_MOD',  
  8                     'FALSE')  
  9                ismodified,  
 10             DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted  
 11      FROM   sys.x$ksppi i, sys.x$ksppcv CV  
 12     WHERE       i.inst_id = USERENV ('Instance')  
 13             AND CV.inst_id = USERENV ('Instance')  
 14             AND i.indx = CV.indx  
 15             AND i.ksppinm LIKE '/_%' ESCAPE '/'  
 16             and i.ksppinm='_enable_shared_pool_durations'
 17  ORDER BY   REPLACE (i.ksppinm, '_', ''); 

NAME                           DESCRIPTION                                            VALUE                          ISDEFAULT ISMODIFIED ISADJ
------------------------------ ------------------------------------------------------ ------------------------------ --------- ---------- -----
_enable_shared_pool_durations  temporary to disable/enable kgh policy                 FALSE                          TRUE      FALSE      FALSE
分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *