ORA-04031一例 ("shared pool","…","SQLA","tmp")

今天突然收到告警，一台11.2.0.4的数据库报错ORA-04031。

1
2
SYS@> show sga        
ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp")

根据错误代码可以判断是共享池使用出了问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@xxx trace]$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and 
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.  
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

从smon日志中可以看到大量的sga组件等待扩展的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Session Wait History:
    elapsed time of 0.261076 sec since last wait
 0: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=2337078 seq_num=43657 snap_id=101
    wait times: snap=0.000000 sec, exc=5.069536 sec, total=13.674583 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=2337178 seq_num=43656 snap_id=1
    wait times: snap=0.003531 sec, exc=0.003531 sec, total=0.003531 sec
    wait times: max=infinite
    wait counts: calls=2 os=2
    occurred after 0.000000 sec of elapsed time
 2: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=2337078 seq_num=43655 snap_id=100
    wait times: snap=0.050112 sec, exc=5.069536 sec, total=13.671052 sec
    wait times: max=infinite
    wait counts: calls=100 os=100
    occurred after 0.000000 sec of elapsed time
...省略

表示sga所分配的内存已经使用完，不足以支撑组件的扩展。这个库使用的sga_target,所以sga的各个组件都能自适应分配内存大小

1
2
3
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ------------------------------------------------------
sga_target                                                   big integer 12G

在sga中有6个子池，每个子池中大部分内存都分配给了"KGH: NO ACCESS",都在1G左右。

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"KGH: NO ACCESS            "       1118 MB 67%
"free memory               "        205 MB 12%
"KGLH0                     "         86 MB  5%
"SQLA                      "         70 MB  4%
"KGLS                      "         21 MB  1%
"KGLHD                     "         15 MB  1%
"VIRTUAL CIRCUITS          "         15 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"kglsim object batch       "         10 MB  1%
"private strands           "       9443 KB  1%
     -----------------------------------------
free memory                         205 MB
memory alloc.                      1459 MB
Sub total                          1664 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"KGH: NO ACCESS            "       1277 MB 77%
"free memory               "        260 MB 16%
"db_block_hash_buckets     "         13 MB  1%
"kglsim object batch       "         11 MB  1%
"ASH buffers               "         10 MB  1%
"private strands           "       9443 KB  1%
"event statistics per sess "       8335 KB  0%
"ksunfy : SSO free list    "       7929 KB  0%
"dbktb: trace buffer       "       6848 KB  0%
"kglsim heap               "       6444 KB  0%
     -----------------------------------------
free memory                         260 MB
memory alloc.                      1404 MB
Sub total                          1664 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"KGH: NO ACCESS            "       1086 MB 68%
"free memory               "        192 MB 12%
"KGLH0                     "         94 MB  6%
"SQLA                      "         39 MB  2%
"FileOpenBlock             "         30 MB  2%
"KGLHD                     "         16 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"enqueue                   "         12 MB  1%
"kglsim object batch       "         11 MB  1%
"KGLS                      "         10 MB  1%
     -----------------------------------------
free memory                         192 MB
memory alloc.                      1408 MB
Sub total                          1600 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"KGH: NO ACCESS            "       1182 MB 67%
"free memory               "        243 MB 14%
"KGLH0                     "         81 MB  5%
"SQLA                      "         48 MB  3%
"KQR M PO                  "         38 MB  2%
"KGLS                      "         17 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"KGLHD                     "         12 MB  1%
"kglsim object batch       "         10 MB  1%
"ASH buffers               "         10 MB  1%
     -----------------------------------------
free memory                         243 MB
memory alloc.                      1517 MB
Sub total                          1760 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 5
----------------------------------------------
"KGH: NO ACCESS            "       1271 MB 74%
"free memory               "        221 MB 13%
"SQLA                      "         51 MB  3%
"KGLH0                     "         45 MB  3%
"db_block_hash_buckets     "         13 MB  1%
"ASH buffers               "         10 MB  1%
"kglsim object batch       "       9622 KB  1%
"private strands           "       9443 KB  1%
"event statistics per sess "       8335 KB  0%
"ksunfy : SSO free list    "       7942 KB  0%
     -----------------------------------------
free memory                         221 MB
memory alloc.                      1507 MB
Sub total                          1728 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 6
----------------------------------------------
"KGH: NO ACCESS            "       1182 MB 70%
"free memory               "        232 MB 14%
"KGLH0                     "         83 MB  5%
"SQLA                      "         48 MB  3%
"KGLHD                     "         13 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"KGLS                      "         13 MB  1%
"kglsim object batch       "         12 MB  1%
"private strands           "       9576 KB  1%
"event statistics per sess "       8335 KB  0%
     -----------------------------------------
free memory                         232 MB
memory alloc.                      1464 MB
Sub total                          1696 MB
TOTALS ---------------------------------------
Total free memory                  1571 MB
Total memory alloc.                  10 GB
Grand total                          11 GB
==============================================

在每个子池中，会有4个持续时间部分（durations），分别是"instance", “session”, “cursor”, 和 “execution”。 主要目的是为了根据不同的事务种类对共享池申请的内存大小和方式的不同，而将会造成大量碎片、或者能重用的任务区分开来，它们之间互相独立、互不干扰。比如cursor所需内存来源于duration2，execution所需内存来源于duration3等等。

12c以前的版本

12c以后的版本

为了能使shared pool里的内存得到充分利用，我们通过隐含参数来去掉durations的限制。

1
2
3
4
       NUM N_HEX NAME                                                   VALUE                          DESCRIPTION
---------- ----- ------------------------------------------------------ ------------------------------ ---------------------------------------------
       111    6F _enable_shared_pool_durations                          TRUE                           temporary to disable/enable kgh policy

最后解决办法

1
2
SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;
- restart the database

设置"_enable_shared_pool_durations = false"的主要好处在于所有的durations会合并成一个池，所以就不会出现其中一个duration内存不足而其他duration还有空余的情况。

这个问题将在12c版本以后解决，由于体系的改变导致可以允许子池减少对duration的需求。

Troubleshooting
#ora-
ORA-04031一例 ("shared pool","…","SQLA","tmp")
https://www.xbdba.com/2018/12/28/ora-04031-shared-pool-sqla-tmp/
作者
xbdba
发布于
2018年12月28日
许可协议