报表数据库4031-白红宇

报表数据库4031

阅读量：2434 次

发布时间：2019-05-10

本文共 72611 字，大约阅读时间需要 242 分钟。

报表数据库11.2.0.3

凌晨3点多 ORA-4031初步分析如下

SQL> show parameter shared_pool

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

shared_pool_reserved_size big integer 191260262

shared_pool_size big integer 3G

1 每个报错都是32 bytes，共享池高度碎片化。

ORA-04031: unable to allocate 32 bytes of shared memory ...

目前的非绑定变量SQL

SQL> select max(sql_id), FORCE_MATCHING_SIGNATURE, count(*) from v$sqlarea having(count(*)) > 10 group by FORCE_MATCHING_SIGNATURE order by 3 desc;

MAX(SQL_ID) FORCE_MATCHING_SIGNATURE COUNT(*)

------------- ------------------------ --------------------

gzxc3g7k3sc5n 15893216616221909352 1045

gzytrw39gf9yu 16484046957026113714 857

gzyv70wrpwsqc 17569133176929366965 817

gzhp074q8kwgh 0 748

gzu2067rd7jyw 7756258419218828704 366

gyhk2suas59uf 10608916155249212908 298

gz7tgax3fh9a4 1624161300791768224 293

gx7snay2cxqy5 7564646865446163327 252

gz05yp57n9j07 3886515924180660832 247

gz8yyn49x2vuk 1362246611959407211 169

gspy97xymz63w 18040382774461829989 167

gvg739mddh5j0 11140912056169054663 165

gjqsukh52bzas 13508424748370422794 99

gy0qparbcjddd 14508944154931705580 84

gs4w7trh69nfq 17947976122481830813 63

gyu6usscfbqk2 111259297832933531 62

gkxv3a38va5t5 7498119390662785279 51

gkwa70v3cztxw 18023912117949046031 43

gmdaxxfj62p4c 7373602686629649627 31

g41nvxyd1dftg 6128566623526257588 26

fr3pfvpnzqdvc 4544829194680067718 24

gsrs32gzsabyy 2165266382939917162 23

fdnxkh6ynt0db 7590092505420371665 23

dfyc1va7ykcmu 11117109627900895533 18

g9qr4y92mhs3n 11810285729586130302 18

g34dhdqx94wgq 953775746539613178 18

gpp7nzgwk38m9 4045863570885546702 17

ctazm8sa18duk 10982123986297386714 12

2 关于reserve pool 使用情况

SQL> SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures,

2 last_failure_size

3 FROM v$shared_pool_reserved;

FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE

---------- ------------- ---------- ------------- ---------------- -----------------

143987384 867393.88 67538344 406857.494 35316 4064

_shared_pool_reserved_min_alloc的值是4400 ，大于LAST_FAILURE_SIZE 4064。

SELECT nam.ksppinm NAME, val.ksppstvl VALUE

FROM x$ksppi nam, x$ksppsv val

WHERE nam.indx = val.indx AND nam.ksppinm = '_shared_pool_reserved_min_alloc'

ORDER BY 1;

NAME VALUE

---------------------------------------- ------------------------------

_shared_pool_reserved_min_alloc 4400

说明共享池找不到连续空间后申请 reserve pool时由于没有达到 _shared_pool_reserved_min_alloc而失败。

这方面可以通过加大共享池和保留池，降低 _shared_pool_reserved_min_alloc缓解。

3 top event出现了SGA: allocation forcing component growth

这是在自动内存管理中 SGA扩展的等待。metalink上有说明可能会造成 4031。 (How To Deal With "SGA: allocation forcing component growth" Wait Events (Doc ID 1270867.1))

目前实例设置了sga_target和sga_max_size仅为24G，

从sga resize操作也可以看出共享池在出问题附近进行了多次扩展， buffer cache在做相应的收缩。

SQL> select component, oper_type, initial_size, target_size, start_time, end_time from v$sga_resize_ops where start_time > trunc(sysdate) order by start_time;

COMPONENT OPER_TYPE INITIAL_SIZE TARGET_SIZE START_TIME END_TIME

---------------------------------------------------------------- ------------- -------------------- -------------------- ------------------- -------------------

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:28 2015-01-12 03:18:28

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:28 2015-01-12 03:18:28

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:29 2015-01-12 03:18:29

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:29 2015-01-12 03:18:29

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:30 2015-01-12 03:18:30

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:30 2015-01-12 03:18:30

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:31 2015-01-12 03:18:31

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:31 2015-01-12 03:18:31

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:32 2015-01-12 03:18:32

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:32 2015-01-12 03:18:32

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:32 2015-01-12 03:18:32

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:33 2015-01-12 03:18:33

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:33 2015-01-12 03:18:33

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:33 2015-01-12 03:18:33

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:34 2015-01-12 03:18:34

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:34 2015-01-12 03:18:35

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:34 2015-01-12 03:18:34

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:34 2015-01-12 03:18:35

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:34 2015-01-12 03:18:34

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:35 2015-01-12 03:18:35

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:35 2015-01-12 03:18:35

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:36 2015-01-12 03:18:36

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:36 2015-01-12 03:18:36

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:36 2015-01-12 03:18:36

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:37 2015-01-12 03:18:37

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:37 2015-01-12 03:18:37

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:37 2015-01-12 03:18:37

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:38 2015-01-12 03:18:38

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:38 2015-01-12 03:18:38

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:38 2015-01-12 03:18:38

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:39 2015-01-12 03:18:39

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:39 2015-01-12 03:18:40

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:39 2015-01-12 03:18:39

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:39 2015-01-12 03:18:40

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:39 2015-01-12 03:18:39

shared pool GROW 4362076160 4429185024 2015-01-12 03:18:40 2015-01-12 03:18:40

DEFAULT buffer cache SHRINK 19394461696 19327352832 2015-01-12 03:18:40 2015-01-12 03:18:40

800 rows selected.

awr里的shared pool建议，增大共享池会有很大收益

其实共享池一直都不够，参数指定的是 3221225472，经过 11826此调整，已经涨到 4429185024了。最大的时候是 6039797760

而且shared pool和buffer cache的最大需求都远大于初始值，之和也超过了sga_max_size定义的24G，因此很容易在繁忙情况下互相拆借空间，造成诸如4031等各种内存错误和latch争用。

SQL> select component, round(current_size/1024/1024/1024) curr, round(max_size/1024/1024/1024) "max", round(user_specified_size/1024/1024/1024) "init"

2 from v$sga_dynamic_components

3 where component in ('shared pool', 'DEFAULT buffer cache');

COMPONENT CURR max init

------------------------ ---------- ---------- ----------

shared pool 4

6 3

DEFAULT buffer cache 18

19 10

SQL> show parameter sga_

NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM

------------------------------ ----------- --------------------------------

sga_max_size big integer 24G