ORA-04031引起的数据库宕机

参考文献

1.Simplified Approach to Resolve ORA-4031

一、背景

客户反应所有产品均无法正常使用的消息,说应用出现平均时长超时告警,查看数据库告警信息,发现数据库1节点实例挂了,2节点出现活动会话告警且CPU利用率几乎100%。

二、初步分析

查看1节点alert日志,发现实例在重启之前出现ora-04031错误,具体错误如下

ORA-04031: unable to allocate 4144 bytes of shared memory ("shared pool","select (t3.l1_name||'/'||t3....","qksqb.c.kgght","qksqb.c.kgght")

首先看看ora-04031错误究竟是个什么东西

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.
// *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
//          INIT.ORA parameters "shared_pool_reserved_size" and
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size".

出现ora-04031错误是因为shared pool内存不够,于是查看shared pool内存大小

SQL> show parameter shared_pool

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
shared_pool_reserved_size  big integer 3516504473
shared_pool_size           big integer 40G

40G的shared pool内存,已经不小了。

三、临时解决方案

方案1、 简单粗暴,直接刷新shared pool
alter system flush shared_pool;

此方案针对报ora-04031但是还未宕机的情况,数据库实例若已宕机重启,shared pool自然会重新分配,也就不用刷新了,此外,在大库上刷新shared pool也有导致宕机的风险。

方案2、增大shared pool

既然是因为shared pool内存不够才报错,那就增大shared pool内存,但shared pool内存位于SGA(System Global area)中,而SGA用的是机器的物理内存,因此得考虑sga和机器实际物理内存大小。

#查看机器物理内存
[oracle@dgg-dggxxxxxxxxx ~]$ free -g
             total       used       free     shared    buffers     cached
Mem:           504        416         87          2          8        114
-/+ buffers/cache:        293        210
Swap:           31          0         31

机器上还有87g的内存

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 200G
sga_target                           big integer 200G
SQL> select sum(bytes)/1024/1024/1024 GB from v$sgastat;

        GB
----------
199.107129

SQL> select sum(value)/1024/1024/1024 GB from v$sga;

        GB
----------
199.107132

SGA大小为200g,因此可以考虑将shared pool调大25%,即增大10g

alter system set sga_max_size=210g scope=spfile;
alter system set sga_target=210g scope=spfile;
alter system set shared_pool_size=50g scope=spfile;

重启数据库实例,使之生效。

四、根本原因分析

shared pool包括两部分,Dictionary cache和Library cache。Dictionary cache记录的是Oracle系统内部管理所需要的数据字典信息,例如用户名、数据对象和权限等,大小变化不大。Library cache记录了sql的相关信息,例如sql文本、执行计划等,是可变的,shared pool内存不足原因就在于存放了大量的sql信息。

1. 查询各用户下sql占用shared pool信息
SELECT parsing_schema_name,
       Sum (sharable_mem) / 1024 / 1024 / 1024 gb
FROM   gv$sqlarea
GROUP  BY parsing_schema_name
ORDER  BY 2 DESC;
2. 查询用户sql有无硬解析
SELECT parsing_schema_name,
       Substr (sql_text, 1, 150),
       Sum(sharable_mem) / 1024 / 1024 MB,
       Count(Substr (sql_text, 1, 150)) cnt
FROM   v$sqlarea
WHERE  parsing_schema_name = '&schema_name'
GROUP  BY parsing_schema_name,
          Substr (sql_text, 1, 150)
HAVING Count(Substr (sql_text, 1, 150)) > 100
ORDER  BY 4 DESC,
          3 DESC;

若查询结果中,cnt字段有值,则该用户下sql存在有硬解析的情况。

五、关于SGA

查看SGA大小
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 200G
sga_target                           big integer 200G
SQL> select sum(bytes)/1024/1024/1024 GB from v$sgastat;

        GB
----------
199.107129

SQL> select sum(value)/1024/1024/1024 GB from v$sga;

        GB
----------
199.107132
查看SGA空间的组成
-- 也可以直接 show sga
SQL> select name,value/1024/1024 MB from v$sga;       

NAME                         MB
-------------------- ----------
Fixed Size           2.13716888
Variable Size        79360.0035
Database Buffers         124416
Redo Buffers           107.5625

在v$sga视图中,SGA空间被分为4部分,Fixed Size存放一些与数据库版本和平台相关的信息;Database Buffers存放数据库缓存信息,太大会增加数据库管理负担,太小会降低数据库命中率,增加IO负担,一般启用自动内存管理;Redo Buffers存放重做日志的缓冲,重做日志并不是直接写到重做日志文件,而是从Redo Buffers写到重做日志文件,因为内存IO速率比文件要高;Variable Size,我们来看下Variable Size里有什么吧。

查看Variable Size组成
select pool,sum(bytes)/1024/1024 MB from v$sgastat group by pool;

POOL                 MB
------------ ----------
               124525.7
java pool          3584
streams pool       8192
shared pool       65536
large pool         2048

SQL> select sum(bytes)/1024/1024 MB from v$sgastat where pool !=' ';

        MB
----------
     79360

v$sga视图中Variable Size的大小等于java pool、streams pool、shared pool和large pool 大小之和,v$sgastat视图pool字段为空的行大小等于Fixed Size、Database Buffers 和Redo Buffers 大小之和。

查询shared pool剩余空间大小
SELECT *
FROM   v$sgastat a
WHERE  a.pool = 'shared pool'
       AND a.NAME = 'free memory';

六、注意事项

  1. 在启用自动内存管理的情况下,直接调大shared pool而不调大SGA,数据库会自动调小Database Buffers。
  2. 不使用绑定变量直接在程序中写入大量类似的sql会导致大量硬解析,占用大量的shared pool,引发ora-04031报错甚至导致数据库宕机,此外,大量的硬解析还会导致主机CPU使用率长期保持在高位。

参考:https://www.jianshu.com/p/c16f81b3a905

给TA买糖
共{{data.count}}人
人已赞赏
ORACLE

Oracle 数据库定时任务 dbms_job的用法详解

2020-4-15 12:49:48

ORACLE

ORA-01439:要更改数据类型,则要修改的列必须为空

2020-5-9 9:52:21

⚠️
Golurending上的部份代码及教程来源于互联网,仅供网友学习交流,未经Golurending作者或上传书面授权,请勿作他用。
若您的权利被侵害,请联系站长 邮箱codesns#163.com(#-@) 或 点击右侧 私信:管理员 反馈,我们将尽快处理。
0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索