记一次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的内存

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大小为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使用率长期保持在高位。